Run an SSIS package from sql server agent job using a non-windows account

Question

Is it possible to run an SSIS package from a sql server agent job using a proxy account that is not a windows account (sql server account).

I created a SSIS package that runs with a particular sql server account to copy data from one server to another. This can be done everytime by typing the password of the sql server account before running the package. When trying to schedule the package to run under a proxy account, it only accepts Windows(AD) credentials and a proxy account cannot be created with a sql server account! What other workarounds do I have?

Answer 1

Proxy account must be Windows account... but proxy isn't the only way to accomplish what you're trying to do.

Look under the "Resolution" tab in the following article for other approaches: http://support.microsoft.com/kb/918760

i.e. storing credentials as part of your SSIS package/template or moving the package into the database.

Answer 2

like lazyDBA says - the proxy account must be a real windows account.

But to elaborate on this, this proxy account is nothing more than the account which runs the SSIS process to execute your package.

This does not include data sources inside your package for which you have specified username and password (SQL Server Logins). Any user can in theory run these packages, but they must be able to read the username and password that's encrypted in the SSIS file (given that you've selected to SaveSensitiveData with Encryption).

What you could do (maybe should do) - is to change the SaveSensitiveData to EncryptSensitiveWithPassword and then setup this password in the agent job so that it doesn't have to be inputted every time. It would then not matter which proxy / windows account executes this SSIS package. You'll still be using the SQL Server username/password as defined in your SSIS packages.

Reference: http://msdn.microsoft.com/en-us/library/ms141747.aspx