May 11, 2010 at 5:17 pm
Using SSMS, I logged into my SQL 2008 named instance using a SQL account. The SQL account is a sysadmin.
While in SSMS, I created a stored proc (MyProc) that accesses tables in my local SQL database, as well as tables in several databases that reside in 4 linked servers (LS1, LS2, LS3, and LS4). Within SSMS, the stored proc successfully runs to completion, and without any errors.
I then created a SQL Job that is owned by the same SQL account that I used to log into SSMS. The SQL Job has been given one transact SQL step to perform: EXEC MyProc. When the Job attempts to run, however, it encounters the following run-time error:
Sql Severity16
Sql Message ID7412
Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "SQLNCLI10" for linked server "LS1" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "LS1". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI10" for linked server "LS1" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). The step failed.
I'm not sure why it is trying to run under the Network Service account. I have tried modifying numerous server settings, but with no success. Has anyone seen this problem and can you offer some suggestions?
I saw another post like this one on this forum, but it went unanswered. If I have posted this questions within the wrong forum, please direct me to the correct forum.
TIA.
May 11, 2010 at 5:26 pm
What account is your SQL Agent service running under? Network Service or something else?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 11, 2010 at 7:06 pm
If your account is a member of the sysadmin fixed server role, here are the execution contexts:
- Stored procedure run from SSMS -> your user context
- Stored procedure run by SQL Server Agent -> SQL Server Agent service context
So if the SQL Server Agent service account is set to Network Service, then the execution context makes sense. You can get around this by creating a credential, specifying your username/password, and setting that credential as the execution context for the SQL Server Agent job.
K. Brian Kelley
@kbriankelley
May 12, 2010 at 7:27 am
Jason & Brian,
Your question makes perfect sense; under what context does the SQL Agent run? Under the Network Service account. Thanks for clueing me in.
But what I don't understand is where I apply the new credential. I am thinking that what you mean is that I should open the existing SQL Job, go to the Steps page, click Edit on the job step, and from the Run As dropdown list, select my new credential. But that list is always empty, so I must be misunderstanding what you are saying.
I know my new credential is valid, b/c I can successfully assign it to a proxy. If I am suppose to set the Run As box as I mention above, I don't understand why the dropdown list is empty.
May 12, 2010 at 10:57 am
This may be a good resource for you:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69441/
The article discusses creating a new credential. You can skip the SSIS stuff if you like.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 11:15 am
Jason & Brian,
I will look into the following resource:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69441/
In the meantime, what I have done in place of using a new credential is that I went back to the properties of each linked server, and on the Security page, I added the Network Service account as a Local Login, and mapped that account to a SQL account as the Remote User and Password. I already had my local SQL account within this list and it is mapped to SQL accounts on the remote servers. By adding the Network Service account as well, and mapping it to the respective SQL accounts on each remote server, the SQL Job now runs successfully, with no further run-time errors.
By adding the local Network Service account to this Security page, I don't think this will create any potential security holes on my SQL Servers, but if you think otherwise, please let me know.
Thank you
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply