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

Answer 1

  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

Answer 2

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

Answer 3

  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

Answer 4

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

 

 

Answer 5

  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

Answer 6

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

Answer 7

  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

Answer 8

 

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

Answer 9

  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

Answer 10

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

Answer 11

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

Answer 12

  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

 

Answer 13

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

Answer 14

   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

 

Answer 15

 

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.

 

 

Answer 16

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