If you have any experience with Networking I’m sure you have heard of Proxy Servers before. Proxy Servers act as an intermediary between clients, and requests the resources for the client.
SQL Server Agents have a Proxy feature that works in the same way. When you add a SQL Proxy account you are letting your SQL Agent jobs “Run-As” the account specified.
Why would we want to? This is a very common “gotcha” for people when they first start Setting up SQL Agent Jobs. The package runs successfully in the BIDS environment, but then it fails when they deploy the package and schedule the job to run. It’s not uncommon for our packages to require access to resources through the file system, or even on other servers. Very often we do not want to just elevate the NETWORKSERVICE account used to run the SQL Server Agent Jobs. So its time to run the job as someone else with Stored Credentials.
Quick Note: T-SQL Jobs will not Run-As Proxy Users. They will run with the security credentials of the Job Owner.
First things first, We will need Credentials for the Proxy to use:
To Create Credentials, Expand Security, then Right-Click Credentials and select New Credential
You can Name the Credentials whatever you like, but be sure to use an account that you know with sufficient rights to run your Jobs.
Next we will create a New Proxy, by Expanding SQL Server Agent, then Right-Clicking Proxies and selecting New Proxy
When you create the Proxy you can Name it whatever you want, but the Credential Name is what ties it to the Credentials
Also, ironically the Credentials never verify the account you setup, but the Proxy confirms it’s a valid account, so you won’t know if you messed up until now.
Now your Proxy Account should show up wherever it is assigned to be used
When we are setting up our Jobs now we have an additional option available in the Run As field-
That’s it! Of course another thing to mention is if you are using a Domain account with a password policy in place you will need to update the password for your Credentials manually.
Hopefully this saves some people from a couple headaches!
Thanks for sharing this useful post
Very clear, thanks.
Cool, Thanks a lot for sharing.
it was really helpful for me..
Thanks a lot for sharing this.