다음을 통해 공유

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?

Comments

  • Anonymous
    March 20, 2012
    It is interesting, time to see if I can reproduce :), always like testing things like this.

  • Anonymous
    March 20, 2012
    Thanks for showing interest Glenn...!!! Do let me know how it goes....!!!

  • Anonymous
    September 20, 2012
    Very helpful post

  • Anonymous
    December 16, 2012
    Interesting.....

  • Anonymous
    December 16, 2012
    Thanks for showing interest in the blog Satheesh...!!!

  • Anonymous
    April 04, 2013
    great post .. Thanks for sharing

  • Anonymous
    April 06, 2013
    Many thanks for appreciating Gurpreet...!!!

  • Anonymous
    October 26, 2014
    Hello Harshdeep This is a nice post. I have a similar issue in which you can help. I have a windows 8.1 pro host machine with hyper-v as hypervisor. I have two virtual windows server 2008 enterprise versions.I have made both of those virtual machines as a part of the Active Directory domain contoso.com. The domain controller's name is DC, whereas the other one is SQL-A. One SQL-A, if I create a new directory and try to assign permissions to a domain user, I do not see the domain name contoso.com, when I got to the new folder's properties -> Security tab -> permissions . Any help woyld be appreciated.

  • Anonymous
    October 26, 2014
    The comment has been removed

  • Anonymous
    November 17, 2014
    Hi Gaganpreet, thanks for sharing the issue and the solution details. I would also recommend that you share this on the AD msdn forum as well. Thanks.