Best Place to Store SSIS Sensitive Encrypted Password In a SQL Server Agent Job

Question

I prefer to keep our SSIS packages in a solution on the server, not in sql. By default, sensitive data is encrypted with a user key. Since the sql server agent uses a service account to run jobs, we have to change this encryption method to something else. I like encrypting with a password.

The problem is whenever I setup a job in a step there is no place to input this password. When I click on the configurations tab, I get a popup dialog for the password. That keeps it stored properly, but is this really the right place to put it. It seems really unusual, and I keep running into issues where it seems to reset itself if I make certain changes.

Does anyone know of a better place to input this password that is more stable?

Thanks,

Answer 1

There is no need for you to have a password in the package at all if you can use Windows authentication and avoid SQL Server authentication and ensure that the rights necessary to execute your package are available to your service account.

Barring that, you should already be overriding the connection string with a config file using package configurations so your package is portable, and then the password doesn't need to be in the dtsx anyway.

Answer 2

The way you deploy your package and store your package in your local project solution can be different. You can save sensitive data with a password when saving your packages locally and "rely on server storage and roles for access control" when deploying the package. Although the data will not be stored in an encrypted format in the msdb database, you can restrict access to the password information by managing pre-existing database roles.