Execute SQL Job with Proxy

Published on
12,714 Points
4 Endorsements
Last Modified:
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful.

Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem when you have created an SSIS package that is running perfectly with BIDS, however, when you execute using SQL Job, it fails. Why does this error happen? It’s probably because you are accessing a directory or directories which do not have the correct permissions for SQL Agent User then SQL Agent (SQL Job) through exception.

To solve this problem you need to create a proxy and execute SQL Job using the Proxy User. Let's go through this step by step below:

1. A Proxy credential needs to be created first. Look at the image below to see how to create a credential using the existing Windows user.

Like this: Server => Security => Credential=> New Credential

 Step 1.1

Create Credential using the Windows user. To do that, click on the button beside “Identity” and select Windows user for whom you have assign the folder (Directory) permissions. Create the password and hit OK.

 Step 1.2

2. Next, let's give permissions to Windows User for the particular folder using the SSIS package.

Right Click on Folder => Properties=> Security Tab

 Step 2

Assign permissions as shown in image above and click OK until all dialog boxes are closed.

3. Now it’s time to create Proxy.

Click on SQL Server Agent => Proxies => SSIS Packages => New Proxies

 Step 3.1
This opens a new dialog box as seen below:

 Step 3.2
Select sub system (SSIS Packages) and existing (Just created) Credential as shown in image above and close the dialog boxes.

4. Let’s use the Proxy in SQL Job

 Step 4
Create the SQL Job as usual: Right click on Jobs folder under SQL Server Agent => Create Job

Select Proxy instead SQL Server Agent user as shown in above image.

I hope, you will find this article helpful.


Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free