This blog post is waiting for me to have some time for the past couple of months by now. It did bite me back then, and so it did yesterday.
“Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. Error source: Microsoft OLE DB Provider for SQL Server” – now, I must emphasize that I’m not going to reproduce the plethora of info that is present out there already, but instead I might be among the few selected ones that indeed had issues with AD domains. If you’re curious about that, read on; if you’re looking for how to enter some lines of code in some connection string, you better go have a coffee or something.
Background: the company that I work for, migrated all their SQL Server 2005 servers (with DAS – Direct Attached Storage) to SQL Server 2008 clustering (on SAN). This obviously means a lot (I mean A LOT) of fun, such as deprecated code, and so on. Besides this, one of the required steps is to swap the IP addresses between the SQL Server 2005 machine and the SQL Server 2008 resource on the cluster (of course, anybody knows that SQL Server 2008 is just a resource on a 2008 cluster, right? – I learned this the hard way).
And now for the fun:
“ONLY jobs that need to run to C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\DTSRun are failing.
The ones that are running C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec are running fine.
Secondly, the ones running under 80\Tools are the ones using the server IP (10.0.0.31). when you ping the IP address, it still returns SQL03DB as the server.”
Rough translation of the above: all the jobs that running DTSX packages are running fine, while all the jobs that are running DTS (SQL Server 2000 – yes, I know, that old) are failing in SQL Server 2008. Besides that, when I run ping –a 10.0.0.31, I still get the name of the SQL Server 2005 machine, instead of the SQL Server 2008 cluster resource.
This is something that happened early May, and I completely forgot about it. And it happened again yesterday.
Sorry for all that blur stuff, but this is a prod machine. Anyway, as you can see, the IP address of my SQL Server cluster resource is 10.0.0.10.
Now, if I ping –a 10.0.0.10, I get the following:
So instead of getting something in the line of “28DBCLU”, I still get the old SQL Server machine, which used to be “01db”.
Again, oddly enough, this manifested itself only for the DTS packages, which are running DTSRun, found in the \80 folder.
Solution? A very simple one, just ask the sys admins to make the necessary changes in the DNS (yes, that DNS, related to AD, DHCP, all that jazz). Also, it would be very helpful not to have hard-coded values in your hosts file.
And for the grand finale: I posted a question on Twitter, under #sqlhelp, asking whether I should have my system databases on SAN (which mean automatic failover in case something goes wrong with the active node), or if they should be on the default installation path (yes, that Program Files thing). While there was a little variation of the answers, my personal choice is to have them automatically fail over. Just imagine what would happen if you have a failover and the msdb
and master databases are not the same.