| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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? |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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'" -EI 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-08 : 13:06:43
|
| I ran it using the proxyDo you mean you set job properties Run as as the proxy account?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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 proxyDo you mean you set job properties Run as as the proxy account?
yup |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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! |
|
|
|
|
Run a SQL Server Agent job under another account