This is one of those typical blog posts that I write for my future self, the guy who keeps fixing the same stuff over and over and forgets what he did the next minute.
If you want to query information about a Windows user or group and its access path in SQLServer, you can use the extended stored procedure “xp_logininfo”. Here’s an example:
EXEC xp_logininfo 'MyDomainSomeUser','all';
If everything is configured correctly, you will see a list of Windows accounts and the login(s) they are mapped to in SQLServer.
However, in some cases, the command fails with the infamous error message:
Could not obtain information about Windows NT group/user 'MyDomainSomeUser', error code 0x5This happens every time SQLServer tries to query information about the Windows user from Active Directory and receives an error.
Understanding where the error comes from can be tricky, but it can become easier to troubleshoot when you understand what happens behind the scenes and what are the most likely causes.
The user does not exist
This is very easy to check: does the user exist in Windows? Did you misspell the name?
You can check this from a cmd window, issuing this command:
net user SomeUser /domain
If you spelled the user correctly, the command will return information about it, like description, password settings, group membership and so on.
If the user name is incorrect and cannot be found in AD, you will get an error message
The user name cannot be found.
Easy peasy: check your spelling and check your AD.
The service account does not have enough privileges to query AD
As I said, SQL Server needs to query AD to retrieve information about the user: if its service account doesn’t have enough privileges, the query will fail.
The most likely cause for this is a misconfiguration of the service account settings in SQL Server. To be more specific, it is very likely that SQL Server is configured to run as a local user who has no access to Active Directory at all. This happens when SQL Server runs as a per-service SID or one of the built-in local accounts (local service or localsystem).
It is very easy to check what account is being used to run SQL Server: all you need to do is query sys.dm_server_services.
SELECT servicename, service_account FROM sys.dm_server_services;
If you see a local account being returned, go ahead and change your service account to a domain account, using the Configuration Manager.
If you still can’t query AD, maybe there is something wrong with the permissions on your AD objects. Try impersonating the SQL Server service account, open a cmd windows and issue the net user command.
> net user SomeUser /domain
The request will be processed at a domain controller for domain MyDomain
System error 5 has occurred.
Access is deniedIf you get the “Access is denied” error message, you need to go to your AD and grant read permissions on that user/OU to the service account.
The service account does not have enough privileges to impersonate the windows user
This was a bit of a surprise for me. In order to retrieve information about the Windows user, SQL Server needs to impersonate it first and then will contact AD impersonating that user.
In order to impersonate a user, SQL Server needs to run under a service account user that has enough privileges to impersonate another user. This privilege is granted through a local policy.
Open the local security policy MMC (secpol.msc) and expand “Local Policies”, “User Rights Assignment”. Find the policy named “Impersonate a client after authentication” and double click it. You can verify whether the service account for SQL Server is granted this privilege, directly or through one of its groups.
Generally speaking, you don’t have to change this, because by default Windows grants this privilege to the “SERVICE” special identity. Any process running as a service is acting as the SERVICE special identity, including SQL Server. If you don’t find it listed here, add it back.
Windows permissions can get tricky at times. I hope that this post helps you (and me!) taming the beast.