Home


Professional Services











Subscribe To This Site




SQL Agent Proxies � Proxies for SSIS


Scenario

SQL Agent Proxies

You have a SQL Server running with the SQL Server Agent and the SQL Service running under the local system account. A new database is to be created that will need to interact, through an SSIS package run through the SQL Agent, with another server in the domain; it will be reading data from files stored in a share on the other server into the new database. Now unless the DBA wants to change the account under which the SQL Agent is running, the DBA has a problem. How can the SSIS job access the share on the other server in the domain? The service is account on the SQL Server is the local system account and hence will not have permissions to access the network share. In SQL Server 2005 the answer to this problem is using a proxy account and this article will talk you through setting up a proxy account to execute an SSIS job.

It should be noted here that you can create proxy accounts to access other subsystems (This is article is SSIS specific but the process of creating a proxy is similar for all subsystems the agent will be accessing):

  • ActiveXScript
  • OperatingSystem (CMDExec)
  • Replication Distribution
  • Replication Merge
  • Replication Queue Reader
  • Replication Snapshot
  • Replication Transaction-Log Reader
  • SQL Server analysis services command
  • SQL Server Analysis Query
  • SQL Server Integration Services Package (SSIS)

SQL Agent Proxies - Create a Domain Account

The first task is to create a domain account with the necessary permission on the share in question. I have assumed that you know how to do this, if not you need help engage a sys admin or other DBA with the knowledge to assist you. Google �Creating a domain account� if you have nobody to help you.


SQL Agent Proxies - Create a Login

Once you have a domain account created with the appropriate permissions that you need, you need to give that account access to SQL Server by creating a Windows Login on SQL Server. This can be done in management studio:
  1. Connect to the SQL Server in question with an account with sufficient permissions.
  2. Expand the security folder
  3. Right click on the
  4. Select
  5. On the Login � New grant the windows login created above access to the SQL Server by completing the details on this screen.
  6. Click ok when finished
If you prefer you can use T-SQL to achieve the same results:

CREATE LOGIN [GETH-PC\geth] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

SQL Agent Proxies - Create a Credential

Having created a login for you Windows account on SQL Server the next step is to create a credential. The credential stores the user name and password for the windows login, which is used to authenticate the user when the SSIS package runs as part of job.To create a credential carry out the following steps in Management Studio:
  1. Connect to the SQL Server in question with an account with sufficient permissions.
  2. Expand the security folder
  3. Right click on the
  4. Select
  5. Enter a name for the credential
  6. Enter an identity for the credential...this will be the windows account you created above
  7. And enter and re-confirm the password for that account.
The following script is the T-SQL equivalent that will allow you to create the credential

USE [master]GOCREATE CREDENTIAL [SSIS_Test] WITH IDENTITY = N'GETH-PC\geth', SECRET = N'******'GO

SQL Agent Proxies - Create a Proxy

The next step is to create a proxy in the SQL Server Agent. To do this in management studio you need to carry out the following steps:

  1. Connect to the SQL Server in question with an account with sufficient permissions.
  2. Expand the
  3. Expand the folder
  4. Right click and select
  5. Complete the you can pick the credential created above from the server list of credentials by clicking on the ellipsis button next to the credential name text box. You also have to select an option in the active subsystem section, in this case we need to select the tick box.

The above task can be completed using T-SQL. The following code will create a proxy based on the credential and login created above.

USE [msdb]GOEXEC msdb.dbo.sp_add_proxy @proxy_name=N'TEST',@credential_name=N'SSIS_Test', @enabled=1, @description=N'Demo Proxy'GOEXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'TEST', @subsystem_id=11GOEXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'TEST', @login_name=N'GETH-PC\geth'GO

SQL Agent Proxies - Your Proxy is Created

The final task is to change the SQL Agent job that calls your SSIS package to use the SQL Agent Proxies. (assuming the job is already created)To do carry out the following tasks:
  1. Connect to the SQL Server in question with an account with sufficient permissions.
  2. Expand the
  3. Expand the Jobs
  4. Right click on your job name
  5. Select
  6. Select the Page
  7. Select the step that runs the SSIS package, select the button at the bottom of the screen
  8. On the ensure the that proxy we created above is selected from the drop down list. Click and to make the change to the job.If the job does not yet exist, when you create the job to call the SSIS package ensure that the proxy created above is selected in the RUN AS box.

SQL Agent Proxies - Conclusion

Proxies are useful ways of allowing SSIS packages and other SQL Server sub systems to be executed through the SQL Server Agent using defined security specifically for that particular Windows account. Before the SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

SQL Agent Proxies - Note on Writing to the Event Viewer

Just a small note here, while testing the running of an SSIS package through the SQL Server agent using a proxy account there was a script task written by a developer that amongst other things wrote out to the event viewer when an error occurred in the task. The windows account used in the proxy did not have permissions to write to the event log so we got this error in the Job in the SQL Server agent �The script threw an exception: requested registry access not allowed.� If you need the user to write to the event log, you can either grant the user local admin rights (NO!!!!!) or you can give the user permissions to the necessary registry keys. I would always evoke the help of a Windows Sys admin when doing this.

footer for SQL Agent Proxies page