November 19, 2018 at 7:20 am
We are running SQL Server 2012 SP3 on Windows Servert 2008.
What is the "best practice" and safest method to protect a SSIS Project/Package database connection password from Developers?
I want to make sure the database connection password is not visible in SSIS Projects/Packages when these items are turned back over to developers for modification.
In our SSIS Projects/Packages, we currently use the ProtectionLevel setting. we have Project ProtectionLevel set to "EncryptWithPasswordSensitivty" and Package ProtectionLevel set to "EncryptWithPasswordSensitivty". Therefore, when we open a SSIS Project using Visual Studio, we have to enter the "Project Password which causes sensitive data in the project to be encrypted with a password. (Failure to enter a password will result in loss of sensitive data.)"
We also currently use a parameter to provide the password to the database connections:
Right click DB Connection Manager, click Parameterize, select Password
"Use existing parameter" is selected and $Project::parm_Database_Pswd is selected.
If the developer has to modify the SSIS Project/Package, would they be able to see the password in clear text with the above methods?
Would the developer be able to change the Sensitive setting in Project.params to False, to allow them to view the password? (I have performed some research and it seems that if Project.params to True, that the password is encrypted when viewing the code (using Visual Studio) for the Project.params.
I just want to be sure the developers are not able to view this password when they have to modify SSIS Projects/Packages. I know the SSIS Packages are nothing but an XML File and I do not want the developers opening the package as an xml file and then viewing the password. I think the best method is for me (DBA) to add/change the password at the Job Level in the Job Configuration Tab (and Connection Managers Tab) when deploying SSIS Projects/Packages from development to the test environment and then to the production environment. I appreciate any suggestions and thoughts based on our current setup as explained above.
Thanks in advance.
November 19, 2018 at 8:09 am
If you have a object with which is classified as sensitive, then that value will not be decrypted unless you have the right credential; password, userkey, etc. If you open the package/project will that encryption key then the values will be blank. Changing the value of the sensitivity property afterwards isn't going to populate that previous unpopulated value.
Note that, as well, if they do edit the package/project while it's not unencrypted, especially if they edit an sensitive value, the encrypted value will be lost (and thus your package will likely begin to fail).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 19, 2018 at 8:15 am
I think you should let the developers know the password to the development database. For production you can put the password in a Package Configuration file.
After all if the packages they are developing has access to the database, this is as good as them having the password as they can develop and test any code they like in SSIS against that database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply