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 an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

Secure Connections Management in SSIS, Part 2

This blog has moved! You can find this content at the following new location:

http://andyleonard.blog/2015/03/02/secure-connections-management-in-ssis-part-2/

Published Monday, March 2, 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
 

Ben Lezin said:

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

June 27, 2017 2:10 AM
 

andyleonard said:

Hi Ben,

  When you change the ProtectionLevel to EncryptSensitiveWithPassword, you need to supply a password and then redeploy the package.

Hope this helps,

Andy

June 28, 2017 7:23 AM
 

Ben Lezin said:

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

June 29, 2017 5:53 PM
 

andyleonard said:

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

June 30, 2017 2:29 PM
 

Ben Lezin said:

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

July 10, 2017 12:58 PM
 

Ben Lezin said:

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

July 10, 2017 6:25 PM
 

Ben Lezin said:

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

July 10, 2017 9:11 PM
 

Ben Lezin said:

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

July 12, 2017 7:33 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement