none
SQL JOB QUESTION

    Question

  • When I connect to a SQL Server agent using My NT login and manually start a SQL Job from sql agent, does the SQL Job run under my NT profile or does it run under the Job owner profile or does it run under SQL Agent acct?
    Thursday, September 17, 2009 3:02 PM

Answers

  • See Tibor's answer.

    Ekrem Onsoy - MCDBA, MCITP:DBA, MCSD.Net, MCT, SQL Server MVP
    • Marked as answer by Desigal59 Thursday, September 17, 2009 3:47 PM
    • Edited by Ekrem Önsoy Friday, September 18, 2009 6:04 AM
    Thursday, September 17, 2009 3:37 PM
  • What job step type?

    For TSQL, Agent uses its own login (Windows authentication), and then uses EXECUTE AS LOGIN = 'n' command to simulate you. For other job step types, Agent uses Proxy and Credentials. You can trace this using Profiler, and even run a jobstep having WAIRFOR and check using sp_who etc.

    Unless job owner is sysadmin, where no impersonation is performed (Agent will be "himself"). You can "trace" this ouputting result from whoami.exe (or similar utility) to an output file.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Ekrem Önsoy Friday, September 18, 2009 6:03 AM
    • Marked as answer by Kalman TothEditor Saturday, November 21, 2009 11:15 PM
    Thursday, September 17, 2009 6:45 PM

All replies

  • See Tibor's answer.

    Ekrem Onsoy - MCDBA, MCITP:DBA, MCSD.Net, MCT, SQL Server MVP
    • Marked as answer by Desigal59 Thursday, September 17, 2009 3:47 PM
    • Edited by Ekrem Önsoy Friday, September 18, 2009 6:04 AM
    Thursday, September 17, 2009 3:37 PM
  • Really?

    I had an issue today where I connected to the server and logged in with Windows Authentication and ran a job which called a stored procedure.  This stored procedure had been changed to reference a table in a different database.

    But the job owner was not an admin, so the job failed.  Once I added the login as a user in the new database and assigned read permissions, the job then ran.

    So in this instance, it ran under the job owner context.

    Thursday, September 17, 2009 4:22 PM
  • What job step type?

    For TSQL, Agent uses its own login (Windows authentication), and then uses EXECUTE AS LOGIN = 'n' command to simulate you. For other job step types, Agent uses Proxy and Credentials. You can trace this using Profiler, and even run a jobstep having WAIRFOR and check using sp_who etc.

    Unless job owner is sysadmin, where no impersonation is performed (Agent will be "himself"). You can "trace" this ouputting result from whoami.exe (or similar utility) to an output file.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Ekrem Önsoy Friday, September 18, 2009 6:03 AM
    • Marked as answer by Kalman TothEditor Saturday, November 21, 2009 11:15 PM
    Thursday, September 17, 2009 6:45 PM
  • Then I probably remember it wrong because I usually deal with sysadmin accounts and I don't have time for now to perform some tests to confirm but I trust your words Tibor =)
    Ekrem Onsoy - MCDBA, MCITP:DBA, MCSD.Net, MCT, SQL Server MVP
    Friday, September 18, 2009 6:03 AM
  • Awesome!  Thanks!
    Friday, September 18, 2009 7:54 AM
  • Hi Ekrem,

    I also usually use sysadmins for jobs. The reason I remember this is that I used to demo this in class. However, since that was a long time ago, I decided to re-check whether this is still the case - which turned into a blog: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx. :-)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Saturday, September 19, 2009 10:24 AM
  • Hi Tibor, thanks a lot for this blog!!!. This is the first blog which I have run into which exactly details how the SQL job is executed when the job owner is not sysadmin. I have a requirement where a job owner (who is not sysadmin) should be able to access linked server, but I am finding that it doesnt work... I will really appreciate if you can provide some tips... I am stuck in this for a week now without success....

    I have an user who is a member of SQLAgentUserRole in a SQL instance on SQL Server 2008 R2 SQL server. As per my requirement, this SQLAgentUserRole should be able to create SQL jobs and execute a query with linked server in the SQL job( see linked server - ServerLink  as in query below):

    select top 10 * from ServerLink.DBTest.dbo.OnlineFact

    But if the SQLAgentUserRole user creates a SQL job, and executes the above query(containing the linked server) the SQL job fails with error (see error message below). I investigated the issue and find that a “SQLAgentUserRole member cannot execute linked server query in SQL job” is by design as per this this MSDN KB Article http://support.microsoft.com/kb/811031 . I do not want to grant Operating System Command using a SQL Proxy for  SQLAgentUserRole members as listed as workAround in the MSDN KB Article. What are my options in order to enable a SQLAgentUserRole query linked servers in the SQL job?

    Error Message 1

    Executed as user: <domain>\<user>. Shared Memory Provider: Either a required impersonation level was not provided, or the provided impersonation level is invalid. [SQLSTATE 42000] (Error 1346)  OLE DB provider "SQLNCLI10" for linked server "ServerLink" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "ServerLink" returned message "Communication link failure". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "ServerLink" returned message "Communication link failure". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "ServerLink" returned message "Communication link failure". [SQLSTATE 01000] (Error 7412).  The step failed.

    Error Message 2 - I get this error message sometimes as well.

    Executed as user: <domain>\<user>. SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. [SQLSTATE 42000] (Error 65535)  OLE DB provider "SQLNCLI10" for linked server "ServerLink" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "ServerLink" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.

    Items I have tried unsuccessfully:

    1. OPENQUERY also uses a LINKED SERVER, but as I detail in my original problem, the SQLAgentUserRole user is unable to query a LINKED SERVER in a SQL job. Hence the OPENQUERY execution using LINKED SERVER fails.

    2. I have tried EXECUTE AS CALLER, SELF, OWNER and a SPECIFIC LOGIN. None of these work.

    3. I also tried setting the "Run AS" option in the SQL Job Step Advanced Section, but that didnt help as well.

    4. It is almost the SQL JOB owner's credentials is unable to pass over when trying to "reach/recognize" the linked server just locally. I also tried the SECURITY impersonation in linked server, but it looks like the issue is when just trying to reach the linked server locally, and not connecting to the linked server yet.

    Note:

    1. The SQL job owner is a sysadmin in the target server of the link server connection. Therefore SQL job owner already has permissions to access the target  server of the link server connection. The issue seems that the SQL Job Owner's credentials do not propagated when reaching the linked server, and hence the network protocols issue (see error message #2).

    2. Also, currently when I build the Credential for a Proxy, I provide the same account as the job owner, to build the SQL credential. Using this proxy/credential, I can get the SQL job to work. This implementation where the Credential for the Proxy is the same account as the job owner proves that the SQL job owner can connect to the linked server, if the job owner's credentials is correctly passed by SQL server when accessing the linked server.

    Wednesday, February 08, 2012 3:20 PM