SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Run a SQL Server Agent job under another account
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gbritton
Posting Yak Master

149 Posts

Posted - 07/04/2013 :  10:15:07  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/05/2013 :  02:14:08  Show Profile  Reply with Quote
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
Posting Yak Master

149 Posts

Posted - 07/05/2013 :  14:59:07  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/07/2013 :  13:07:33  Show Profile  Reply with Quote
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
Posting Yak Master

149 Posts

Posted - 07/08/2013 :  10:25:46  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/08/2013 :  11:32:40  Show Profile  Reply with Quote
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
Posting Yak Master

149 Posts

Posted - 07/08/2013 :  12:07:01  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/08/2013 :  13:06:43  Show Profile  Reply with Quote
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
Posting Yak Master

149 Posts

Posted - 07/08/2013 :  14:11:22  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/09/2013 :  01:34:10  Show Profile  Reply with Quote
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
Posting Yak Master

149 Posts

Posted - 07/09/2013 :  10:01:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000