none
SQLSTATE 42000- Error 15404 with ADS

    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 already tested the suggested:

     

    execute as login='ads\me' and I get the same error on both (my local installations and production)

     

    appreciate your help

    Tuesday, August 7, 2007 2:56 PM

Answers

  •   Most likely the machine account doesn’t have permission to query the AD.

     

      I would recommend requesting access to the AD administrator or change SQL Server and run the service as a low-privileged domain account that has proper permissions on the AD.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, August 9, 2007 5:57 AM
    Moderator

All replies

  • Which service account is the SQL Server running with ?

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

     

    Tuesday, August 7, 2007 5:45 PM
    Moderator
  •  

    We are using the Local system account
    Tuesday, August 7, 2007 9:01 PM
  •   Most likely the machine account doesn’t have permission to query the AD.

     

      I would recommend requesting access to the AD administrator or change SQL Server and run the service as a low-privileged domain account that has proper permissions on the AD.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, August 9, 2007 5:57 AM
    Moderator
  • Yes, as Raul mentioned, the Local Service does not have any rights to query / check the identity of the connecting user.

    Jens K. Suessmeyer

    ---

    http://www.sqlserver2005.de

    ---

    Thursday, August 9, 2007 7:07 AM
    Moderator
  • Which kind of permissions this account needs? 

     

    • Proposed as answer by marxbiker Monday, September 19, 2011 3:14 PM
    Monday, August 13, 2007 7:58 PM
  • Members of the Domain should be enough.

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ----

    Monday, August 13, 2007 8:07 PM
    Moderator
  • I have been receiving the same error - only intermittently.  Popped up after installing SP2 on SQL 2005.  For example a job that runs every ten minutes failed at 6:10, worked until 7:30 and then failed.  Received  the same error on both -  Unable to determine if the owner of job... has server access (reason: Could not obtain information about Windows NT group/user 'ad\user', error code 0xea  [SQLSTATE 42000] (Error 15404)  

    The ad account  had administrator access on the server and in SQL.  The service account also has administrator access. 

     

    Thanks for any help,

    Sandy

     

     

     

    Monday, September 24, 2007 12:14 PM
  • Hi.

    In the company I'm working for we are planning to move from SQL Server 2000 to SQL Server 2005 but in our test environment we have the same problem. We are using a single Windows Server 2003 Active Directory. The SQL Server ist run with a standard domain user account (DOMAIN\sqlserver) without any special permissions or restrictions.

    I am not able to run any scheduled job or any maintenance plan manually with my domain user account. My domain account is member of the SQL Server's host's administrators group and member of each admin group inside the SQL Server. The scheduled jobs are working fine if I set their owner to sa but that's not possible for the manually run maintenance jobs. The SQL Server is set to Windows authentication only.

    If I add DOMAIN\sqlserver to the Domain Admins group of the Active Directory everything works fine. But this solution is inacceptable. What permissions does the service account need to access the Active Directory to check the permissions of a domain user?

    Kind regards

    Kay Zumbusch

    Monday, November 12, 2007 3:28 PM
  •  

    Hi Sandy

     

    My Maintenance Plan for backups stopped working after some security patching. Same error message. Did you ever resolve your issue?

     

    Thanks

    Ruth

    Friday, November 21, 2008 1:57 PM
  • I'm seeing the same issue within reporting services with schedule reports (which creates jobs within sql server agent).  It creates the job fine, but I get the same error as listed in this forum when it tries to execute.  The User is a domain user.  The only work around I have is after someone creates a scheduled report to update the sql agent job with a different owner.  I'd like to not have to do this, so has anyone found a real solution?

     

    Thanks,

    Sean

    Tuesday, December 2, 2008 2:50 PM
  • This is still an issue in SQL Server 2005 SP3+ (9.0.4035 in my case).

    I'll be very interested when/if Microsoft comes up with a workable solution.
    Fred Morrison
    Friday, June 12, 2009 1:56 PM
  • Most likely this is being caused by a lack of permissions.  And it didn't seem to be resolved with a definitive answer.  If you see this error try adding the DOMAIN\SQLSERVER machine account to the pre-windows 2000 access group.  This may not solve the problem for everyone, but did for me.
    "The only thing necessary for a truth is a question."
    • Proposed as answer by Martin Payne Wednesday, November 18, 2009 12:25 AM
    Monday, July 27, 2009 8:17 PM
  • I had this problem and found that, if I created the maintenance plan while authenticated to the Management Studio as a user of the same domain that the SQL Server was running on, the maintenance plan was successful; even if I then modified it while logged on as a user of another domain.

    A little further info - my machine (the one I run Management Studio on) is logged on as domainA\username1.  The SQL Server I am connecting to is running in domainB.  I can connect with domainA\username1 because it is set as an administrator on the SQL Server machine.  However, when I create a new maintenance plan while logged in as domainA\username1, I get this problem.  I tried 'EXEC AS' and creating a new connection that used SQL Server authentication, but to no avail. 

    I then added domainB\username2 as an admin on the SQL Server machine, logged into that machine and created a maintenance plan that worked.  I also found that, going back to my machine and logging in as domainA\username1, I could modify the maintenance plan and it still worked.

    Good luck
    Wednesday, November 18, 2009 12:36 AM
  • This same error has started cropping up in several of my production servers:

    The job failed.  Unable to determine if the owner (AMERICAS\$svcpnbsqlprod001) of job UDMaintenancePlan.DBA-Trans Log Backup for All User Databases has server access (reason: Could not obtain information about Windows NT group/user 'AMERICAS\$svcpnbsqlprod001', error code 0x5. [SQLSTATE 42000] (Error 15404)).

    This is our production service account which is used for the Logon Account for both the Database Engine and Agent services.  It has Local Server SysAdmin privileges on the box, and SysAdmin privileges on the SQL instance, and it is not locked out at the Active Directory level.  Nothing has changes with these accounts at the local level, and these jobs have been running without error for 18+ months.  I can only assume that something at the AD level has changed, but when I looked at the account settings using the AD Users & Computers MSC I saw nothing out of the ordinary.

    I've been researching this error and have found several forum messages describing the issue in different contexts, but I haven't come across any solutions yet.  Has anyone encountered this problem and found a viable solution?

    Brandon.Forest@hp.com
    Senior SQL Server DBA

    Tuesday, November 24, 2009 10:10 PM
  • I have a similar problem:

    "Unable to determine if the owner (sa) of job eBlast Engine Check for Audiences to Sync has server access."

    I had restarted one of my 3 local AD domain controllers yesterday and after that all SQL Agent Jobs I have started failing with an error similar to this.
    I restarted the SQL Agent Service and now it's all working again.

    The issue seems to be if connectivity is lost to a AD Domain Controller it loses it's ability to authenticate users for the SQL Agent Jobs.

    My expected behavior would be that worst case the SQL Agent Jobs would fail until the AD Domain Controller is available again... then it should automatically start working again.

    -Phil

    Any Ideas on a fix???
    Thursday, January 28, 2010 8:32 PM
  • **** THE ANSWER ****

    I have just had the same problem. I have read all over the internet about this but it took our IT engineer who knows about SQL Server and AD to point out that I had set the SQL Agent to use a domain account but it was acutally the SQL Server service that brokers the requests to AD.

    To resolve this problem, ensure that the SQL Server service account under "" is running under a domain user account.

    I believe you can leave the SQL Agent running under a restricted local server account.

    This enables you to run jobs that are owned by a domain account and so they should be able to access network resources.

    Regards

    Peter

    Liverpool PCT, NHS, England

    Thursday, June 3, 2010 2:22 PM
  • For future readers of this problem, another possible cause is if the account trying to execute a job is locked out.

     

    • Proposed as answer by axaxaxaxax Monday, December 3, 2012 2:20 PM
    Wednesday, August 4, 2010 3:28 PM
  • Also note for this very similar error:

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

    the ownership of the job and maintenance plan must be correct.  See http://www.sqlservercentral.com/articles/SQL+Jobs/68764/ for more info.

    Tuesday, August 31, 2010 5:38 PM
  • In this case, you can create a job with the SQL agent service account as job owner and JOb steps can use AD account to run the job steps. This will resolve the problem.


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    Friday, March 2, 2012 5:55 PM
  • Hi All,

    It's probably permissions' problem. The best way to solve this is detecting which account under Security\Logins that has the db_owner permissions and map it to a user with enough permissions to perform the backups. That is for specific database you need to backup, probably the account permissions is changed if it's local, can be due to some application update or new install.

    To solve the problem, the best of practice is mapping the account with db_owner to a local account with enough permissions (if backup is local) or to a domain backup operator (if backup is remote or network).

    Hopefully that helps!

    Monday, September 10, 2012 3:13 PM
  • I had the same issue.

    Looks like the security patching needed a reboot. As the domain control was restarded the same time for the same reasons (more or less), The SQL SERVER couldn't make the link with AD and wasn't able to mount it after time.

    A simple reboot of the SQL SERVER and all came back working again.

    So try first to do a simple reboot of your SQL SERVER before making any change in configuration (specially if it was working before) and try to avoid rebooting Domain Controler and other Server on the same time.

    Monday, June 22, 2015 7:59 AM
  • Stop SQL Server & SQL Server Agent services and Start  with  "Local System Account" or Windows AD account instead of    "NT Service\MSSQLserver".
    Sunday, September 6, 2015 6:24 AM
  • I have a similar error on a virtual machine.  In this case, the account that it uses to run the job, has a cached profile.  The cached profile cannot be found in the AD.

    R, J

    Tuesday, March 7, 2017 1:40 PM
  • This fix worked for me. I found that any time a SQL Job runs under a Windows AD Account we also need to ensure the SQL Service and SQL Agent Service are also running under Windows AD Service Accounts or the job results in this error.

    This is not required if the job can run under SA or some other SQL authentication.  In our case, the job needed to run as a domain account, so flipping the services over to domain accounts was our fix.

    • Proposed as answer by BlairMueller Tuesday, February 20, 2018 10:06 PM
    • Unproposed as answer by BlairMueller Tuesday, February 20, 2018 10:06 PM
    Tuesday, February 20, 2018 10:06 PM