sql server dts: Protection Levels and Deployment -- sql server dts

Protection Levels and Deployment

Ethem Azun
8/27/2006 6:22:02 AM

sql server dts:

Hi,

i'm a bit confused about how protection levels, deployment and configuration
fit to each other.

I have sensitive data in my connection strings (such as passwords) and it's
out of my reach to change this design decision. These info should be kept
independent of the user accessing it, so the most appropriate selection for
Protection Level seems to be EncryptSensitiveWithPassword. So far so good.

Now, I want to create a Sql Job to run this package. I create a new Job,
then a new Step and set the type to Integration Package. I select my package
on the File System, and try to go to the Data Sources tab. I'm prompted with
the password dialog, which is great! I can edit my connection strings,
include my passwords knowing that without entering the main password, no one
can see them.

The problem starts if you save your changes, close the job and re-open it.
Go to the steps, open the step you just created, enter the main password,
click on the Data Sources tab, and the passwords are gone! So each time you
want to change one connection info, you have to enter everything again!
There's no warning for this, and your entries are deleted automatically,
meaning next time you run the job, you'll get into trouble...

I think I'm missing something here. All I want is to be able to change the
connection strings using this UI and I don't want them to get lost each time
someone else opens them. This is so annoying that I would even accept a way
to get rid of this "security feature".

RE: Protection Levels and Deployment

Charles Kangai
8/27/2006 10:36:01 AM

Hi Ethem,

Instead of saving your packages to SSIS Package Store, you could use the
other server-based option, MSDB. You could then dispense with passwords or
user keys (which are even worse), and use database roles to protect access to
your packages.

I agree that the security options are a bit of an overkill for most people.
I wish there was an option that allowed people to not use any security
whatsover, even if the option was not default. Microsoft have followed their
security strategy to the letter here: "Secure by Design, Secure by Default,
Secure in Deployment and Communication".

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
Author of Learning Tree's 4-day course: "SQL Server Reporting Services" URL:

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.