Twice Bitten, Thrice Shy

I’ve been bitten twice recently by my reliance on faulty assumptions. I’m sure we all remember the old adage that when you assume you make an ASS out of U and ME. Well, I have certainly proven the truth in that saying lately. (Ok, maybe I only made an ass out of me and not you…)

The replication project I wrote about last week started off because we needed to replace a SQL 2000 cluster that hosted a subscriber database and we wanted to upgrade to SQL 2008 at the same time. I knew that replication is limited to working between versions of SQL Server that are no more than two versions apart. So replicating from SQL 2000 to SQL 2008 would work. But for some reason, I thought that the version difference had to be “downhill” – meaning the publisher had to be the higher version and the subscribers had to be the lower version. Based on this, I thought replication in my situation would not work, so I started investigating other ways to solve the problem of how to copy data on a regular basis from a SQL 2000 machine to a 2008 machine. I looked at log shipping and determined that wouldn’t work because the 2008 machine (subscriber) needed to have the database available for reporting. So I had about settled on using a homegrown process of automating nightly backups and restores when I posted to the forums on asking for help from the SQL community to see if there was perhaps a more elegant way of doing what I was trying to accomplish. Someone pointed out that in my situation, replication actually was possible! In fact, this TechNet article even states: “SQL Server 2000 and SQL Server 2005 can both participate in replication topologies with SQL Server 2008.” (Turns out, the version of the distributor is the limiting factor. The same TechNet article says: “For all types of replication, the Distributor version must be no earlier than the Publisher version.”  Since my distributor and publisher were the same machine, I was ok.)

So I wasted a day or two heading down the wrong path simply because I did not verify what I thought about replication was true.

On the same project, I was bit again, although this time I only wasted about 3 hours. One of the things I needed to do was transfer logins from the publisher to the subscriber. I used the sp_help_revlogins procedure given in this Microsoft Knowledge Base article. I tried both methods given and I was still unable to use the SQL login on the subscriber machine. Then I tried making an SSIS package and using the Transfer Logins task. That didn’t work either. Whenever I tried to log in to the subscriber machine using a transferred SQL account, I got a login failed message. I started to delve into SIDs and other arcane details of transferring SQL logins when my mind dredged up something that I read a while ago. I recalled that the login failed message presented to the user was purposely vague, to help prevent hacking, but typically, a more detailed reason for the failure would be written to the Windows event log. So I checked the event log and sure enough, I saw this:

Login failed for user ‘<username>’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

Doh! All our servers (or I should say, almost all our servers) are configured for mixed mode authentication. The one I was using for testing, however, was apparently not. So that was why my login attempts using a SQL account failed. The logins had transferred successfully, but SQL Server was not configured to use them. I changed the server to mixed mode authentication and everything worked.

So when things aren’t working out the way you think they should, it very often pays to take a step back and question the assumptions you are working under. You could save yourself a lot of time.

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.