An interesting “Issue” with adding Windows Logins in SQL Server

Now here’s one that had me stumped for quite some time. A brief description of the issue:-

1. I have 2 instances of SQL Server set up on different servers, both with a Case sensitive collation

2. At least one of the instances is installed on a cluster.

3. The Windows version for all of them is Windows Server 2008 R2, with the default collation settings (case insensitive).

When I try to add a windows login to one SQL environment, I am able to add the login in either case. But when I add the login in the other environment, I am unable to add it in both cases. I am only able to add it in one particular case. When trying to add it in the second case I get this error:-

Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'HARSH2K8HARSH' not found. Check the name again.

So the question arises, why?

After spending quite some time researching this “weird” issue, I found some interesting stuff, which I will try to explain here without going into too much technical detail:-

    • When we add a windows login in SQL, the basic steps it performs internally go something like this:-

o Take the name of the login, try to retrieve the SID. If unsuccessful, raise error.

o Take the SID retrieved in the previous step, and try to fetch the login name for that SID. If unsuccessful, raise error.

o Take the newly retrieved name, and compare it with the name passed originally (to be added as windows login). If both do not match, raise error.

    • As you probably guessed already, to perform all these steps, SQL uses Windows API calls. These are the same calls which are used when you try to add a Login to the security permissions on a File/folder (where we use the “Check Name”button).
    • The issue arises because the windows API used to retrieve the login name for a SID can return the login in any case (not necessarily the same case in which the Login is defined in Active Directory). And when the name is returned, the SQL code does a “simple” i.e. case sensitive comparison. This is where the mismatch occurs and we see the error mentioned above.
    • There is also some “caching” of the name for a SID at the machine level.

Though the jury is still out on whether the issue is with SQL Server for not doing the comparison in a case-insensitive manner, or with the Windows API for not returning the Login in the same case in which it exists in AD, the product group figured the best way was to fix it themselves. So, they modified the code for SQL 2012, and the code change was made in the RTM version of SQL 2012. However, due to certain restrictions, we were not able to back port it for the earlier versions of SQL. Also, there is a pretty obvious workaround of adding the login in the other case. The only time you’re likely to run into an issue with that is if you try to add one of the two servers as a Linked server in the other one, in which case authentication will fail since the logins are in different case on the two servers. In such a scenario, try rebooting the box, and then try to add the login. If that doesn’t work, then the best way out is to use a different login altogether, one which you’re able to add in the same case on both servers.

Not a very common or helpful post, I know, but an interesting one nonetheless. What say?