Secure Connections Management in SSIS, Part 1

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

Let’s begin by examining ways to connect to a database – any database – using SSIS. SSIS connectivity is provider-driven. To connect to a database (or almost* any resource), SSIS developers use Connection Managers.

* Some exceptions: SSIS developers can use the Script Task and Script Component to connect to resources via the .Net Framework. Custom tasks and components can “reach” from within SSIS to outside resources. The Raw File Source and Raw File Destination access the file system from within the Data Flow Task without using a Connection Manager.

Configure an SSIS Connection Manager

There’s no better way to learn than by doing, so please follow along if you have SSIS installed and available. Create an SSIS solution. Note that I am using SSIS 2014 for my screenshots, but I will endeavor to point out differences in versions of SSIS (I have experience with all released versions of SSIS). Once the solution is created, right-click in the Connection Managers tab and click “New OLE DB Connection…” as shown in Figure 1:

Figure 1

Clicking “New OLE DB Connection…” opens the Configure OLE DB Connection Manager window, shown in Figure 2:

Figure 2

If you have not configured OLE DB connections, your Data Connections list will be empty. I have configured a handful of OLE DB connections on my demo virtual machine. They are listed in the Data Connections listbox in Figure 2.

Click the “New…” button to configure a new OLE DB connection. Clicking the “New…” button opens the Connection Manager editor, shown in Figure 3:

Figure 3

If you click the Provider dropdown, you can see there are several OLE DB providers available to SSIS, as shown in Figure 4:

Figure 4

If your workstation has other OLE DB providers installed, such as providers for Oracle or DB2, those providers will also appear in this dropdown list. For the purposes of this exercise, let’s stick with the SQL Server Native Client.

Enter the name of a SQL Server instance in the “Server name” combobox, or click the dropdown and select a name from the available SQL Server instances, as shown in Figure 5:

Figure 5

The “Log on to the server” groupbox is located beneath the “Server name” combobox. This is a common first encounter with data security in SSIS. There are two options:

Use Windows Authentication
Use SQL Server Authentication

There are several reasons why Windows Authentication is considered better and more secure than SQL Server Authentication. Windows Authentication uses Security ID’s (SIDs) instead of username / password combinations. Active Directory manages domain access when a user logs on. SQL Server permissions are granted based upon SIDs. There are several caveats to using Windows Authentication making Windows Authentication a good, but not perfect, solution for all use cases.

Note: I am not a security expert. I know some security experts and their advice to me has always been, “Use Windows Authentication whenever and wherever possible.” I share their advice with you.

In the combobox labeled “Select or enter a database name,” do exactly that (select or enter a database name) as shown in Figure 6:

Figure 6

Click the OK button to close the Connection Manager editor and return to the Configure OLE DB Connection Manager window, as shown in Figure 7:

Figure 7

Click the OK button to close the Configure OLE DB Connection Manager window and return to your SSIS package. Note a shiny, new Connection Manager, as shown in Figure 8:

Figure 8