Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Run a SQL Server Agent job under another account

Author  Topic 

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-07-04 : 10:15:07
ON my server, SQL Server Agent runs under the NT AUTHORITY\NETWORK SERVICE account. That's usually just fine and I don't want to change it. However, it means that jobs running under the agent cannot access LAN shares. That's what I would like to do in a new job. I figured I could just use an account that I know has access to the share. What I can't figure out is how to do that. I read some MS doc on setting up proxies, but I can't figure out how to make that work.

Has anyone done what I'm trying to do? If so, can you give a step-by-step to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 02:14:08
http://www.bidn.com/blogs/DonnyJohns/ssas/1705/sql-server-agent-proxy-accounts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-07-05 : 14:59:07
That's Great! I only have one other issue: I want to use Agent to run a Stored Procedure under the credentials I just set up, but I see that the "run as" option is greyed out when I select T-Sql Script as the type.

Does this mean that I simply cannot do what I want to do? Or, is there another way?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-07 : 13:07:33
Does the current login you use have system admin priviledge?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-07-08 : 10:25:46
quote:
Originally posted by visakh16

Does the current login you use have system admin priviledge?



My windows login has admin privs on the local machine. My SSMS login has SQL Server admin privs, but is only a SQL server account.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 11:32:40
quote:
Originally posted by gbritton

quote:
Originally posted by visakh16

Does the current login you use have system admin priviledge?



My windows login has admin privs on the local machine. My SSMS login has SQL Server admin privs, but is only a SQL server account.


so were you using sql authentication to login to sql server?
then that might be reason you can set proxy account as that sql account may not have enough priviledges

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-07-08 : 12:07:01
I think I'm getting closer to understanding what is going wrong for me. I set up a simple test job, type Cmdshell, that just does this:
sqlcmd -d mydb -S myserver -Q "exec xp_cmdshell 'whoami'" -E

I ran it using the proxy I had set up. It ran successfully, and produced this output:

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NT AUTHORITY\SYSTEM
NULL

(2 rows affected)

Now that is interesting. In spite of the fact that I told it to run under a proxy account and further specified option -E (use trusted connection) to sqlcmd, when SQL Server actually ran the code, it did *not* run under the proxy account with respect to Windows. Unfortunately, this is what I *need* it to do. The job I'm putting together is a stored procedure that uses xp_cmdshell to call 'dir', 'robocopy' etc. When those commands execute, they need to run under a domain account, since they will be accessing network shares.




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 13:06:43
I ran it using the proxy
Do you mean you set job properties Run as as the proxy account?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-07-08 : 14:11:22
quote:
Originally posted by visakh16

I ran it using the proxy
Do you mean you set job properties Run as as the proxy account?




yup
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:34:10
Hmm...thats a bit strange. How will it use system account if you've set it to use proxy account?
I hope the proxy account you created was actually a valid account in domain with required permissions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-07-09 : 10:01:38
quote:
Originally posted by visakh16

Hmm...thats a bit strange. How will it use system account if you've set it to use proxy account?
I hope the proxy account you created was actually a valid account in domain with required permissions




Yes, in fact, for the test, I used my normal Windows login. As you can see from the result, when xp_cmdshell executes the shell command, it does so under the Windows Account used to run SQL Server Agent, *not* the proxy you specify. Note that xp_cmdshell has no parameter to tell it to execute the shell command as some specific user, so it just executes as the user invoking the shell command -- Sql Server itself in this case. This is what I think happens:

1. Sql Server Agent starts up my job with the designated proxy.
2. My job (a cmdshell job) runs under that account, but immediately calls Sql Server to run a script using sqlcmd.
3. Sqlcmd logs in to sql server using the account info I specified (since I used -E, that would be the account running at the time sqlcmd starts up, which should also be the proxy)
4. The script immediately calls xp_cmdshell, but *that* runs under Sql Server using the same windows account running Sql Server, which it *not* my proxy!
Go to Top of Page
   

- Advertisement -