The last few days have been a little bit hellish. I’ve been on-site at my client’s office helping them do a Windows 2000 to Windows 2003 migration, set up their new server, and deploy beta versions of the applications that I’ve been writing for them.
I backed up their SQL Server database from the old server and restored it onto the new Windows 2003 server. It restored fine. I could connect using Query Analyzer and any ADO.NET-based application that was running locally could connect. It’s looking good then we start testing some ASP.NET apps that are on a different machine and hit this new database server. BLAM! We started getting exceptions. Either “Login failed for user ‘sa’. Reason: Not associated with a trusted SQL Server connection” or “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.”
First order of business: go to google and start digging. Very quickly I found this tech support article saying that SQL Server isn’t set to “mixed mode” authentication. I checked it out and the sysadmin had installed SQL Server to “Windows only“ mode. That’s easy enough to change. Changed it and restarted the MSSQLSERVER service.
No love. Now I start wondering if there’s something messed up in the registry. I find this article describing how to manually change SQL Server to “mixed mode.” Nope. That’s already done. The documentation says that we only have to cycle the instance for the change to take but we reboot the whole server just to make sure.
Still nothing. At this point, we’ve burned up a lot of time on something that should be totally easy. Now I start wondering if it’s a security problem because I kinda half remembered reading about distributed transaction problems between Windows 2000 and instances of SQL Server running on Windows 2003.
None of it was really looking likely and at some point I started thinking about how SQL Server isn’t supported on Windows 2003 under a certain service pack level. What service pack is this server running anyway? So, I fired up Query Analyzer and ran “SELECT @@VERSION“. The version was “2000.80.194.0“. Quick skim of the build numbers listed online and…UNBELIEVABLE! This thing hasn’t been patched even ONCE!
So, I upgraded to SQL Server Service Pack 4 and my problems went away. While I was at it, I also turned on Windows Update and found out that none of the security patches had be applied either. Doh.
-Ben
Leave a Reply