Could not obtain information about Windows NT group/user (error code 0x5)

• Jon.Morisi

  SSChampion

  Points: 12846

  More actions

  November 13, 2018 at 10:18 am

  #416488

  Here's my issue:

  1.  Log into SSMS locally on the server as sa
  2.  Attempt to impersonate an existing "Windows authentication" (domain) account.
  3.  Receive error "Could not obtain information about Windows NT group/user '[domain\user]', error code 0x5."

  I've seen quite a few posts about this issue.  Some are related to running a stored procedure, some are my case exactly.  However all of them don't have the actual resolution to the issue.  It seems most are content to "just create and use a SQL Server authentication login", or change the owner of the object to a SQL login.

  I'm pretty sure that the issue is that the domain account running the SQL Server Service is missing a required permission in Active Directory (0x5 is access denied).  
  If anyone knows the missing Active Directory permission, I would be grateful. 

  P.S. the server is correctly auto-registering the SPN, so the service account has that much access. (required Active Directory “Write to Public Information” permissions).

  Here's the SQL for the impersonation test:
  
SELECT SUSER_NAME(), USER_NAME();

  EXECUTE AS LOGIN='[domain]\';
GO

  --Msg 15404, Level 16, State 19, Line 4
--Could not obtain information about Windows NT group/user '[domain]\', error code 0x5.


• Sue_H

  SSC Guru

  Points: 90860

  More actions

  November 15, 2018 at 3:06 pm

  #2013749

  Jon.Morisi - Tuesday, November 13, 2018 10:18 AM

  Here's my issue:

  1.  Log into SSMS locally on the server as sa
  2.  Attempt to impersonate an existing "Windows authentication" (domain) account.
  3.  Receive error "Could not obtain information about Windows NT group/user '[domain\user]', error code 0x5."

  I've seen quite a few posts about this issue.  Some are related to running a stored procedure, some are my case exactly.  However all of them don't have the actual resolution to the issue.  It seems most are content to "just create and use a SQL Server authentication login", or change the owner of the object to a SQL login.

  I'm pretty sure that the issue is that the domain account running the SQL Server Service is missing a required permission in Active Directory (0x5 is access denied).  
  If anyone knows the missing Active Directory permission, I would be grateful. 

  P.S. the server is correctly auto-registering the SPN, so the service account has that much access. (required Active Directory “Write to Public Information†permissions).

  Here's the SQL for the impersonation test:
  
SELECT SUSER_NAME(), USER_NAME();

  EXECUTE AS LOGIN='[domain]\';
GO

  --Msg 15404, Level 16, State 19, Line 4
--Could not obtain information about Windows NT group/user '[domain]\', error code 0x5.


  There are also quite a few posts that indicate the issue was with the service account and not having set password never expires.
  There are also some posts where it's some odd issue with a particular account so trying to execute xp_logininfo with a few different accounts.
  And it can also happen if there are issues with communication between SQL Server and active directory.
  Otherwise, the service account needs to be able to read the container with the user accounts.

  Sue

• frederico_fonseca

  SSCoach

  Points: 16158

  More actions

  November 15, 2018 at 5:51 pm

  #2013758

  first I wonder if the syntax is correct
  EXECUTE AS LOGIN='[domain]\';
  possibly should be
  EXECUTE AS LOGIN='[domain\user]'; -- brackets around all of the domain\user, not around both domain and user

  Second it may be that the SQL Server domain account does not have "impersonation" privileges and is therefore failing.

• Jon.Morisi

  SSChampion

  Points: 12846

  More actions

  November 16, 2018 at 10:36 am

  #2013808

  EXECUTE AS LOGIN='[domain]\';

  Above is just pseudo code.  I did test

  frederico_fonseca - Thursday, November 15, 2018 5:51 PM

  first I wonder if the syntax is correct
  EXECUTE AS LOGIN='[domain]\';
  possibly should be
  EXECUTE AS LOGIN='[domain\user]'; -- brackets around all of the domain\user, not around both domain and user

  Second it may be that the SQL Server domain account does not have "impersonation" privileges and is therefore failing.

  Thanks, this lead me down the right track.  It was not an AD permission it was the local impersonation configuration under, "Configure/verify local impersonation user rights" here:
  https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/