THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services.

Secure Connections Management in SSIS, Part 2

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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_5
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:

SSISCM_2_4
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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_8
Figure 8

In addition, an error will display in the Error List window (View—> Error List) as shown in Figure 9:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

 

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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:

SSISCM_2_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
Test your knowledge -->
Linchpin People Blog: SSIS
Stairway to Integration Services

SSIS2014DesignPatterns200

Published Monday, March 02, 2015 6:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Richard Polichetti said:

The BEST refresher on this subject on the internet!

January 9, 2017 4:38 PM
 

andyleonard said:

Thank you for you kind words, Richard.

:{>

January 9, 2017 4:52 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement