Failed to decrypt XML node "DTS:Password"

Hi!
I made an SSIS package and it runs fine on my workstation, but it fails when I run it on the SQL Server.
I'm using an ODBC connection and when I run the package, I get the error message:
"Login failed. Check for valid user ID, server name or password".
I tried to load the project in Visual Studio on the Server and got the error message above:
Failed to decrypt XML node "DTS:Password"
How can I set up the permissions for decrypting the password?

Thanks for any help!

The problem is that you have the ProtectionLevel set to "EncryptSensitiveWithUserKey". This means the package can be executed only by the user that created the package and on the machine that it was created. (unless you use a domain account and use machines in the same domain)

If you import the package on your server I recomend to change the ProtectionLevel to "EncryptSensitiveWithPassword" - will ecnrypt sensitive information like passwords - or if you store the package on SqlServer you can use "Rely on server storageand rolse for access control" - this means that the package will be stored unencrypted on the SQL Server.

The package can be stored unencrypted ONLY in the SQL Server.

HTH.
Ovidiu Burlacu

|||Thanks for the explanation!
I tried to use the last option"Rely on server storageand rolse for access control", however it causes a lot of error messages in the Business Intelligence Studio and I can not create a build with a deployment manifest. The help sais, this option is not accessible from Visual Studio, but I can not figure out, how to use it.
Can you tell me, how I can use it?
|||

Are you saving the package on a SQL Server or to a file? You can use ServerStorage ONLY if you save the package on SQL Server

To save a package from BIDS directly to SQL Server use File-> "Save Copy of <package_name>.dtsx As..." and in the dialogs that appears you can specify a SQL Server

If you want to create a deployment manifest encrypt with a passord and when you run the Deployment wizard and specify the destination to be a SQL Server the you can choose the "Rely on server storage and role for access control".

HTH,
Ovidiu

|||Thanks alot!

Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B

When and why does this error message appear..

Error 1 Error loading MasterPackage.dtsx: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

are you using package configurations? if so , how are they being stored (db, XML file ect.)

what is your ProtectionLevel set to on your package properties?

how is the package being run? (debug, SqlAgent, execcute w/o debug , command line ect.)

|||

I have a master package which is calling other packages...I just opened to set some package configuration but did not set and then it's coming out with this message when i try to open my master package but is running fine even if comes out with this message...

|||

what is the ProtectionLevel property set to on the children and parent? Right click properties -> ProtectionLevel)

it appears that when you are opening the package it cannot decrypt the sensitive information.

This often occurs when developing on one box and promoting to another.

|||

my protection leve is set to EncryptSensitiveWithUserKey on both parent and child.

|||As Ryan wrote, this error may occur when moving from one box to another - this protection level uses Windows Data Protection API (DPAPI), which automatically encrypts secrets with user key, but is limited in that it obviously allows only original user to decrypt secrets, and (less known) it only works on original computer only, unless you are using Roadmin Profile.

If you need to move packages to other computers, or execute them under different user account - use other protection levels. See Books Online for details.

Failed to decrypt protected XML node

Hi,

I,ve been searching the forum for answers to this error but with no luck:

Failed to decrypt protected XML node "DTSassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

Setup:
I'm running the packages from the SQL Job Agent - the packages are stored in the file system. The agent is using a proxy account to get the right permissions. I know this because the job has run for severel weeks without errors. The package is calling other packages and is using configuration files. It was actually more than on job that failed (with the same error) - but not all the jobs.

Now it is saying that it can not "find the file specified" - what file would that be? - I'm wondering if it is a package file or a configuration file or maybe another file. It dosn't give me any other information to where the problem is.

Any help is appreciated!

Regards
Simon
What's the ProtectionLevel on the package?|||It has never been changed so it is EncryptedByUserKey - but it is on the same computer with the same user and it has been running so I don't think that it is the problem, but maybe I'm wrong?!?!
|||

The only time I have seen this is when another developer updated the package, and saved it with his user key. That doesn't seem to be true in your case, though.

As a workaround, try changing it to EcryptSensitiveWithPassword, and see if that works better.

|||Thanks for you reply.
I will probably do that - it is only that there is a bunch of packages that I have to go through to changes the setting.

If anybody can give me the reason for this error I would be happy :-S
|||

jwelch wrote:

The only time I have seen this is when another developer updated the package, and saved it with his user key. That doesn't seem to be true in your case, though.

As a workaround, try changing it to EcryptSensitiveWithPassword, and see if that works better.

As I don't have any sensitive data in the package but instead store this information in configuration files I changed the setting to: DontSaveSensitive.

I afterwards started the packages which ran succesfully. I've earlier tried just to open a package and afterwards save it and the error would go away. So I will monitor the package to see if it fails again at a later time.

Thanks jwelch for your help.

Failed to decrypt protected XML node

Failed to decrypt protected XML node ... blah blah.

Looks familiar, right?

Package Protection mode is : EncryptSensitiveWithPassword

Package runs fine from within BIDS. Close package. Open package - asks for password. So far so good.

When running from command prompt with:

dtexec /File "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\MyPackage.dtsx" /De mypassword

Get that cryptography error.

Doesn't happen for other packages... what can I be doing different/wrong?

Thanks,
NiteshDoes your password contains any of the following:
a) space characters - use the format /De "your_password"
b) punctuation characters - use the format /De "your_password"
c) " - double any "
d) ; - use the format /De "\"your_password\""

Hope this helps

Regards,
Ovidiu Burlacu|||

Ovidiu Burlacu wrote:

Does your password contains any of the following:
a) space characters - use the format /De "your_password"
b) punctuation characters - use the format /De "your_password"
c) " - double any "
d) ; - use the format /De "\"your_password\""

Hope this helps

Regards,
Ovidiu Burlacu

Nopes.
Password is same as that for other packages, all lowercase alphabets, 8 characters.

thanks,
Nitesh|||

Since your protection is only EncryptSensitiveWithPassword then only sensitive information will be encrypted, and my guess is that this package has been encrypted with a different password (mistyping).

Can you try to open package in BIDS and then go to View -> Error List and see if the same error has been posted?

I'm guessing that the same error happens in the designer, but we should not fail open the package just because we cannot retrieve the Password in a ConnectionString, so instead we post an error.

HTH,
Ovidiu Burlacu

Failed Node Need to rebuild

I am having an issue with the primary node of our SQL 2005 x64 Cluster
(Windows2003EnterpriseEditionx64). After installing SP1 I am getting the
below error when the service tries to start.
So obviously I messed up something during the upgrade. So decided to write
this node off and try and reinstall SQL. Unfortunately it will not
uninstall because it can not see the quorum drive even though it really is
there. Obviously I can not move the group because the service is failed.
How can I for uninstall SQL to try and rebuild the node? Has anyone seen
this issue before? I would rather not rebuild the OS if possible. It is
only SQL that is failing the rest of the box it working great.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 11/22/2006
Time: 8:33:48 AM
User: N/A
Computer: SQL001A
Description:
The SQL Server (MSSQLSERVER) service terminated with service-specific error
17058 (0x42A2).
Hi Tim,
I ran into a similar issue when working on SQL 20056 clusters in my lab. I
did find a link that was very useful when trying to uninstall a node manually.
Take a look at this one and let me know if it helps;
http://msdn2.microsoft.com/en-US/library/ms180973.aspx
Regards,
Sporter
"Tim Murray" wrote:

> I am having an issue with the primary node of our SQL 2005 x64 Cluster
> (Windows2003EnterpriseEditionx64). After installing SP1 I am getting the
> below error when the service tries to start.
> So obviously I messed up something during the upgrade. So decided to write
> this node off and try and reinstall SQL. Unfortunately it will not
> uninstall because it can not see the quorum drive even though it really is
> there. Obviously I can not move the group because the service is failed.
> How can I for uninstall SQL to try and rebuild the node? Has anyone seen
> this issue before? I would rather not rebuild the OS if possible. It is
> only SQL that is failing the rest of the box it working great.
> Event Type: Error
> Event Source: Service Control Manager
> Event Category: None
> Event ID: 7024
> Date: 11/22/2006
> Time: 8:33:48 AM
> User: N/A
> Computer: SQL001A
> Description:
> The SQL Server (MSSQLSERVER) service terminated with service-specific error
> 17058 (0x42A2).
>
>

Failed installation to cluster

I have a 2 node cluster on which I am trying to install a
virtual SQL Server.
I am able to complete the setup process, but the
installation fails after checking the cluster resources.
In the application log I see that the ports have
automatically been disabled (as I'm installing SQL from a
CD without sp3a as yet) and the errors that follow show
that connections to the SQL Server were unable to be made.
Is there a switch for the install to stop the disabling of
ports? Has anyone else seen this issue?
Any help would be very much appreciated.
Kind regards,
Simon
Here is the workaround you need.
PRB: Installation of a Named Instance of SQL Server 2000 Virtual Server on a
Windows 2003-Based Cluster Fails
http://support.microsoft.com/default...&Product=sql2k
Once you install SP3a you can remove the alias.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Simon Call" <Simon@.eduserv.org.uk> wrote in message
news:138901c4265c$191f39a0$a101280a@.phx.gbl...
> I have a 2 node cluster on which I am trying to install a
> virtual SQL Server.
> I am able to complete the setup process, but the
> installation fails after checking the cluster resources.
> In the application log I see that the ports have
> automatically been disabled (as I'm installing SQL from a
> CD without sp3a as yet) and the errors that follow show
> that connections to the SQL Server were unable to be made.
> Is there a switch for the install to stop the disabling of
> ports? Has anyone else seen this issue?
> Any help would be very much appreciated.
> Kind regards,
> Simon
|||Geoff,
Many thanks for this.
Kind regards,
Simon

>--Original Message--
>Here is the workaround you need.
>PRB: Installation of a Named Instance of SQL Server 2000
Virtual Server on a
>Windows 2003-Based Cluster Fails
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;815431&Product=sql2k[vbcol=seagreen]
>Once you install SP3a you can remove the alias.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Simon Call" <Simon@.eduserv.org.uk> wrote in message
>news:138901c4265c$191f39a0$a101280a@.phx.gbl...
a[vbcol=seagreen]
a[vbcol=seagreen]
made.[vbcol=seagreen]
of
>
>.
>
|||You probably noticed this but you cannot disable named pipes if you want to
install on Windows 2003, because setup needs named pipes to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.