asked on

SSIS - package encryption - best practices

Hi :

I have created a package and stored it under a windows account whose password never expires and now i want to encrypt it.I am using SQL 2008

Other than that can someone point me to some really good documentation to encrypt packages and more importantly best practices to be followed.

Thanks

Raja Jegan R

Yes, you can encrypt SSIS packages using various protection levels available.
Kindly go through the below link for more information and revert accordingly:

http://msdn.microsoft.com/en-us/library/ms141747.aspx

kmgingee

ASKER

thanks rrejan:

I did read this before i posted here and got confused,As an expert in this,what would you recommend based on the following requirements :

We are a large firm, but only 3 users will execute this package(sometimes simultaneously).We also plan on putting this package on a timer for automatic execution.Based on the above what is the general industry practice you guys use.

In other words I probably just need what most commonly you all generally do when encrypting and deploying a package into the production environment.

Thanks

Raja Jegan R

Since 3 users are going to work on these packages parallely, I would recommend using encryption level EncryptSensitiveWithPassword so that the three users can use the packages with a same password..
If there is some problems( in your scenario) with a common password, then use individual User keys EncryptSensitiveWithUserKey so that only the user who has the user key can open it up..

If you want to execute the three packages to run at scheduled times, then create an agent job with three jobs running one after the other.

kmgingee

ASKER

not 3 packages: just 1 package and 3 users under one account.

Raja Jegan R

>> but only 3 users will execute this package(sometimes simultaneously).We also plan on putting this package on a timer for automatic execution

Kindly confirm whether that package can be executed in parallel or not..
Packages with statements like Truncate table, drop table can't be executed simultaneously..
If it can be executed parallely, then no issues at all..

kmgingee

ASKER

oops ok,there is a truncate statement in the package,so probabaly wont allow simultaneous execution.That is fine.

But here is another issue : I am able to execute the package ONLY from the windows account I created the package in.Is this because of Windows Integrated Security? If so how can I add permissions for other windows accounts to be able to execute this package? Please note the package is deployed in a shared folder.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kmgingee

ASKER

Thanks rrejan:

>>Use an SQL Login so that all users can execute this package without any issues.

I think that is what I need + EncryptallwithPassword Option.

That way all of us can execute the package from our computer instead of from that windows account

Raja Jegan R

Yes, SQL Login is required or recommended in these kind of shared scenarios..
Kindly let me know if you have any other questions..