Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Run an SSIS Package Under a Different Account

By Polar Bear,

Recently, in a SSIS package, I needed to get some data from a SQL Server database which is used by a third party application. This application uses named user license. The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance. If we set ETLadmin to an application admin, this will waste an application admin license. And usually ETLadmin account should only have read permission to grab data. Application admin permission will be too much for this account. It will be nice if we can use an existing application admin user account to run the package. This can be done by using SQL Server Agent proxies.

Here are the steps to setup a proxy by using an existing application user account:

Create a credential using the account that having right access to the application database

  • Open SSMS, connect to the SQL Server instance that the SSIS will be scheduled to run
  • Go to Security - > Credentials, and click on 'New Credential...' to create a new credential

  • Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.
  • Click 'ok' and the new credential should be listed.

Create a proxy using the credential created in the previous step

  • Right click SQL Server Agent -> Proxies, and select 'New proxy...'
  • Enter the new proxy name, and choose the credential that created from the previous step from the dropdown list. And check 'SQL Server Integration Services Package' under 'Active to the following subsystems'. The SQL Server Agent proxy can be activated for many sub systems (as listed in the screen shot). In this case we are only enabling it for SSIS packages.
  • Click 'OK' and the new proxy should be listed.

Setup a job to run the SSIS package using the proxy

  • Open the job step properties for the step that run the SSIS package
  • Select the proxy that created in the previous step from the 'Run as' dropdown list
  • Click 'OK' to save the change

Now you can run the package using the application account.

By using SQL Server Agent proxy, we can run jobs on different databases, different servers using existing accounts, and avoid giving excessive permission to ETL users or developers.

 

Total article views: 12096 | Views in the last 30 days: 54
 
Related Articles
FORUM

Proxy Account

How to create proxy account.

FORUM

sp_xp_cmdshell_proxy_account gives a error, need help....

when icreate proxy account ...

FORUM

SSIS Proxy/Credential usage - Encountering issues

How to use SSIS proxy account and credentials?

FORUM

ERROR CREATING PROXY ACCOUNT....

ERROR CREATING PROXY ACCOUNT....

FORUM

Proxy account not working when logged off

Proxy account not working when logged off

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones