Windows 2008 and xp_logininfo

I've come across an issue which appears to be caused by changes in the security of Windows 2008.

Originally I believed this was related to SQL 2008 however I have replicated the same behavior on SQL 2008 and SQL 2005 when running on a Windows 2008 OS.

Scenario
In an environment where you have user accounts existing in a parent domain (e.g. DOMAIN01) and Computer and Service accounts existing in a Child Domain (e.g. DOMAIN02) you may find that SQL Server running on a computer in the Child Domain will report the following error when you perform specific operations from a SQL Server running either SQL Server 2005 or SQL Server 2008 on a Windows 2008 Operating System.

Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'DOMAIN01\matticus', error code 0x5.

Some operations which may cause this are:

• Attempting to restore a database using a 3rd party application such as Red-Gate SQL Backup
• Creating a schedule for the re-population of a Full Text Catalogue or creating an SQL agent job with the owner set to a domain account within the Parent Domain (e.g. DOMAIN01)
• Using xp_logininfo from within an application / script to check the group membership of a domain user account (e.g. exec xp_logininfo 'DOMAIN01\matticus')
  

This error occurs due to changes in the security of Windows 2008 and specifically changes to the Microsoft Windows API calls used to retrieve information about the Windows domains and the Windows user accounts.

The issue will only occur when SQL Server is attempting to retrieve information regarding Windows Domains and User accounts from the Parent Domain (e.g. DOMAIN01). Queries to AD objects within the same domain (e.g. DOMAIN02) will be successful.


Steps to Reproduce the Issue
The following steps are provided here to reproduce the error within an environment to meets the configuration as stated in the scenario above.

1. Connect to the SQL Server running in DOMAIN02 using SSMS and an account with SYSADMIN rights to the SQL Instance
2. Open a New Query window
3. Run the following TSQL to reproduce the error (replacing the account name with an account within the parent domain)
   
   exec xp_logininfo 'DOMAIN01\matticus'
   
   
4. You should receive the following error:
   
   Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
   Could not obtain information about Windows NT group/user 'DOMAIN01\matticus', error code 0x5.
   
   
5. Now run the following TSQL to demonstrate that querying within the same domain is not affected (replacing the account name with the service account within the child domain)
   
   exec xp_logininfo 'DOMAIN02\sqlserviceacc01'
   
   
6. This should complete successfully and return a result set. The same would occur for any other accounts within the Child Domain.
   
   

Solution 1
At this time the only solution I have found to this problem is to add the service account which the SQL Server service is running on to the "BUILTIN\Windows Authorization Access Group" group within the parent domain. This membership could be inherited through another domain group being added to that BuiltIn group if you have a large number of servers to manage in your organisation.

NOTE: In the past I had found a similar case where membership of the "BUILTIN\Windows Authorization Access Group" group in the parent domain was required. However that was for an issue with the SCOM2007 Service Broker and also SSRS Email Delivery where I believe they had their own methods of querying AD group memberships or had started to implement the changes which MS were planning for Windows 2008 as all servers in that case were running on Windows 2003 OS. These were though due to the accounts for those services being different to the main service account for the related application. That has been the only other case where I have seen this behaviour. I found this article which clearly outlines the SSRS Email Delivery issue.


Reference Material

How to troubleshoot a SQL Server 8198 error
http://support.microsoft.com/kb/834124

xp_logininfo (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190369.aspx

Some applications and APIs require access to authorization information on account objects
http://support.microsoft.com/kb/331951

xp_logininfo fails for agent account
http://www.sqlservercentral.com/Forums/Topic612567-149-1.aspx