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
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
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.
2. Next, let's give permissions to Windows User for the particular folder using the SSIS package.
Right Click on Folder => Properties=> Security Tab
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
This opens a new dialog box as seen below:
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
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