Miscellaneous Reader Feeback
Today we share responses from readers on different recent topics.
John writes regarding SQL Authentication
We have also been working on tightening up security for our SQL Server accounts. One interesting problem I found for us was dealing with linked servers. We use Active Directory for authenticating to our SQL Servers for one of our data repositories. I found that the linked server attached to the source SQL Server (also 2008 R2) was set to authenticate as a particular service account that is SQL Server authentication and that has ‘sysadmin’ rights. I fixed this immediately, setting the linked server to authenticate according to the user’s rights on the other machine, which is what we expect.
However, this does not appear to work. Evidently, Microsoft introduced a problem in SQL Server 2005 dealing with AD and multiple hops that has since been fixed in SQL Server 2012, but not in any versions between 2000 (where it worked before) and 2012. We are no longer allowed to use SQL Server authentication, but the linked server is a must have for nightly processing since most of the inter-server work is done using stored procs with DataStage being the main data movement workhorse.
Does anyone have any suggestions that can help? All my research thus far has stated Microsoft needs to issue a service pack and they simply do not want to do it.
Greg writes about Cartesian Joins
I just wanted to say that your Cross Join article was very enlightening – the “No Cartesian Product” mantra is very prevalent, and it is very cool to see where breaking the “rule” can be advantageous. I have used this concept in a few very specific queries as well where I wanted to see ALL sales for ALL stores for ALL periods, even if they were zero.
Thank you for keeping us thinking – your articles are nearly always thought-provoking, clear, and concise.
Editor:
I think the main reason a Cartesian join is frowned upon is that there are no restrictions. All rows in table A are joined to all rows in table B. I have actually had to pull the power cord out of the wall on a server where a developer ran a query like this on two large tables, just to get the query to stop.
Still, there are times when a Cross Join is the correct implementation for your query. Like any tool in your tool chest; if you don’t know how to use it, it can be dangerous. But, that doesn’t mean it isn’t a good tool.
Thanks for the great responses. If you have some ideas for John’s authentication issue, feel free to respond by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
The Ever-Changing Role of the DBA
Defining the job of DBA is getting to be increasingly difficult. Oh, most people know the rudimentary aspects of the job, namely keeping your organization’s databases and applications running up to par. The DBA has to be the resident DBMS expert (whether that is DB2, Oracle or SQL Server, or most likely a combination of those). He or she has to be able to solve thorny performance problems, ensure backups are taken, recover and restore data when problems occur, make operational changes to database structures and, really, be able to tackle any issue that arises that is data-related.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)
Featured Script
UDF – Phone Parser
This UDF will take in the 5 most common phone formats and output a new format from a predetermined input parameter…. (read more)