January 10, 2012 at 7:06 pm
I am working on a SQL Server 2008 R2 with a linked Teradata server with a separate login and password. I am trying to create a job that will run as me, perform the remote login, and then do an append or maketable query on my server. The query I'm testing is:
Select * into MyDB..NewTbl
from OpenQuery(LinkedServerName, 'select * from TeradataTbl')
The first error I ran into was "Access to the remote server is denied because the current security context is not trusted", but that was easily fixed by "alter database MyDB set trustworthy on".
After that, I got the error 'EXECUTE AS USER' failed for the requested user 'MyDomain\MyUID' in the database MyDB.
I'm running the job as myself, I'm the owner of the destination database, and I'm an admin on the SQL server. The user ID on the SQL Server is my Windows login, MyDomain\MyUID, and on the remote machine is just MyUID. I can run the maketable query I'm testing in the job from SSMS just fine with no security problems.
I've been googling for fixes and haven't found anything that seems to fit yet, so any ideas would be appreciated. I'm not a DBA but I play one at work 😉
January 12, 2012 at 1:46 pm
does the SQL Server Agent Service also run as your domain user?
January 12, 2012 at 3:37 pm
It appears that the LoginName under which SQLAgent is currently running is "NT AUTHORITY\SYSTEM". However that was why I told it to run the job as me, since my Windows login is mapped to my Teradata login in the linked server.
I don't really want to map the SQLAgent's login to my Teradata login since we have 6 people that currently want to schedule or run jobs (and possibly quite a few more), and spool space limitations on the Teradata server would cause problems trying to run too many things under one user.
We have an actual network account we're going to switch the SQLAgent over to, though I don't see any reason that would fix this issue...
January 12, 2012 at 4:11 pm
In your Job step definition, what database is set up ? MyDb ?
If not, can you modify it and try again.
January 12, 2012 at 4:57 pm
I'm using a sandbox database that I own, I substituted MyDB above to make the code more clear. The database _Sandbox_Brad is selected.
This is the actual step query:
Select * into _SandBox_Brad..TestTbl
from OpenQuery(LinkedServerName, 'select * from clarity_fc')
I've tried both setting the Run As to Domain\MyUID and also doing EXECUTE AS login = 'Domain\MyUID'
January 16, 2012 at 12:15 am
I would recommend creating a credential on your SQL Server. This will be mapped to a domain login.
Then create a SQL Agent proxy account linked to the proxy and have the SQL Agent job execute under the context of the proxy account.
January 16, 2012 at 3:09 pm
I have never worked with proxy's or credentials...up until a few months ago I was using SQL Server 2000 exclusively. I'm also a developer, not really a DBA, though I pretend to be one on occasion.
Is there an easy guide on how to set those up?
Also, please confirm - if I'm reading your response correctly, you are suggesting that I change the "Run As" in the General tab to a proxy for me (currently no options appear there), instead of setting "Run as user" to my windows login in the Advanced tab or using EXECUTE AS login = 'my\windowslogin' in the command.
January 16, 2012 at 3:15 pm
Books online is actually very helpful when it comes to proxies.
http://msdn.microsoft.com/en-us/library/ms189064.aspx
I would recommend setting up a separate account for use as the proxy. You don't want things running under the context of your own user account.
January 16, 2012 at 4:51 pm
In the link you provided, one of the key passages is:
"Job steps that execute Transact-SQL do not use SQL Server Agent proxies. Transact-SQL job steps run in the security context of the owner of the job. To set the security context for a Transact-SQL job step, use the database_user_name parameter in the sp_add_jobstep stored procedure. For more information, see sp_add_jobstep (Transact-SQL)."
and when I viewed the script for the job it clearly has my windows login set up as @database_user_name.
The problem seems to be that even though the job is calling the linked server CLR_SC_ODBC via OpenQuery it doesn't make the connection between my windows login and the and password for the external server defined under the linked server, which works fine under SSMS.
I also having it call a stored procedure that uses OpenQuery rather than using it in the job step directly and got the same result.
This may be due to the SQLAgent running under a different user ID, but I have found no way (yet) to force it to connect things properly within the job.
January 16, 2012 at 5:37 pm
I just did more testing and found the following:
If I set up a simple maketable job on my database (not using the linked server at all) but use Run As 'MyDomain\MyWindowsUID' it fails, and gives me the same exact error message. So the problem isn't with the linked server per se, but that the job is somehow not running as me correctly. I'm the owner of the database and an admin on the server, there shouldn't be anything it can't do while running as me...
I also tested removing the run-as clause from the linked server job, after adding a "For a login not defined in the list above connections will be made using this security context" option into the linked server to run as me, and that worked, which means the NTAuthority\System login mapped correctly to the remote server.
So it appears the only problem is that running a job step as me prevents me from doing anything in the database that I own, while NTAuthority\System has the authority to do that, but not to connect to the linked server (unless I provide a blanket passthrough login).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply