Updates
Change log
| Date | Changes |
|---|---|
| 27 August, 2017 | Alan brought to my attention that the sensitive value might get exposed in the log. And yes, he was right. I found out that the Raise Change Event (value True) caused this, so I put it to False. Then everything still worked and the sensitive value was not exposed via the log. So the changed advice is to leave Raise Change Event on False for the variable containing the plain value of the sensitive parameter. |
Introduction
This time I want to tell something about the use of sensitive parameters. Passwords, connection strings, anything that is .. uhm .. sensitive.
Problem
SSIS Package Parameters and Environment variables can hold sensitive values, like passwords, connection strings and so on. You do not want these to be seen by people who are not authorized to have access to this information.
Solution
First I want to give a *short* intro on how (not) to save sensitive parameters in SSIS.
There are several options which are explained enough on other internet sites:
- DontSaveSensitive
- EncryptSensitiveWithUserKey
- EncryptSensitiveWithPassword
- EncryptAllWithPassword
- EncryptAllWithUserKey
My opinion on those options:
- When you use a *WithUserKey value, you might get into trouble when someone else from the development team wants to work on a SSIS Package.
- When you use a *WithPassword value, you have to type (or paste) the password every time you open the package in Visual Studio. I personally really disklike that.
- But more important, why would you care about saving the sensitive data at all? Thinking about professional SSIS package development, you would not want sensitive data like passwords and connection strings hard coded in the package anyway, while you can simply configure your packages from environment variables as explained in two of my earlier blog posts. So, as you might guess, DontSaveSensitive is my favorite option.
So the rest of this article is based on using the DontSaveSensitive property.
You have to set it in the SSIS package, but also in the project properties:
Setting the package property “ProtectionLevel”
Setting the Project Protection Level
I have set up a very simple demo. It consists of the following steps:
Set up a folder and environment in SSISDB if not done already
See the script for this in my previous blog post.
Create a sensitive environment variable “MyDBConnectionString”.
In the demo I use my existing DEV folder with the DEV environment inside that folder.
In my case I use Windows Integrated Security, therefore the connection string on itself is not sensitive. I’ll just use this example, because a connection string could also contain a username and password.
The environment variable “MyDBConnectionString”. Check the checkbox to make it sensitive
Create a SSIS Package with a sensitive parameter “MyDBConnectionString”
Do I need to say more?
In the SSIS Package create a (non-sensitive) variable “MyDBConnectionString”
Not so exiting either, except that you should set Raise Change Event to True for the variable, so that any other properties set by an expression using the variable will certainly change when the variable changes.
Before you can do that, you must make sure that the column is visible. The printscreens below explain how.
Clicking on this icon shows the Variable Grid Options
On the Variable Grid Options check “Raise event when variable value changes”
For the variable set “Raise Change Event” to True
| Don’t set “Raise Change Event” to True! |
|---|
| See change log at the top of the article for details. |
Here you can see that the Expression @[User::MyDBConnectionString] is used to set the ConnectionString property of the OLEDB Connection Manager OLE_SRC_MyDatabase.
Copy the parameter value to the variable value using a Script Task
The “problem” is that you cannot use the sensitive parameter in an expression that uses the value for a non-sensitive property, in my case the ConnectionString property of an OLEDB Connection Manager.
This error is shown:
This is why you have to copy the value of the sensitive parameter to a non-sensitive variable first using a Script Task.
I personally think it is a pity that Microsoft did not create a GETSENSITIVEVALUE(« SensitiveParameterOrVariable ») expression, so you could use an Expression Task, but they didn’t, so a script task is the way to go.
Add a Script Task to the control flow of your SSIS Package and adjust public void Main() as below:
{
try
{
// Copy sensitive value to normal (non-sensitive) variable, so that it can be used in an expression.
string value = Dts.Variables["$Package::MyDBConnectionString"].GetSensitiveValue().ToString();
if (!string.IsNullOrEmpty(value))
{
// Only copy, if not empty. Else preserve design time value, for instance for debugging.
Dts.Variables["User::MyDBConnectionString"].Value = value;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
throw (e);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
The control flow of the demo package now looks as follows:
The final control flow of the demo package
To demonstrate how it works, the “design time” connection string, that is stored in the variable MyDBConnectionString, is different from the sensitive environment variable MyDBConnectionString.
I have added an Execute SQL Task SQL_FooBar that uses the OLEDB Connection Manager OLE_SRC_MyDatabase.
The connection string in the variable connects to a database named FF (which is Dutch SMS talk for “Just a moment”/temporary).
The connection string in the sensitive environment variable connects to tempdb.
To proof that the connection string from the sensitive environment variable (which passes it to the package parameter) is used during package execution, I take the FF database offline, after the OLEDB Connection Manager is configured.
Taking database FF offline ..
So when the connection manager would not be configured from the sensitive parameter during run time, the Execute SQL Task would certainly fail during execution.
Now it’s time to deploy my project with the testpackage to my DEV folder in SSISDB. I do not have screenshots of that, because I assume that you will know how to do this. If not, check my previous blog posts.
This time I do the configuration manually: in the project I add a reference to the DEV environment (not in the picture), then I connect the environment variable to the package parameter.
After it is configured, I execute the package.
Shortly after it has succeeded, proof for me that it was using the configured connectionstring with database tempdb, and not the design time value with database FF.
Considerations for debugging
If you want to debug the SSIS package in Visual Studio you have a problem: due to DontSaveSensitive the package parameter will have no value, and you cannot debug the package.
My workaround for this is to put the debug value for the sensitive value in the SSIS variable. In the Script Task the variable will only get the value from the parameter when it is not empty. So during debug time, the variable value will be preserved.
If your value is too sensitive for this, either use a less sensitive value referring to a test database/server etc., or remove the value from the variable after debugging the package. In the latter case, also delete copies of the package in the bin folder.
Will my sensitive value be safe in an environment variable?
Short answer: yes, I think pretty safe.
Why? If a DBA unticks the “Sensitive” checkbox, the value is wiped out and cannot be read.
Of course a DBA or hacker with sysadmin access to the SQL Instance and knowledge could deploy a SSIS Package, just to pull out the sensitive value. But there are little things a DBA cannot do..
Conclusion / Wrap up
In this article you could read some practical insights on using the ProtectionLevel DontSaveSensitive for a SSIS package containing a sensitive parameter configured from a sensitive environment variable during execution.
Steps to take are:
- Create a folder in SSISDB. In that folder, create an environment. In my example both the folder and environment were called DEV.
- Create a sensitive environment variable.
- Create a SSIS package with a sensitive parameter and a (non-sensitive) variable with the same name.
- In the SSIS package create a script task to set the value of the variable to the value of the parameter.
- In the SSIS package use an expression to use the variable value where needed, for example in a OLEDB Connection Manager or a FTP Task.
(c) 2016/2017 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.
Hello Hans,
Excellent post, very detailed and exactly what I need.
I am, however, facing an issue when trying to use the variables in the connection manager.
I am passing user (non sensitive) and password (sensitive) as environment variables. The user is used correctly as I’m getting “Login failed for user X”, being X my parameter value. However, the cause for the login failed seems to be that the password is not being correctly handled. In the Connection manager expressions I’m passing:
User: @[$Package::UserName]
Password: @[User::Password]
I’m not sure if the problem is the script part (SetVariableFromSensitiveParameter) decoding the password, but I can see in the package execution report log (OnVariableValueChanged):
“Value of variable [Password] is changed to .”
Does this mean the password is coming as empty? I have it correctly set in the environment parameter which is being passed.
I can also see the OnWarning message “Found SQL Server Integration Services 2012 ScriptTask “guid” that requires migration” related the OnVariableValueChanged, so I’m not sure if this procedure is still valid in SQL Server 2012 (SSIS 2012).
Are you able to shed any light on my problem?
Thanks!
Hello Artur, what SQL Server version are you using, is it SQL 2014 or 2016? To be honest I do not have a clue what causes your problem. Maybe you can rebuild an empty package from scratch in the right SSIS version, just to prove that the concept works until the variable value is set. From there you can either copy your old package into the new one or try to find differences between the two packages.
Also upgrading the package to the applicable version might help.
https://www.hansmichiels.com/2016/10/23/how-to-upgrade-your-ssis-packages-from-sql-server-2012-to-2016-ssis-series/
Hope this helps, best regards, Hans
Thanks for your reply! I’m using SQL Server 2014.
I just managed to resolve the problem – I’m not sure what it was but since a fresh packagr was working fine, I tried deleting and recreating the connections in my original package and redoing them seems to have fixed the issue.
However I have now another question. This only seems to work if I have a hardcoded password in the Password variable. I mean, it still uses the user and password I provide via the environment parameters, but if I leave the variable value blank, it fails on the validation step saying it can’t acquire the connection.
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
Any idea what I missed or may be doing wrong here? I don’t want to leave the password in clear text in the variable, but seems like it needs to be there for it to work.
Thanks,
Artur
Hello Artur, good that you managed to solve the initial problem. As far as the second error, maybe you should try setting the DelayValidation property to True. If that does not help, please let me know. Best regards, Hans
Hi Hans. That was exactly it, the DelayValidation property set to True sorted the second issue.
Thank you very much and keep up the good community work!
Artur
What if someone checks the SSISDB execution report and views message context? Won’t the value of this connection string variable appear?
Hello Alan, thanks for your feedback. You are completely right, you should leave “Raise Change Event” on False, I changed the article.
Hi Hans,
Thank you for this post. I am looking for a way to have someone else enter a password in a manner that I am able to use that password in my package and in the SQL Server Agent. But I should not be able to read that password. I don’t think it is a strange requirement, yet I cannot find a way to do this.
I’ve asked the question here as well: https://social.msdn.microsoft.com/Forums/en-US/8ebc0441-4521-4ff8-a497-b1646fe6a51a/have-someone-else-enter-a-password-for-a-connection-manager?forum=sqlintegrationservices
Can you point me in the right direction?
Thanks in advance!
Hi Rudy,
If this separation of roles is in place, I also assume you do not have admin rights on the SQL Server Instance where the password needs to be entered.
The most simple way to meet your requirement would be to instruct the system administrator how to set the sensitive environment variable like you would do yourself. This can be done with SQL Server Management Studio, also on a remote SQL Instance.
If you are not an administrator on that SQL Server you will not be able to get grip on the password. If you were, you could prepare a special SSIS package just to get the password out, as I explained in the blog post.
Hope this helps.
HI Hans, Thank you for the post. I have a similar situation where in we store the connection string to connect to sharepoint site in an environment variable which is visible through catalog in SSSM. i want to encrypt it but if I chose to do from the SSIS will the admins be able to update the connection string at later point or we will need a redeployment of ISPAC once the password is updated in Project params with new password?
Hi Jay, admins can update the environment variable, no redeployment of ISPAC file needed. Of course the admins will see the connection string when they update it.
Thanks for the article. Exactly what I’m looking for as we have to use SQl logins for lower environments and windows authentication for prod (don’t get me started on why). We’re on SQL 2014 and the Raise Change Event checkbox is not an option for variables. Is that something added for SQl 2016?
Hello Ed, don’t worry, it is better to leave the “Raise Change Event” checkbox unchecked, see the change log on top of the article.
Hi hans,
i have been facing this issue since long in my project. It so happened that even when for the first time we configured all the environment variables for our passwords of connection managers in project configurations of testing environment
The issue is ,everytime we deploy in testing environment where the passwords for connection managers are different,those environmental variables pre configured are overwritten by the hardcoded passwords we use in development project params.
Could you please throw some light here.
Note: we have used parameterize for all the connection managers,with password property set to “use existing password” pointing to project param password variable and value. and we have harcoded passwords defined in project params values of the password variables.
Hello Chandini, I understand your problem but do not have it. Every time I redeploy the environment variables keep their values. In this blog post is explained how I do this: How to automate your SSIS package deployment and configuration (SSIS Series).
For connection strings I usually use expressions for the connection string of a connection manager, and put the connection string as a whole in an environment variable, not the different components, like servername, username, password and so on seperately. Hope this helps.
Best regards,
Hans
For connection strings If we use expressions for the connection string of a connection manager, then how can we provide the password there?
Hi Dinku, I think the blog post describes this scenario pretty well. Just beware that you can only use the connection manager after the SSIS variable that hold the (plain) connection string is set from the sensitive parameter. This means for instance that you can’t use this approach for a connection manager that is used for SSIS logging.
Hi Chandini,
It sounds like you were adding the value directly into the project configuration, and not creating a separate Environment to store the values. If that’s the case, then a redeploy will certainly overwrite the configured values. See this for creating an Environment:
http://www.sqlservercentral.com/articles/Stairway+Series/121490/
Hopefully you’ve already made it past your roadblock, and this advice is not needed.
Hi Hans,
Thanks for the post. I have a project with 30+ packages in it. Rather than have each package have it’s own connection managers, as they are all talking to the same data source and destination, just different tables effectively; I have project connection managers for reusability.
So, with project connection managers, we can use project parameters to parameterize, but when I attempt to map a sensitive environment variable to the sensitive project parameter, it is breaking. Do I need to convert to package connection managers for each package, or is there something I’ve missed?
Hi John,
Thanks for your question. To be honest I am not sure how to do this with project connection managers, I hate those things due to problems in the past. I just use connection managers in each package and then configure the connection string from the outside (using a parameter or sensitive parameter). If you have to add a connection manager in bulk for a lot of packages, you could consider to add the connection manager(s) to one package as an example, and then use a different editor like Notepad++ to copy it quickly to all other packages. Then replace the DTSID of the project connection manager by the DTSID of the just copied in connection manager (again: not in Visual Studio but a text editor) and you should be done with not too much effort. Then you can follow the instructions from the blog post.
I am running Visual Studio 2019 with the latest SSIS extension installed. I have converted one of my projects with 3 packages from Package Deployment Model to Project Deployment model primarily to take advantage of encrypting sensitive values and using SSISDB for deployment. I have tested the packages within the Visual Studio IDE development environment and verified they run just fine with the Project Deployment model WITHOUT using sensitive (encrypted) parameters. However, as soon as I change one of the project or package level parameters to be sensitive (non DB connection related parameters…those appear to work fine when encrypted) the script task always reads an empty string from the GetSensitiveValue() method call. As soon as I mark sensitive = false for a parameter GetSensitiveValue() returns the correct string. I’ve tried setting my project and package protection levels to both “DontSaveSensitive” and “EncryptSensitiveWithUserKey” and both yield empty strings when GetSensitiveValues() is called. My package is connecting with SFTP sites using SSH for authentication and using PGP to encrypt/decrypt files, so I have to securely store passphrases for the private keys involved in these transactions. Does the GetSensitiveValue() work when testing in the Visual Studio IDE? I have had a very difficult time finding any reliable info that can get around this problem. It would be most regrettable if I’m forced to build a separate .NET program to do the secure FTP portion of the package logic instead of the current script task I’m using. At least with .NET I know how to encrypt and decrypt sections of an app.config file. I would appreciate any guidance that could be provided. Thanks!
Okay, so I was actually able to get it working with encryption set for the project and for all packages to either EncryptSensitiveWithUserKey or with EncryptSensitiveWithPassword. I ended up sticking with EncryptSensitiveWithPassword so that the project and packages aren’t tied to my user profile. I think this may be something you have to do if you want to test out your package in the Visual Studio Development environment. One thing that helped was to make sure that I set all parameters (except for the connection string password parameters) to sensitive = false before setting the encryption level for the project and its packages. After setting the encryption level for the project and packages I then went back and set specific parameters to sensitive = true. Make sure that sensitive parameters are not used in any expressions (as Hans indicates in this article).
Hi Steven,
Good to hear that you got it working and thanks for sharing your knowledge.
Kind regards,
Hans
Hi Steven,
What I remember from it is that GetSensitiveValue() did not work when running in debug mode. Hope this helps. Can you call the SSIS Package from a SQL Agent job to test it?
Kind regards,
Hans