<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Execute SQL Job with Proxy

Published on
12,218 Points
5,318 Views
4 Endorsements
Last Modified:
Approved
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.



Thanks,

Alpesh
4
Comment
  • 2
2 Comments
 

Administrative Comment

by:MHenry
PatelAlpesh,

I'll be helping you to get your article published. I'll review it shortly.

Best,
MHenry
PE
0
 

Administrative Comment

by:MHenry
PatelAlpesh,

Congratulations, you're article has been published.

Thanks for contributing to Experts Exchange.

Best,
MHenry
PE
0

Featured Post

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Join & Write a Comment

Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month