Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Storing passwords

Many questions on the SSIS forum relate to security aspects of using SSIS and a large percentage of those relate to the storing of passwords in configurations.

Many people have correctly observed that, whilst SSIS will generate configuration files for you, it will not include passwords, instead leaving them blank. The reason is simple, storing passwords in an unencrypted text file is a security risk and Microsoft want to make sure it is YOU that is responsible for exposing this security risk, not them.

Still though, if you enter passwords into a configuration file then you are creating a security risk. What do you do about this? Well, until Microsoft introduce encrypted configuration files (which I hope they do soon) there are a number of things you can do:

  1. Restrict access to the folder that the config file is being stored in.
  2. Store your configurations in SQL Server rather than in text files. You can restrict access to the table in which the configurations are stored and, moreover, you can encrypt them too. Voila - encrypted passwords in configurations.
  3. Encrypt passwords within the package by password protecting the package by setting ProtectionLevel=EncryptSensitiveWithPassword. This will require you to pass a password using dtexec.exe at execution time and here again is a security risk - if you are executing your packages on a schedule then you have to make sure that the password is not visible in your scheduling tool.
  4. Encrypt passwords within the package by password protecting the package by setting ProtectionLevel=EncryptSensitiveWithUserKey. I generally recommend against doing this because it means no-one else can edit the package and thus you will undoubtedly encounter problems when deploying your packages to different environments

I personally always go with option #1. It works, and I think its pretty easy to achieve.

-Jamie

 

Published Thursday, April 26, 2007 8:14 PM by jamie.thomson

Comments

 

Darren Gosbell said:

Cool article - I was actually thinking of asking you about this topic. I had pretty much arrived at the same conclusions. I have a couple of clients running AS/400's and #1 was the best approach I could come up with. I was wondering if I had missed something as I am still not completely comfortable with this option.

Bring on encrypted config files

April 27, 2007 4:06 AM
 

Matt said:

You mention that you can encrypt the password when it is stored on a SQL Server. I know this is probably outside the scope of your blog, but can you give an example of how to set this up?

May 1, 2007 4:48 PM
 

jamie.thomson said:

Matt,

Sorry, its not just outside the scope of my blog its also outside the scope of my knowledge. :)

You'll have to go elsewhere for advice on that I'm afraid.

-Jamie

May 1, 2007 5:44 PM
 

LarryC said:

We store lots of stuff in SQL configuration tables and it's pretty easy.  What I could never figure out is how to encrypt those tables and still get the packages read correctly.  If you have any hints on where elsewhere is, I'd be forever in your debt!

BTW your blog has answered more of my SSIS questions (including some of the hardest ones) of any source I've used to date.  Great work and a big THANK YOU! :)

May 3, 2007 12:25 PM
 

LarryC said:

Doh, just posted on the SQL Server boards and an obvious answer came up.  Create the table using varbinary(MAX) fields and create an encrypted view that decrypts the values.  An encrypted instead of trigger could make updates and inserts transparent.  I'm going to take a shot at this and if there's interest I could post the results.

May 3, 2007 1:15 PM
 

jamie.thomson said:

Larry,

That's good news. If you find out anything interesting, let me know.

-Jamie

May 3, 2007 5:00 PM
 

Larry Charlton said:

I've taken a stab at posting an article on MySpace with a script you can run to encrypt it.  Hope it's useful and people can find it :)

May 4, 2007 3:13 AM
 

LarryC said:

May 4, 2007 3:18 AM
 

Larry C said:

This blog turned out a lot better.  With luck you might even be able to read it!

http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html

May 4, 2007 4:28 AM
 

robbie said:

Oh at last a blog that tells me where I am going wrong, I am new to SSIS and when deploying a package that worked on my laptop to production it failed :( i worked out it was becasue of the passwords, if I follow your suggestion 1 how do i modify the config file for it to work correckly,

thanks

Robbie

July 31, 2007 1:01 AM
 

jamie.thomson said:

Robbie,

Easiest way is to edit it manually i.e. someone types it in. If you think about it this HAS to happen at some point.

On my project(s) we do something a little bit cleverer. We have built a WIX (http://wix.sourceforge.net) installer to deploy all our SSIS "stuff".  We call the MSI that WIX gives us from the a batch file. We pass in all connection strings to the MSI - this means all we have to do is produce a seperate batch file for each environment.

I've been meaning to blog about this for ages but haven't done so yet. Watch this space.

-Jamie

July 31, 2007 3:14 AM
 

SSIS Junkie said:

Recently on this blog I mentioned in passing that I have been working for some considerable time now

August 6, 2007 8:57 PM
 

Tiago said:

Maybe I'm missing something but could you explain me what is the advantadge of the encryption solution (through the use of encrypted view and instead of triggers) once it provides a view that is returning the same decrypted information as in the "original" table?

Thanks in advance.

January 16, 2008 1:00 PM
 

jamie.thomson said:

Tiago,

I personally can't speak authoratively about encryption I'm afraid. You'd have to go elsewhere.

I fail to understand the relevance of your question to this blog entry.

-Jamie

January 16, 2008 2:12 PM
 

stittdba said:

I was hoping to find out if changing the protection level of a package at runtime was a possibility. I have about 300 packages created, however, the protection level is set to EncryptSensitiveWithUserKey. For best practices, my user should not be the user SQL Agent uses to execute the jobs. So to avoid going back into each package individually I thought,

<Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32">

<ConfiguredValue>0</ConfiguredValue>

</Configuration>

would alter the protection level to DontSaveSensitive as runtime through the dtsconfig file. It doesn't seem to have worked. Any thoughts?

March 20, 2008 5:25 PM
 

jamie.thomson said:

stittdba,

yeah this won't work I'm afraid. The checks that will occur regarding protectionlevel will occur before configurations are applied.

-Jamie

March 20, 2008 5:35 PM
 

Mycroft Holmes said:

Jamie

Thanks for all the info - a most useful blog.

I use SQL to store the config info and it encrypted the password string on the way in (I assume because SMS displays the *****).

My problem is that I cannot see a list of configs in Job Management when I want to set up the job. I can browse to an xml file but not get at the SQL configs.

I like the SQL configs as it will allow me to launch a package from a winforms UI. I want to set up 2 configs, 1 for scheduled and another for adhoc processing.

July 18, 2008 10:21 AM
 

jamie.thomson said:

Mycroft,

Yeah, not sure if the job subsystem allows you to pick a SQL config. Not sure to be honest, I never use them and I haven't got a SSIS instance to hand with which I can check.

I think you might br screwed. Mind you, if you use cmd-line subsystem (which you should be doing anyway: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html) then it won't be a problem.

-Jamie

July 18, 2008 1:33 PM
 

Mycroft Holmes said:

Once we get into the  cmdexec all sorts of permissioning issues raise their ugly head - and we work on hardened servers. Looks like the work around (kludge) is to deploy multiple copies of the package (with configs set into the pkg)

More work required!

July 21, 2008 4:16 AM
 

server error aspx http | Digg hot tags said:

December 12, 2008 6:15 AM
 

spartan said:

hi jamie,

im having a script task which opens an excel file and extract some data into database. when i run this package it run successfully. but when i schedule this package some errors occuring. i know the problem is with script task only but i don know wat is it.

first when i schedule the package the following error occurs

Programmatic acces to visual basic project is not trusted.

then after sometime when i schedule it, it shows the following error

cannot create ActiveX component

then after sometime when i schedule it, it shows the following error

The script threw an exception: Unable to get the Open property of the Workbooks class

i don know wat is happenning.. the script task is running well if i run it manually..

any help is appreciated

thanks in advance

(explain in detail jamie...coz usually i wont understand your short solutions)

January 7, 2009 9:33 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems