Deploying SSIS Package and retaining a connection password
I have an SSIS package that queries a view for some records then sends off an email using Database mail. I want to deploy the package to the SQL Server that these views exist in and then schedule us using SQL Server Agent but i've run into a number of issues.
The main problem is that within the job there is a connection manager that has stored credentials for a SQL Server account that has access to the views and email profile. I've set the package to EncryptSensitiveWithPassword instead of User Key but i can't seem to find any way to actually pass that password the the SQL Server Agent when the job is run. So when it executes it fails since it can't connect.
A bunch of websites say to use the config file, but when I go that route it breaks the job entirely complaining that the OLEDB connection can't be established. In that scenario I can't even run the job on my machine locally.
A few other sites recommended copying the package to the local ssis package store on the machine, but I only have access to the machine through SQL Server Tools, I can't remote in or install anything on the file system so I have to install through the manifest file using the SSIS deployment process.
I've read something about storing settings in another DB table, but that again presents issues with permissions on the server.
Finally I tried passing /password or /decrypt in the job scheduler, but when i click ok it scheduler removes that parameter.
This is an internal server and security isnt really a big concern, Any idea how i can pass the decryption password in plaintext or just tell SSIS to not bother securing sensitive data?
Answer
The trick is:
- When saving the package in SQL Server Business Intelligence Studio, ensure the Package Properties > ProtectionLevel is set to EncryptSensitiveWithUserKey.
- When you import the SSIS package into the SQL Server (from SQL Mgmt Studio > object explorer > [server] Integration Services > Stored Packages > MSDB > right-click > Import Package > set Package location to File System, Select the file in Package path, and select the option "Rely on server storage and roles for access control". I would guess when importing the .dtsx file you have to be logged in as the same user that saved the .dtsx file.
This means that the password is saved with the package and therefore can be used when anyone executes the package. If it's something you don't want anyone to execute you then lock down security appropriately.
Alternatively you could change the Connection so it uses Integrated Security (windows authentication instead of a sql login) and change the Job so it executes the package as a user that has access to the database & view that's queried by the package. This is the preferred option from the view that you don't need passwords anywhere and instead manage it via user identity and permissions.