Problem Statement: How to manage the password used in SQL Authentication into SSIS Packages?
Solution: We
can manage the password using more than one ways as mentioned by following
stackoverflow responses:
https://stackoverflow.com/questions/1274080/ssis-connection-manager-not-storing-sql-password
After doing good amount of investigation, I
have found that in SQL Server Integration Services Catalogs we can create
environment variables by enabling sensitivity to store the password, which is
used for login in SQL Authentication. The step-by-step implementation is as
follows:
I.
SSIS
Package Development:
1.
In
Microsoft Data Tool 2012+, create an SSIS Project. In this Project add
connection manager to a SQL database using OLEDB connection manager:
Screenshot # 1
As we can see in the above screenshot, I have provided the
credentials for SQL Authentication. However, we know that these credentials cannot
be stored in SSIS Packages, because the password is sensitive data. Even if we
check the check box “Save my password”, but when we reopen the connection
manager we do not find the password stored there. To make ourselves clear we can open
the source code of the package and there we do not see the password string in the connection manager:
Screenshot # 2
Note: I have currently selected package level property ProtectionLevel=DontSaveSensitive
property, so that the password value should not be saved inside the package.
2.
Parameterize
the connection manager related the following parameters:
a.
Server
Name
b.
Database
Name
c.
User Name
d.
Password
To store the above values required to parameterize connection,
we can create either project parameters or package parameters. The thumb rule
is that if a parameter value is going to be used by more than one packages,
then that parameter should be created as a project parameter. For this demo
purpose, I am creating package parameters:
Screenshot # 3
3.
In above
screenshot # 2, we can see the entered password string. Now select the
Password parameter and press F4 to open its properties window. In the
properties window select the Sensitive option to True (by default it's false):
Screenshot # 4
Once we set the Sensitive option to True, the password
string will be hidden now:
Screenshot # 5
II.
SSIS
Package Deployment:
1.
Build the
SSIS Project, which would create ispac file in \bin\Development location.
2.
In SSMS,
go to Integration Services Catalogs and expand it. If you can see SSISDB then
it means Catalog is created, but if the SSISDB is not created, then right click
to Integration Services Catalogs and click the menu item “Create Catalog…” This
will open Create Catalog dialog box, which can be used to create the database.
3.
Once the
SSISDB database is created under Integration Services Catalogs, right click on SSISDB,
click on “Create Folder…”, and create Demo Folder. Expand the Demo folder and
Projects and Environments subfolders can be seen. These subfolders are empty as
of now and will be used for the deployment of SSIS Packages and creation of
Environment variables respectively.
4.
Deploy the
package using the ispac file we created as mentioned in above step # 1. Once
the deployment is complete, you may expand the Projects folder under Demo and
see the packages which are deployed there (as shown in below screen shot # 4):
Screenshot # 6
5.
Right
click on Environments folder and click “Create Environments…” menu item. Create
Environment PackageDemo and then create following Environment variables:
Screenshot # 7
Note: Make sure to check the Sensitive checkbox. As we
have already selected the Password parameter Sensitive property to True and
both should match.
6.
Map the
environment variables to package parameters: Right click on PackageDemo.dtsx and select Configure… menu item. In the Configure
dialog box, for each package parameter, there would be a “…” button, which will
open the Set Parameter Value dialog box. If in this dialog box Use Environment
Variable option is disabled, then it means the Reference of the SSIS package is
not set. Close the Set Parameter Dialog box, select References in Configure
dialog box, and set the references.
Screenshot # 8
7.
After
setting the reference, option “Use Environment Variable” would be enabled as
shown in below screenshot # 7:
Screenshot # 9
8.
After
mapping of environment variables, right click on deployed package and select
Execute… option. In Execute Package dialog box, check the Environment check box
and click OK button. You would get the following message:
Screenshot # 10
Click on Yes button and the execution report would open. If
everything is green like below screenshot, then it means that package have
been executed successfully:
Screenshot # 11
Note: Please make sure to set Sensitive
property value as True in the package for Password parameter as well as to check
the Sensitive checkbox for the Password environment variable.
No comments:
Post a Comment