none
SQL Server Agent Job Owner Domain Account Errors

    Question

  • We are receiving errors on SQL Agent jobs owned by domain users.

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

    Setup: SQL Server 2K8R2 Standard on Windows 2K8R2 64x Enterprise.

    We have 2 domains, HOSTING_DOMAIN is a hosting related domain housing our production servers. We also have an INTERNAL_DOMAIN which hosts all our corporate users & desktops/laptops. We have a one way trust between HOSTING_DOMAIN => TRUSTS => INTERNAL_DOMAIN. Both the SQL Service and the SQL Agent service are starting up as a HOSTING_DOMAIN\SERVICE_ACCOUNT user.

    We setup INTERNAL_DOMAIN\USERs all the time inside of SQL Servers running in the HOSTING_DOMAIN. The INTERNAL_DOMAIN users authenticate into HOSTING_DOMAIN SQL servers just fine. The issue comes in that we have end users needing to control SQL Agent jobs, so we grant them SQLAGentOperatorRole, SQLAgentReaderRole and SQLAgentIserRole roles. They are able to create jobs, but then they go to run them they get the above error. The SQL Agent job itself is owned by their INTERNAL_DOMAIN\USER account as expected, and that same user is authenticated into SQL Server already, so it appears that the HOSTING_DOMAIN\SERVICE_ACCOUNT account that both SQL Server service and SQL Agent service can communicate properly with the INTERNAL_DOMAIN AD control(s), but we still get the above error.

    We have googled the hell out of this and read all about the "work around"s of changing the owner of the job to "sa" or "try sysadmin", etc, which admittedly those things do work, but we require these users to be standard non-SysAdmins and require that these users can manage their own SQL Agent jobs.

    Any feedback would be greatly appreciated!

    Tuesday, November 27, 2012 5:34 PM

Answers

  • If I understand your reference earlier, you imply that the code fails to connect from SQL Server Agent, but runs fine if you are logged in and running the same code.  Is that correct, or did I misunderstand?

    If I understood correctly, per Erland's "No! When a user logs into Windows, he presents his credentials: username and password." then within the SQL Server Agent job using a SQL Agent Proxy may be a solution.

    Aside from T-SQL job steps, all the other job step types support a SQL Agent Proxy, which (to work properly) needs a Credential to be created and used by the proxy.  The credential is under the SSMS Security tab for a database.  In the credential you define a domain login and its password.

    Once you have defined the proxy and credential properly, instead of "impersonating the user in question", the job step will actually log into windows users the credential's username and password.   Once it has done that, then the security picture is pretty much the same as when you login and run the command interactively.

    If your job step is a T-SQL job step, you can convert it to an Operating System step which does support the proxy.

    -- T-SQL Job Step 
    EXEC MySpecialProcedure
    GO

    You just need to change this to an Operating System step (with the proper proxy, of course) like this:

    SQLCMD -Q "EXEC MySpecialProcedure" -S LocalServer -d LocalDB -E

    The -E is a trusted connection for the SQLCMD, which means that the Proxy's Credential having logged into Windows will be the login that runs the SQLCMD step without any impersonation.

    All the best,

    RLF

    Thursday, November 29, 2012 2:57 PM

All replies

  • You can use new roles that introduced from SQL server 2005(Applies +2005 also)-

    SQL Server 2005 introduces the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

    • SQLAgentUserRole
    • SQLAgentReaderRole
    • SQLAgentOperatorRole

    When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.

    Link->

    SQL Server Agent Fixed Database Roles

    http://msdn.microsoft.com/en-us/library/ms188283%28v=sql.90%29.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, November 27, 2012 7:52 PM
  • Yep, thx, but as noted in original post, already have those roles in play for these users.
    Tuesday, November 27, 2012 7:55 PM
  • Ross,

    You are right,  any recent modification happened at the domain level?


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, November 27, 2012 8:26 PM
  • Nope, we haven't touched either domain or its trust / rights / users forever. I believe we have always had this issue, but we were able to work around it with the"change the job owner to SA" work around.  I'm not confident that this specific auth issue has ever worked, but the standard INTERNAL_DOMAIN\USER logging in and working inside SQL Server has always worked (and continues to work fine).

    Doing more testing found that I can reproduce the error using the EXECUTE AS command...

    Example, doing

    REVERT;
    SELECT SUSER_NAME(); 

    Shows INTERNAL_DOMAIN\USER as expected.

    REVERT;
    EXECUTE AS LOGIN = 'INTERNAL_DOMAIN\USER';
    SELECT SUSER_NAME();
    GO

    This throws error: "Msg 15404, Level 16, State 19, Line 3 Could not obtain information about Windows NT group/user 'INTERNAL_DOMAIN\USER', error code 0x5" even though this is the same exact user that I'm already auth'd in with so we know it works....
    Tuesday, November 27, 2012 8:58 PM
  • Not that I claim to know this, but 0x54B = 1355, and NET HELPMSG 1355 tells me:

    The specified domain either does not exist or could not be contacted.

    From what you say, it seems that HOSTING_DOMAIN trusts INTERNAL_DOMAIN, but not the other way round. But when Agent is to run the job, it tries to talk with INTERNAL_DOMAIN to verify the user. But since INTERNAL_DOMAIN does not trust HOSTING_DOMAIN, it turns a blind eye to the request.

    I am really out on a limb here - Windows security is certainly not my field of expertise.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 27, 2012 10:32 PM
  • Well, we don't have it solved but I think we are getting close. What we believe is going on is some aspects of SQL server require kerberos delegation to function correctly. We found on our firewall that communications to our INTERNAL_DOMAIN are working fine, but we're reading more and more that if kerberos delegation is required, a TWO WAY trust is required to get this to work, and since we are only able to allow a one way trust, this is probably whats breaking the delegation process.

    I'm confused why kerberos delegation would be required in this instance since we aren't attempting to pass creds across 2 hops, or if it is, what 2 hops are involved? Our understanding is a SQL Agent Job owned by a user running on a single SQL Server and only operating on the single SQL Server server should be no different than that same user logging into SQL Server under normal circumstances (which we know works) and running the TSQL manually.

    Kerberos delegation makes total since when dealing with like Linked Server connections as described in this article <http://www.mssqltips.com/sqlservertip/2312/understanding-when-sql-server-kerberos-delegation-is-needed/> but we're stumped why this would effect our situation here....

    Wednesday, November 28, 2012 8:07 PM
  • Our understanding is a SQL Agent Job owned by a user running on a single SQL Server and only operating on the single SQL Server server should be no different than that same user logging into SQL Server under normal circumstances (which we know works) and running the TSQL manually.

    No! When a user logs into Windows, he presents his credentials: username and password. Later, when he logs into SQL Server, Windows vouches for him and passes the security token.

    When SQL Server Agent runs a job for another user, there is no password, but Agent impersonates the user in question. I can't but see that to get the valid security token and that, SQL Agent must talk with the domain that owns the user to get the security token. And without it, Agent cannot log into SQL Server as that user. Because else SQL Server cannot know which Windows groups the user is a member of. And of that matter, if the user still exists in the AD.

    Kereberos may have something to do with it. The key is that Agent needs to talk to the AD in a domain that does not trust the domain Agent is coming from. But since I'm not a Windows guy, I don't know the exact rules.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, November 28, 2012 10:39 PM
  • Thx Erland, I think your on the right track and it makes sense when you explain it that way. We are still investigating, but based on the mountain of research and Google searches, it appears its coming down to a issue with the kerberos delegation. Based on sparse info, we have seen it stated in 3 spots now that for the kerberos delegation to work correctly a full two way trust between domains is required, but, based no our security requirements, we can only allow a one way trust between our HOSTING_DOMAIN and INTERNAL_DOMAIN. 

    We are at the point now of either dealing with "work around" like using SQL user(s) for the end user to manage SQL jobs, or, getting Microsoft involved to see if there is any means of getting the SQL Servers usage of kerberos delegation to work with only the one way trust. Some readings seem to hint it can be done, but doesn't elaborate on how, or in what circumstances...

    Thx for everyones feedback, if we come to a final resolution I'll be sure to post it! 

    Thursday, November 29, 2012 2:22 PM
  • If I understand your reference earlier, you imply that the code fails to connect from SQL Server Agent, but runs fine if you are logged in and running the same code.  Is that correct, or did I misunderstand?

    If I understood correctly, per Erland's "No! When a user logs into Windows, he presents his credentials: username and password." then within the SQL Server Agent job using a SQL Agent Proxy may be a solution.

    Aside from T-SQL job steps, all the other job step types support a SQL Agent Proxy, which (to work properly) needs a Credential to be created and used by the proxy.  The credential is under the SSMS Security tab for a database.  In the credential you define a domain login and its password.

    Once you have defined the proxy and credential properly, instead of "impersonating the user in question", the job step will actually log into windows users the credential's username and password.   Once it has done that, then the security picture is pretty much the same as when you login and run the command interactively.

    If your job step is a T-SQL job step, you can convert it to an Operating System step which does support the proxy.

    -- T-SQL Job Step 
    EXEC MySpecialProcedure
    GO

    You just need to change this to an Operating System step (with the proper proxy, of course) like this:

    SQLCMD -Q "EXEC MySpecialProcedure" -S LocalServer -d LocalDB -E

    The -E is a trusted connection for the SQLCMD, which means that the Proxy's Credential having logged into Windows will be the login that runs the SQLCMD step without any impersonation.

    All the best,

    RLF

    Thursday, November 29, 2012 2:57 PM