SSIS SQL Server Authentication for OLE DB
Introduction
One of my friends sends me a mail related to saving password in SQL Server authentication for OLE DB Source and destination. In this article we are trying to discuss about it.
The Case Scenario
The part of my friends e-mail is mentioned here for better understanding the case scenario.
"I create a new connection Manager and use SQL Server authorization and save the password, the connection manager seems to "forget" the password. That is, when I click the 'save password' check box, and do a test connection, it connects fine. But as soon as I close that connection window, and reopen it, the password box is empty, and the 'save password' box is still checked. Even if I do a test connection at this point, it won't connect because it does not have the password."
Protection Level Property
There is a property of SSIS package called the Protection Level Property that is used to specify how the sensitive information is saved within the package. Here the sensitive information that I mean is connection string etc.
There are six type of property value found in SSIS package like.
- DontSaveSensitive
- EncryptSensitiveWithUserKey
- EncryptSensitiveWithPassword
- EncryptAllWithPassword
- EncryptAllWithUserKey
- ServerStorage
Now we are going to understand each one
Don't Save Sensitive
When we are choosing this value of property Protection level it means that the any kind of sensitive information is not written in the package XML file when we save the package. When we use this value and open the OLE DB connection manager the password is just BLANK even we choose the save my password check box.
Encrypt Sensitive with user Key
It encrypts the sensitive information based on the credentials of the user who create the package. If we use this value the XML file for the password looks like this.
<DTS:PASSWORD Sensitive="1" DTS:Name="Password" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+...</DTS:PASSWORD>
Here in the above XML code we find that the attributes Encrypted = "1". When the developer who created the package is open the package the password is decrypted automatically. If any other user except the owner tries to open the package get an error like.
And that is the limitation of these settings.
Encrypt Sensitive With Password
Here we just provide a password in the package and this password is used to encrypt and decrypt the sensitive information in the package.
The Encrypt Sensitive With Password setting for the Protection Level property overcomes the limitation of the Encrypt Sensitive With User Key setting, allowing any user to open the package as long as they have the password.
When we execute a package with this setting using DTEXEC, we can specify the password on the command line using the /Decrypt password command line argument.
Encrypt All with Password
It allows encrypting the entire contents of the SSIS package with specified password. The settings is just like the Encrypt Sensitive With Password
Encrypt All With User Key
It allows encrypting the entire contents of the SSIS package with specified password. Only the developer who created the package can able to Open, view or modify and Run it.
Server Storage
It writes all the sensitive information to SQL server when we save the package. It stores information in MSDB database.
How to solve the above problem
To solve the above problem we must create the SSIS XML configuration file. The Select Property to explore page of the Package configuration wizard is displayed bellow.
Those who want to go to the details of the package configuration manager please read my previous article.
Example of XML file
For Windows authenticated connection to SQL Server:
<DTSConfiguration>
<Configuration ConfiguredType="Property"
Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>
Data Source=[SRVNAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
For SQL Server authenticated connection to SQL Server:
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>
Data Source=[SRVNAME];Initial Catalog=[DBNAME];User ID=[LOGIN];password=[PWD];Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Here the [SRVNAME] is the name of the SQL Server. [DBNAME] is the name of the Database,[LOGIN] is the name of the SQL Server login user ID and [PWD] is the Password.
Hope you like it.
