SQLServer Error: 15404 with Active Directory

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

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

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

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

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

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

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

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

SQL Server Engine

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