none
SQLServer Error: 15404 with Active Directory

    Question

  • Hello,

    I'm having trouble running jobs with my active directory (ADS) account. I've setup my SQL services to run under an ADS account, but jobs cannot seem to query ADS for user information. We're running Windows Server 2003 and SQL Server 2005 SP2.

     Here is the error message:

    ==

    The job failed.  Unable to determine if the owner (ADS\me) of job eFASRtest has server access (reason: Could not obtain information about Windows NT group/user 'ADS\me', error code 0x5. [SQLSTATE 42000] (Error 15404)).

    ==

     

    also this message in log:

    ==

    [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'ADS\me, error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

    ==

     

    I have done a *lot* of searching and cannot find the solution. I believe the 0x5 code is access denied. 

    The same job runs if I change the owner to a SQL login that's not an ADS account.

    I'm not certain how to confirm that the SQL Agent account is the account actually querying ADS, also.

    Any help would be greatly appreciated!

     

     Thanks,

     

    -Tony

    Monday, April 09, 2007 7:04 PM

Answers

  •  

    In my working scenario, the SQL Server service was running as a domain user in Domain A.

     

    In the non-working scenario, the SQL Server service is running as a local account that does not have domain access to Domain B.  I set it up this way because I thought it was the SQL Agent account that was actually doing the AD query.

     

    I changed the SQL Server service to run as a domain user account and the agent jobs now appear to be working when a domain account is the owner.

     

    I am going to do some more testing, but I think that the SQL Server service account was my problem.

     

    Thank you very much, Raul.

     

     

    Wednesday, October 10, 2007 4:27 PM

All replies

  •   Correct, the error code 0x5 is ERROR_ACCESS_DENIED, and from the whole error message I would guess that it is the SQL Server service account the one failing to issue the query to the AD. Verify that SQL Server service account has permission to query the AD.

     

      -Raul Garcia

       SDE/T

       SQL Server Engine

    Tuesday, April 10, 2007 1:10 AM
    Moderator
  • Hi, Raul.

     

    Thanks for your reply.

     

    I tested the service account's ability to query ADS in this way: On the server, whenever I adjust NTFS permissions on a file or folder and want to add an ADS account or group, I have to supply an ADS username and password to query ADS. I did this using the SQL Server Service account's name and password, and I was able to "check names" in ADS. I'm assuming this is a valid test.

     

    Also, in one test, I set the owner of the job to the service account and the job ran. So, it looks like the service account has permission to check itself in ADS, but not other ADS objects. (Assuming the SQL Server Service account is the account actually making the query to ADS.)

     

    So, I'm still not certain if I need to adjust SQL Server, Windows Server, or the ADS service account permission to ADS (which I cannot do myself - I'm on a very large ADS with limited rights to objects).

     

    Thanks again,

     

    -Tony

    Tuesday, April 10, 2007 12:53 PM
  •   SQL Server should ask for all the group memberships for any Windows user; I am not sure if the test you tried with NTFS ACLs would do something similar.

     

     Try the following statement in SQL Server using a sysadmin account:

    EXECUTE AS LOGIN = ‘ADS\me’

    Go

     

      The SQL Server impersonation token for Windows users is based on the information SQL Server gets back from the AD. If the impersonation succeeds, I would assume SQL Server has absolutely no problems querying the AD and that we will need to look for a different reason. If this query fails then we will need to find out what part of the AD queries the account is restricted to perform.

     

      -Raul Garcia

       SDE/T

       SQL Server Engine

    Tuesday, April 10, 2007 6:27 PM
    Moderator
  • Hi, Raul.

     

    I executed the following:

     

    EXECUTE AS LOGIN='ADS\me'
      Go

     

    Message:

     

    Msg 15404, Level 16, State 19, Line 1
      Could not obtain information about Windows NT group/user 'ADS\me', error code 0x5.

     

    I also executed that command with the service account:

     

    EXECUTE AS LOGIN='ADS\myserviceaccountname'

    Go

     

    Message:

     

    Command(s) completed successfully.

     

    So, it's as if the service account can query its own information but not that of other ads user objects. I suspect that SQL is trying to validate my SID with the SID it has for me within a SQL system table to verify that my ADS account really is a SQL login, but I'm just guessing there.

     

    I'm going to send this information to our ADS admins. It really seems to point to a permission problem in ADS.

     

    If it is a SQL issue, could it be that ADS\me doesn't have permission to execute a system stored procedure in order to perform the ADS query? To answer my own question, it seems like the service account would be doing this and not my ADS\me account.

     

    Thoughts?

     

    Thanks very much,

     

    -Tony

     

     

    Wednesday, April 11, 2007 12:43 PM
  •   I have to admit I am also a little bit surprised. I have never faced a scenario where an account is getting an explicit access denied when querying information about itself and all its groups, but succeeds while querying information about other principals and objects in the AD.  I agree with you, check with your AD administrator, probably they have some security policy we are not aware of that may be causing this problem.

     

      I personally don’t think the problem is regarding any permission to execute a system stored proc (EXECUTE AS should call the code directly); but let’s wait until we have more information from the AD admins regarding this issue.

     

      One more thing you can try: Can you install a test-only copy of SQL Server 2005 (i.e. SQL Server Express) using a different account for the service? (it could be a different machine). If you can install it, can you try the same EXECUTE AS LOGIN tests?

     

      Thanks a lot,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, April 11, 2007 6:03 PM
    Moderator
  • Hi, Raul.

     

    Actually, I think it's the other way around: the service account can query its own information, but cannot query other ADS users.

     

    I'll see if I can try that test on my local SQL instance.

     

    Thanks again,

     

    -Tony

    Wednesday, April 11, 2007 8:36 PM
  •   Got it, I misunderstood that part; I apologize for the confusion. Thanks for the clarification.

     

       It seems like it is a limited account on the AD and doesn’t have permission to query, hopefully the AD admin will be able to verify if this assumption is correct.

     

      -Raul Garcia

      SDE/T

      SQL server Engine

    Wednesday, April 11, 2007 8:48 PM
    Moderator
  •  

    Hi Folks,

     

    I am having the same exact problem.   I even test on my test SQL server and I get the same message.  I am not sure where to start to solve this problem.   Any help?

     

    thank you

    Tuesday, August 07, 2007 2:40 PM
  •   Make sure that the SQL Server service account has permission to query the AD. Typically this error is caused by trying to impersonate or query information regarding a Windows user from a different domain.

     

      Please, if you need further assistance let us know the scenario that were you are hitting this issue.

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Thursday, August 09, 2007 4:49 AM
    Moderator
  • Raul,

    Can you give me the step by step on how to grant, or make sure, the SQL Server service account has access to query AD?  I'm new to SQL and not sure how to do this.  I have two domains setup.  One is internet facing, and one is our regular domain.  Our regular domain doesn't trust out Interent domain, but our Internet domain trusts our regular domain.  The SQL server I am working on is in our Internet domain, and the jobs are falling with the same messages above.  We are running a few jobs with different user accounts.  Some from our regular domain, and a few with our admin account on the Internet domain...All jobs are falling.  I have tried running the jobs with the sa account, but that fails as well...

    Can you help me out?

    Thanks,
    Ryan
    Monday, September 24, 2007 3:19 PM
  • Hello,
    I am also getting the same error (15404) as Syndrake.  I have tried all of Raul's suggestions with the same results.

     

    I have confirmed with our AD Admin that there are no restrictions on this domain account.  They setup the service account as a standard user.  She actually created a new account, just as a test, while I had her on the phone and we had the same results.

     

    One intresting twist with my situation is that this issue cropped up when I migrated this server to an AD domain from a NT4 domain (seperate domains, not a domain upgrade).  The Agent service account was able to query user info just fine in NT 4, but this same SQL Agentservice/job owner setup now fails in AD.

     

    Thanks,
    Jeff

    Monday, October 08, 2007 3:39 PM
  •   For further assistance on AD configuration I would recommend using the directory services forum (http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=571&SiteID=17), the audience of this forum will be better qualified to help you on this area than myself.

     

      Please correct me if my assumption is incorrect:

    ·         Domain-A  trusts Domain-B, but Domain-B doesn’t trust Domain-A

    ·         SQL Server is installed on a Domain-A machine

      In order to work, SQL Server should be running under a Domain-B service account, otherwise it is very likely that Domain-B will not accept the token from the service and fail.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Monday, October 08, 2007 7:38 PM
    Moderator
  • Hi Raul,

    Thanks for replying to my post.

     

    Actually, Domain A trusts Domain B and Domain B trusts Domain A.  Full 2-Way trust.

     

    SQL Server machine was a member of Domain A but now has joined Domain B.  Domain A will be going away.

     

    Domain A = NT4

    Domain B = AD

     

    Previous Working Scenario

    • Server is a member of Domain A (NT4).
    • SQL Agent service running as Domain A\SQLAgentAcct.
    • Domain A\SQLAgentAcct has sysadmin rights in SQL.  Also belongs to Domain Users domain group and the local Administrators group.
    • SQL Agent job owner = Domain B\Developer

    Agent job runs successfully.  Domain A\SQLAgentAcct while logged into Domain A is able to get information about Domain B\Developer.  I assume because of the 2-way trust.

     

    Non-working Scenario...Attempting to take Domain A out of the picture.

    • Server is now a member of Domain B (AD).  Also, all accounts involved now belong to Domain B.
    • SQL Agent service running as Domain B\SQLAgentAcct.
    • Domain B\SQLAgentAcct has sysadmin rights in SQL.  Also belongs to Domain Users domain group and the local Administrators group.
    • SQL Agent job owner = Domain B\Developer

    SQLAgentAcct could not obtain information about Windows NT group/user 'Domain B\Developer'

     

    Like Syndrake, I was able to log into the server as Domain B\SQLAgentAcct and add Domain B\Developer to local groups.  The check names function works great.  So, you would think Domain B\SQLAgentAcct would have adequate permissions to query AD.

     

    My AD Admin claims that the Domain B\SQLAgentAcct does not have any restrictions.  It is setup like any other user.  And, because I am able to query AD outside of SQL, this doesn't seem to be a AD permissions issue.

     

    Thanks,

    Jeff

    Tuesday, October 09, 2007 2:54 PM
  •    What is the service account for SQL Server? The AD query should be running using the AD credentials (if coming from SQL Server Engine).

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Wednesday, October 10, 2007 5:59 AM
    Moderator
  •  

    In my working scenario, the SQL Server service was running as a domain user in Domain A.

     

    In the non-working scenario, the SQL Server service is running as a local account that does not have domain access to Domain B.  I set it up this way because I thought it was the SQL Agent account that was actually doing the AD query.

     

    I changed the SQL Server service to run as a domain user account and the agent jobs now appear to be working when a domain account is the owner.

     

    I am going to do some more testing, but I think that the SQL Server service account was my problem.

     

    Thank you very much, Raul.

     

     

    Wednesday, October 10, 2007 4:27 PM
  • I have got the same issue after renaming the server. When executing a maintenance plan I get the same error. What I can se is that the user failing is <old servername>\Administrator not <new servername>\Administrator. The server does not belong to a domain. Also the default <servername>\<username> accounts under Security/Logins are also <old servername>\<username>.

    How can I fix this ?


           Jens
    Tuesday, September 02, 2008 2:16 PM
  • We have this issue also.  I'm going to try maybe setting the trust for delegation rights on the sql svc acct?    Could this be a double hop kerberos issue?

    Has anyone found any fix for this?


    UC Berkeley
    • Proposed as answer by Johann Coulon Tuesday, November 16, 2010 6:50 AM
    Thursday, October 14, 2010 9:07 PM

  • I have the following scenario and solved it this way:

    • A cross-Forest Trust between "Domain A" and "Domain B" (in this example alpha.corp and beta.corp)
    • The SQL Server is member of domain BETA and tries to obtain information about a user in domain ALPHA but fails with error "SQLServer Error: 15404..."
    • The SQL Server is running under the service account "BETA\sqlservice"


    On The SQL Server, error 28005 was constantly logged in the event log:

      An exception occurred while enqueueing a message in the target queue.
      Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'ALPHA\myaccount', error code 0x5.

    On the Domain Controller(s) of domain "ALPHA" I could also find event id 4769 and Failure Code 0xc,
    which translates to KDC policy rejects request Workstation/logon time restriction.

    Log Name:      Security
    Source:        Microsoft-Windows-Security-Auditing
    Date:          16.11.2010 07:11:46
    Event ID:      4769
    Task Category: Kerberos Service Ticket Operations
    Level:         Information
    Keywords:      Audit Failure
    User:          N/A
    Computer:      DC1.alpha.corp
    Description:
    A Kerberos service ticket was requested.

    Account Information:
     Account Name:  sqlservice@BETA.CORP
     Account Domain:  BETA.CORP
     Logon GUID:  {00000000-0000-0000-0000-000000000000}

    Service Information:
     Service Name:  cifs/DC1.alpha.corp
     Service ID:  S-1-0-0

    Additional Information:
     Ticket Options:  0x40810000
     Ticket Encryption Type: 0xffffffff
     Failure Code:  0xc
     Transited Services: -

     

    Solution:

    Enable 'Allowed to authenticate' security setting for the service account BETA\sqlservice on the domain controllers computer object in domain ALPHA:

    1. Logon to the Domain Controller of domain ALPHA
    2. Open Active Directory Users and Computers (dsa.msc)
    3. Enable the 'Advanced Features' under the menu 'View'
    4. Navigate to the domain controllers computer object and open the property window
    5. Click on the security tab
    6. Add the SQL Service account "BETA\sqlservice" and enable the setting 'Allowed to authenticate'
    7. Click [OK] to close the window
    8. Repeat steps 4-7 on each Domain Controller computer object

     

    Tuesday, November 16, 2010 7:32 AM