One of the questions I get when teaching others how to use Biml is how do you deal with sensitive information like usernames and passwords in your Biml Solution. No one wants to leave this information in plain text in a solution. You need access to it while interrogating your sources and destination connections for metadata. You also need it while Biml creates your SSIS packages since SSIS uses SELECT to read the metadata during design time to gather its metadata. If you lock away that sensitive information too tightly, you won’t be effective while building your solutions.
In the end, you’ll have to compromise between security and efficacy.
One of my go-to solutions is using Package or Project Parameters. While this isn’t completely secure, it does allow you to control access across multiple environments and multiple deployments. I’d like to share an example from my Biml repository on GitHub. You can find this code in the ParametersAndConnections folder. You’ll find a BimlStudio and a BimlExpress solution there. For this blog, I’ll cover this code from the BimlExpress solution.
Open \Biml\ParametersAndConnections\BimlExpress\ParametersAndConnections_BE\ParametersAndConnections_BE.sln and you’ll see an empty solution with a single BimlScript: BimlScript.biml. Open that script and you’ll see connections defined at the top.
Notice I’ve hard coded the connection strings for all four connections: ADONET_AdventureWorks_Package, ADONET_AdventureWorks_Project, OLEDB_AdventureWorks_Package, and OLEDB_AdventureWorks_Project. While most are using integrated security, ADONET_AdventureWorks_Package is not. It’s using username and password, and you can see both in plain text.
Even if I had separated my connections into a separate biml file, and made that biml file a parameterized BimlScript that took username and password. Then stored the username and password into an encrypted table, developers would still be able to get the password into plain text. Simply call the parameterized BimlScript, and write the output to a file using the GetBiml() function.
If we didn’t allow the BimlScript to get the password plain text, we wouldn’t be able to use the connection node(s) for later parts of our Biml Solution. Thus the compromise has to come somewhere.
Next in BimlScript.biml, we build a single package. You can see in lines 20 – 29 and 34 – 35, that I’ve tested a few different scenarios. The Connections node in the Packages\Package node is where we start using Project Parameters.
Feel free to try different combinations of Parameters here and generate your own resulting packages. As long as you are referencing Project Parameters named: ADONET_ConnectionString, OLEDB_ConnectionString, DatabaseName, UserName, or Password, you’ll be ok. If you want to create more Project Parameters, you have to create those manually when using BimlExpress. Adding them in the Projects\PackageProject\Parameters node will not add them to the Project.params file in this solution. Feel free to test it and see for yourself.
There are no current plans to add this to BimlExpress at this time. This code will work from BimlStudio, adding new parameters to a generated SSIS project.
As you test using different Parameters to affect the different connections, you should find two things that make you stop and wonder. First, you cannot affect Project Connections with Project Parameters through BimlScript. I believe this is functioning as intended since you would configure Project Connections at runtime by passing the updated values through [catalog].set_execution_parameter_value.
Second, when you update a password through Parameters and Expressions, the password does not show up in the Connection Manager dialog box, yet at run time the package succeeds!
This is due to oddness in SSIS. If your Package and Project have the same protection level (EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword) The Connection Manager doesn’t show the password, even when you save it. But when you Test Connection or run the package, the password is there. That same behavior is there when using Biml to create your Packages.
In the end, you’ll have to pick the amount of security you implement in your Biml Solution, just as you did in your SSIS solution. Just be sure you and the business are OK with the risks. While Biml is newer than most all of your ETL techniques, it doesn’t prevent you from using those old techniques, it simply allows you to use them as a framework instead of manually. Hopefully, this example shows how simple it is to use those older techniques for masking sensitive information.