<

Execute SQL Job with Proxy

Published on
12,041 Points
5,141 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

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month