CODEWITHSUNDEEP
sql-serversql-server-2008ssissql-server-2008-r2
moleboy
moleboy

Reputation: 864

SSIS in SQL Server: How to get it to store password

I am an Oracle guy who suddenly got SQL Server and SSIS dropped in his lap, so I am probably not using the terminology in the correct manner, but here goes:

There is a SSIS package that pulls data from the Oracle database into our SQL Server 2008 R2 warehouse. If I open this package in SSIS Visual Studio 2008, I get prompted for a password:

The sensitive data in the package 'MyRefresh.dtsx' is encrypted with a password.

for the package itself. I enter the password. I run the package. Works great. The previous guy had loaded this package into SQL Server with a job to run at 1am every day and it worked great there too.

Recently, there were some database changes. The package, of course, stopped working. I was able to fix it, and, again, it runs great if run through SSIS Visual Studio 2008. However, when I loaded it into SQL Server, and the job runs, it fails with:

0xC001405F Failed to decrypt an encrypted XML node because the password was not specified or not correct.

Where/how to I specify the password so the job can run?

Upvotes: 0

Views: 3634

Answers (3)

Mark Davidson
Mark Davidson

Reputation: 414

Late answer, but might be helpful to other users/thread visitors

In short, to load the package to SQL Server it must be exported with new credentials specified, and then imported back into specified folder.

Here is the article I found on setting the SSIS package encryption manually in SSMS, that provides a quick tutorial on how to Import/Export an encrypted package.

Please note that the Protection level option regards sensitive data, in one case, or all the data included in particular package in other. Data that is considered sensitive is set by default in Integration Services: variables previously marked as delicate, non-changeable XML tags, which are controlled by the SSIS service, and password, which can be considered sensitive if the ‘Encrypt all data with password’ is chosen.

Package protection levels:

  • Do not save sensitive data: if sensitive data exists, it will not be included after the exporting of the new package, remaining unavailable;
  • Encrypt sensitive data with user key: sensitive data will be encrypted with current user credentials, and package still can be used on local server. Which data will be considered as sensitive, depends on the creator/owner of the package;
  • Encrypt sensitive data with password: with this level, a password must be provided – this kind of encryption is desirable, if user want to keep only sensitive data private.
  • Encrypt all data with user key: same as the encryption of sensitive data, it can be used on local server, but it regards all the data within the package;
  • Encrypt all data with password: this level encrypts all data within the package, password is required, and it provides a 100% privacy.

Hope this info is helpful.

Upvotes: 8

Nick.Mc
Nick.Mc

Reputation: 19184

If you have the opportunity I suggest you no longer use the EncryptAllWithPassword protection level. Read here for more info about package encryption levels:

http://sqlblog.com/blogs/eric_johnson/archive/2010/01/12/understanding-the-ssis-package-protection-level.aspx

In short the idea of package encryption is to stop people opening up the package XML to extract plain text passwords. But generally this is implemented in a insecure manner which defeats the purpose.

I suggest you use windows authentication throughout instead:

  1. Ensure your Oracle server supports external authentication
  2. Create an externally identified login to Oracle using the SQL Agent windows service account
  3. In your Oracle connection manager, use external authentication (login with user / and no password)
  4. If you have any SQL Server connection managers you need to do the same (in SQL Server this is called windows authentication)
  5. Lastly ensure that all developers are set up with windows authentication in SQL Server and Externally identified authentication in Oracle so they can run the package in BIDS

Now you don't need to encrypt your package anymore (you can use DontSaveSensitive). The authority for all operations are against the SQL Agent service account.

You don't need to remember a package password or an Orace login password any more.

Also for example if you need to rotate the password on your Oracle login, originally you would have to go and change this password in Oracle and in your package. But by using windows authentication this is no longer necessary.

I can give you more info if you are interested.

Upvotes: 3

shree.pat18
shree.pat18

Reputation: 21757

You can use the /de switch along with the dtexec utility for your password like so:

dtexec /f <filename> /de <password>

Upvotes: 1

Related Questions