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:
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:
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.
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:
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:
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:
Note what happens when you re-open the OLE DB Connection Manager Editor, though: the Password textbox is empty as shown in 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:
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:
In addition, an error will display in the Error List window (View—> Error List) as shown in 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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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).
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.