SQL Server Integration Services (SSIS) is built to move data. SSIS ships with features to support the secure transmission of data. In this series, my goal is to promote awareness of these features and advocate for their use.
SSIS Connection Managers
In Secure Connections Management in SSIS, Part 1, I demonstrated configuring an SSIS Connection Manager to us Windows Authentication, as shown in Figure 1:
Figure 1
Using Windows Authentication in SSIS Connection Managers is a best practice. But what if you need to use a SQL Login to connect to the database? The OLE DB Connection Manager Editor provides an option labeled, “Use SQL Server Authentication” in the “Log on to the server” groupbox. After selecting the “Use SQL Server Authentication” option, enter a user name and password in the appropriate textboxes, as shown in Figure 2:
Figure 2
Save my Password
There is a “Save my password” checkbox beneath the Password textbox, but checking this checkbox will not automatically save your password. Conversely, not checking the checkbox will not remove the password configuration from the OLE DB Connection Manager.
I can hear you thinking, “What determines whether the password will be saved as part of the Connection Manager configuration?” That is an excellent question. Let’s examine some properties and use cases that determine whether the password will be stored as part of the Connection Manager configuration.
Do Nothing
If you created an SSIS package with design-time defaults and you do not check the “Save my password” checkbox and click the OK button on the OLE DB Connection Manager Editor, the password will be stored as part of the Connection Manager configuration.
How can you check? After closing the OLE DB Connection Manager Editor, right-click the OLE DB Connection Manager in the Connection Managers tab at the bottom of the SSIS package Control Flow and click “Test Connectivity” as shown in Figure 3:
Figure 3
Connectivity is automatically tested when click the OK button on the OLE DB Connection Manager Editor. But you can manually test connectivity at any time via the Connection Manager’s context (right-click) menu. If SSIS is unable to acquire a connection with the current configuration, the OLE DB Connection manager will appear as shown in Figure 4:
Figure 4
In this case, SSIS is able to acquire a connection with the current configuration, so the OLE DB Connection manager will appear as shown in Figure 5:
Figure 5
Note what happens when you re-open the OLE DB Connection Manager Editor, though: the Password textbox is empty as shown in Figure 6:
Figure 6
If you click the “Test Connection” button the test will fail and you will a dialog similar to that displayed in Figure 7:
Figure 7
If you dismiss the dialog and click the OK button to close the OLE DB Connection Manager Editor, the OLE DB Connection Manager will indicate the connection is not connected as shown in Figure 8:
Figure 8
In addition, an error will display in the Error List window (View—> Error List) as shown in Figure 9:
Figure 9
The error will be similar to:
An error has occurred while connecting vmSQL14.TestDB: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user ‘testUser’.".
Why did this happen? Was the OLE DB Connection Manager ever really connected?
Let’s answer that second question first. Yep, the OLE DB Connection Manager was really connected.
Why Does The OLE DB Connection Manager Behave Like This?
To answer why the OLE DB Connection Manager behaved in the way it did, we have to examine some other properties in the SSIS package and project. Those properties are:
-
SSIS package ProtectionLevel
-
SSIS project Deployment Model
-
SSIS project ProtectionLevel
The SSIS Package ProtectionLevel Property
The first property to examine is the SSIS package ProtectionLevel property shown in Figure 10:
Figure 10
The default setting for the SSIS package ProtectionLevel property is EncryptSensitiveWithUserKey (shown in Figure 10). You can learn more about the SSIS package ProtectionLevel property at MSDN. The EncryptSensitiveWithUserKey package ProtectionLevel setting configures the SSIS package to encrypt the OLE DB Connection Manager password using “a key that is based on the current user profile.”
The SSIS Project Deployment Model
In SSIS 2012 and later, the default deployment model for SSIS packages and projects is “Project Deployment Model.” If you created an SSIS solution in SQL Server Data Tools – Business Intelligence (SSDT-BI) and haven’t made any changes to the default Deployment Model, you are developing in Project Deployment Model. There is currently only one other Deployment Model available in SSIS: Package Deployment Model. Package Deployment Model is included in SSIS 2012 and SSIS 2014 to provide backwards compatibility with SSIS 2005, SSIS 2008, and SSIS 2008 R2. In pre-2012 versions of SSIS, all SSIS packages used the same deployment model, and that model was the Package Deployment Model.
How can you tell if your SSIS project is configured to use the Project Deployment Model or the Package Deployment Model?
If the SSIS project is configured to use the Package Deployment Model, Solution Explorer will indicate this with the text, “(package deployment model)” beside the name of the project, as shown in Figure 11:
Figure 11
If the SSIS project is configured to use the Project Deployment Model, there will be no text following the project name in Solution Explorer, as shown in Figure 12:
Figure 12
The SSIS Project ProtectionLevel Property
In Project Deployment Model, you must make sure the SSIS project ProtectionLevel property setting matches the ProtectionLevel setting for each SSIS package contained in the project. To set the SSIS project ProtectionLevel property, right-click the project in Solution Explorer and click “Properties” as shown in Figure 13:
Figure 13
Clicking Properties opens the SSIS project’s Property Pages. the SSIS Project ProtectionLevel property is located on the Common Properties\Project page, as shown in Figure 14:
Figure 14
Again, the SSIS Project ProtectionLevel property must match the SSIS Package ProtectionLevel property setting for every SSIS package included in the SSIS project.
By default, the SSIS Project ProtectionLevel property is set to EncryptSensitiveWithUserKey and the SSIS package ProtectionLevel property is set to EncryptSensitiveWithUserKey. So – by default – these settings match.
The Behavior, Explained
When we enter a password into the OLE DB Connection Manager Editor and click the OK button, the value of the password is encrypted “a key that is based on the current user profile” (a quote from the page regarding SSIS package ProtectionLevel property at MSDN). Where is it encrypted? In the SSIS package XML. You can view the SSIS package XML by right-clicking the package name in Solution Explorer and clicking “View Code” as shown in Figure 15:
Figure 15
The encrypted password is stored in the definition of the OLE DB Connection Manager. You can see it in the code displayed when you click “View Code” as shown in Figure 16:
Figure 16
Closing the Code window and return to the SSIS designer window, we can reopen the OLE DB Connection Manager Editor as shown in Figure 17:
Figure 17
Note the Password textbox is empty. If one clicks the OK button now and closes the OLE DB Connection Manager Editor, the Connection Manager is validated (via SSIS design-time validation) and – since the Password textbox was empty when the developer clicked the OK button, validation fails as shown in Figure 18:
Figure 18
Returning to the Code view, we see that – compared to the previous XML – the <DTS:Password> tag is missing from the later version (on the right) as shown in Figure 19:
Figure 19
The other difference between the two versions of the XML is the <DTS:ConnectionManager> tag is closed with a “/ >” construct in the later version (on the right) instead of a closing tag “</DTS:ConnectionManager>” in the earlier version (on the left).
Conclusion
Is this complex? Yep. As a data integration developer with SSIS, do you really need to know all of this? No, not all of it; but you do need to understand the moving parts of SSIS that are related to security – as well as how they interact – and these are some of the moving parts.
Learn more:
Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015
Watch the Video
Linchpin People Blog: SSIS
Stairway to Integration Services
The BEST refresher on this subject on the internet!
Thank you for you kind words, Richard.
:{>
Hello, Thank you for this post since it’s exactly my issue as I’m using SQL Server authentication for a 2016 SSIS package. I’m able to execute the package within VS, but it will throw the login error, presumably because of the blank password, when I deploy to MSDB or a file share on a different server. I have tried in vain to change the security from EncryptSensiviteWithUserKey to encryptsensitivewithpassword, to no avail.
Am I required to deploy to a SSMS project catalog to get this to work?
Many thanks in advance!
Ben
Hi Ben,
When you change the ProtectionLevel to EncryptSensitiveWithPassword, you need to supply a password and then redeploy the package.
Hope this helps,
Andy
Hi Andy,
Thanks! It did work once I saved the password and then deployed.
However, what I’m really trying to do is deploy the package with a config file that stores a SQL Server data source connection that uses a SQL login. But as soon as I create the config file with the Package Configuration Manager, and execute the package, I get a login error. I have to remove the config file and then re-enter the password in the connection manager to restore the package (as you explain in your post).
Do I need to use a specific ProtectionLevel property, i.e. ‘Don’t save sensitive’, to use a config file that hosts the data source connection? And do you know what specific config properties I need for the data source connection, or do I include everything for the connection manager? Unfortunately, I can’t find any documentation regarding, what would appear to be, a common use case for deploying a package with a sql login.
Your assistance is much appreciated. And if you cover it one of your books, please let me know.
Thank you,
Ben
Hi Ben,
I usually store the Connection String property of the Connection Manager – *as shown in the Properties of the Connection Manager* – in an external store (a Config file for non-Catalog deployments, a Catalog Environment for Catalog deployments). For SQL Server Logins, I externalize the Password property separately.
If deploying to the MSDB database, I choose ServerStorage for the package ProtectionLevel.
For Project Deployment Model (deployment to the Catalog), there is no ServerStorage option for ProtectionLevel. If deploying to the SSIS Catalog, ProtectionLevel is really a design-time setting only. During deployment the ProtectionLevel is updated to something very much like ServerStorage.
Hope this helps,
Andy
Hi Andy,
Thank you for the helpful info! The tricky part for me is figuring out how to “externalize the Password property separately” for SQL Server logins, since I thought I was doing that already. But will give it another shot.
Thanks!
Ben
Well, I took what I thought was the easy path and deployed to the SSIS catalog, which allowed me to hard code the SQL login in the connection manager. However, now I’m getting a connection error with Excel, even though the package executes on its own with a package password! So it’s not the notorious 32 bit Visual Studio 2010 Tools for Runtime error.
gorkana:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: “Microsoft Access Database Engine” Hresult: 0x80040E4D Description: “Cannot start your application. The workgroup information file is missing or opened exclusively by another user.”.
Do I need special file share permissions for the Excel connection manager?
Thanks,
Ben
Aargh, now I’m getting “is not a valid path” error for the Excel connection manager, even though it’s a mapped drive that is consistent with each environment, and didn’t throw an error previously.
I think the previous Excel connection error was related to changing the username and password in the configuration manager in the SSIS catalog.
I tried to use my same credentials in case there was an encryption issue since it’s a different server, even though I’m an admin on both machines.
Is there any documentation or literature about the connection properties configuration for an Excel connection deployed to an SSIS catalog? Since I’m reluctant to keep bugging you on this blog.
Thanks again,
Ben
Hi Andy,
I didn’t intend to spam you, but I did resolve the Excel Connection Manager issue once I deployed the package to the SSIS Catalogs.
However, I’m curious why the file path in the connection string in the SSIS Catalog configuration behaves differently than executing the package externally.
For example, I have a mapped drive (F:) on my Dev and Production instances with the same path to the Excel source file for my package for a consistent connection string:
F:\Prod\Scripts\GorkanaETL\gorkana.xlsx
This works fine when I execute the package on both servers. However, when I deployed the package to the SSIS Catalog I was required to modify the Excel Connection Manager with a connection string using a path without a mapped drive as follows:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Ben\gorkana.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES”;
Any idea why the mapped drive connection string wouldn’t work when it’s included in the SSIS Catalog Connection Manager, but works when the package is executed outside of the SSIS Catalog?
Thanks,
Ben