Friday, October 03, 2008 11:44 PM
I get the following error when I try running a package on another server...
Description: Failed to decrypt protected XML node "DTSassword" 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.
I developed the package on my workstation with a local instance of SQL Server 2005. It runs great there. I change the Connection Manager object to point to a Server instead (we’ll call it Elmer). I am using the dtexec.exe utility to run these both locally on my workstation and remotely on Elmer via a remote connection. Local works fine when pointing to my local database using SQL Server Authentication. The users for the databases and login for both instances of SQL Server are the same. I set up a Package Configuration for this package that will allow me to tweak the InitialCatalog and ServerName for the connection manager that points to the destination database on Elmer. At this point with only the those two parameters defined in my Package Configuration I get the aforementioned error. If I include the UserName and Password in my Package Configuration and explicitly include my password (as plain text) it runs fine on Elmer. Bad idea in terms of security of course but I can’t get past the error without doing this. The ProtectionLevel of the package is set to EncryptSensitiveWithUserKey. If I set the ProtectionLevel to DontSaveSensitive I get the following error...
Code: 0xC0202009 Source: TestPackage Connection manager "Some.Connection" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.”.
Does any of this sound familiar to anyone? I haven’t been able to gleen enough from this thread (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=142920&SiteID=17) to give me a definitive solution so I am hoping this being entered as a new thread without a solution attributed to it will spur a little more interest and views from folks. I’m a little new to this so be gentle.
Saturday, October 04, 2008 12:37 AM
Well part of the aforementioned is not quite true. I just realized that when I use the plain text UserName and Password in the Package Configuration it still errors with the same error but its not a show stopper. It continues to run and finish loading the data into the tables. The subsequent error is the following...
Error: 2008-10-03 17:21:06.91
Source: TestPackage Connection manager "Some.Connection"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the
other end of the pipe.
Error: 2008-10-03 17:21:06.94
Source: Data Flow -CC SQL Server Destination 
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Some.Connection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection
method call failed.
Error: 2008-10-03 17:21:06.97
Source: Data Flow -CC DTS.Pipeline
Description: component "SQL Server Destination" (3713) failed validation and returned error code 0xC020801C.
Progress: 2008-10-03 17:21:06.99
Source: Data Flow -CC
Validating: 100% complete
Error: 2008-10-03 17:21:07.00
Source: Data Flow -CC DTS.Pipeline
Description: One or more component failed validation.
Error: 2008-10-03 17:21:07.02
Source: Data Flow -CC
Description: There were errors during task validation.
Not quite sure what to make of this? I thought any follow on errors were usually related to the first and if you get the first one taken care of in most cases the remaining ones pretty much go away. Thats been my experience so far anyways. So i get the original error and then this series of ones afterwards and it wont run the package . But if I use the plain text UserName and Password in the Package Configuration I get the original error but it doesn't stop and continues to run successfully.
Sunday, October 05, 2008 11:12 PMI think your problem is the package ProtectionLevel set to EncryptSensitiveWithUserKey. This is the default package encryption setting and it doesn't work very well, unless you setup your SQL Job Agent to execute thru proxy account. You may set up the encryption of the package with password or check the documentation for setting up a proxy account here.
Give it a try and let us know the result.
Thursday, October 09, 2008 8:34 PM
You are not kidding when you say it doesn’t work very well. I guess there may be some pieces here I am not getting. When you build a package without any external config file it seems to encrypt the sensitive data with in the package fine. When I try and use the Package Configuration option so I can change the database name, is when things seem to go south. I have tried every setting there is for the ProtectionLevel and I still can’t get it to work (EncryptSensitiveWithUserKey, EncryptSensitiveWithPassword, Dont SaveSensitive, etc…). When I try using the EncryptSensitiveWithPassword and type a Password in the field above the ProtectionLevel it doesn’t seem to retain it at all. I am really only trying to run this from a command line with some input parameters in the .dtsConfig file with an encrypted password. By not selecting the user name and password to go in the .dtsConfig file you would think these values are still internal to the .dtsx package itself and would work but it doesn’t. This package would never be scheduled via SQL Job Agent (for less than good reasons totally out of the realm of my control unfortunately) so it’s not clear to me how setting up a proxy on SQL Server would do me any good. Would using the proxy allow me to use Windows Authentication in stead of SQL Server Authentication? How is the proxy referenced in the connection manager in the SSIS side of things?
Thursday, October 09, 2008 10:15 PMMathias,
Did you check the article I have referred at all? Setting up a proxy account and running under proxy account, allows you to run your SSIS package under an account other than the account under which the SQL Job Agent is executing. The default account is the Local System account and it usually doesn't have too much permissions to other databases or remote machines. When you setup your SSIS package with password, you have to make sure you use custom command line, when executing the SQL Job Agent step (you will find the option somewhere in the config dialog). Then you will have to provide additional parameter "/De[crypt] <encryption password>". Simply setting up the SQL Job Agent step is not going to work because the package sensitive data has to be decrypted somehow.
I understand your frustration because we have all been there and we know the feeling. I wish it was simpler to setup, but you will make it work. I'm sure. Just a little bit more patience.
Thursday, October 09, 2008 10:33 PMI did and I am assuming you are talking about method 2 on that document, right? I guess its not 100% clear to me what they are requiring when they say a Microsoft Windows user account. Are they talking about creating an application account or Windows user account on the domain this server is on?
Thursday, October 09, 2008 11:42 PMWindows user account. If you want you can try to setup and run your package, using the user account you have used to author the package. In this way you can use EncryptSensitiveWithUserKey encryption option for the package.
Monday, October 13, 2008 5:13 PM
Just so I am clear on this, you are saying its not possible to use SQL Server Authentication credentials to make my connections and have those encrypted when used in conjunction with a Package Configuration file? You keep referring to SQL Agent Job and I want to make it clear thats not a part of my process, nor can it be due to the constraints I have been given. This package will only be run via the dtexec.exe utility. The reason I am trying to use SQL Server Authentication credentials is so I don't have to use my Windows User account credentials which may or may not be a part of the domain where this solution is eventually deployed. I am not real partial to having to set up a Windows User application account on our domain (in lieu of my own account) since my security folks aren't real keen on application accounts in general. Sorry if I am a bit confused by what direction this proposed solution is taking, but it seems to be heading in a direction I am trying to avoid in the first place. I guess I am a bit frustrated that I can't seem to get this to work using the SQL Server Authentication credentials. Thanks for hanging in there with me though, I really do appreciate your input.
- Proposed As Answer by Mark 65 Friday, August 07, 2009 3:09 AM
Monday, October 13, 2008 6:01 PMModeratorWhat are you storing in the package configuration (which package properties) and what ProtectionLevel are you currently using?
Monday, October 13, 2008 8:19 PM
There are two problems I am reading here:
1. Unless you are signing packages and passing that key onto the next executing machine, be it server or another development machine, none of your credentials will be available on the new machine when you use the setting "EncryptSensitiveWithUserKey". It is machine and user account specific. Unless you are going the signing route, I would suggest using "EncryptSensitiveWithPassword" and you will be able to pass this password to the package at runtime, but obviously take a look at your security designs for your team overall and then make a choice.
2. The detailed error provided in your second post also points to a problem with the connection setting, stating a problem with the Shared Memory Provider -- Shared Memory Provider protocol only works when SQL and the connecting program are on the same machine, you will need to enable TCP or named pipes for connections between two different machines.
Monday, October 13, 2008 8:48 PM
jwelch - I am using the "EncryptSensitiveWithUserKey" setting and I primarily want to have the database name set in the Package Configuration.
SidAtkinson - Just to make sure I am on the same page, any key that is generated in the package when the "EncryptSensitiveWithUserKey" setting is used is derived from the user account (in this case the SQL Server Authentication credentials I use in the connection manager) and the workstation id (machine) the package was built from? That would make some sense to me at least in the context of the problem I seem to be seeing. If I want to build a package on my development box but want to build the package and deploy it to a server I can never use the "EncryptSensitiveWithUserKey" since it uses the development boxes identity as a part of the key and this will never match the server its deployed to and its identity. Did I get that right? I am going to retry it again with the "EncryptSensitiveWithPassword" option and see where that gets me. I will start out without any Package Congifuration just to make sure I am seeing some baseline behaviour first without further complications.
Also on the second item you mentioned the package is run from a command line on the server that has the database and SQL Server instance installed.
Monday, October 13, 2008 9:20 PM
The key generated is from the user account of the machine the package was developed on -- completely separate and agnostic to the credentials used in the connection manager.
The second options was mentioned for if you choose "EncryptSensitiveWithPassword", you can pass your new package password as a parameter to the dtexec program. Check out this link, http://msdn.microsoft.com/en-us/library/ms138023.aspx and look at the /decrypt option.
here is Jamie's blog post from a while back that might help you and your team make a decision:
Monday, October 13, 2008 9:48 PM
I think you answered my question finally via the second link. Its funny that if you look at the actual code of the package itself it encrypts the passwords just fine but for some reason you can't get the same level of functionality in the Package Configuration file. I can either plain text the password in the Package Configuration file, plain text the /Decrypt password in the batch file that runs the dtexec.exe utility. The other options are SQL server table and restrict access to the directory with the batch file and/or Package Configuration file. I guess my next question is can you do a little of both? I would like to try using the SQL server table option for the password storage and encryption but also still pass some non-sensitive parameters in via a Package Configuration file. Is that doable?
P.S. One other quick item you can confirm for me, there is absolutely no correlation between the password used to "EncryptSensitiveWithPassword" and the Connection Manager password as well?
Monday, October 13, 2008 9:58 PMModeratorYes, you can mix and match configuration types.
Monday, October 13, 2008 10:06 PM
Tuesday, October 14, 2008 1:05 AM
just to give you a final confirmation:
- there is no correlation between the passwords stored in the individual connections to either sources or destinations used in your package(s) and the package password -- two completely different animals.
Have fun with security!
Wednesday, November 26, 2008 9:05 PM
I had the exactly the same problem. Setting the proxy was the solutions.
Wednesday, March 18, 2009 7:30 PMAnother option, besides setting up the proxy is to modify the command line of how SQL Agent run SSIS package. Here is an example: /FILE "\\NYSAN\VS2005\Integration Services Project1\MyPackage.dtsx" /CONNECTION "Server.Database.sa";"\"Data Source=ServerName;User ID=sa;pwd=password;Initial Catalog=DatabaseName;Provider=SQLNCLI.1;Persist Security Info=False;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Wednesday, August 19, 2009 1:48 PMMAKE SURE THAT LATEST SERVICE PACK FOR SSIS IS INSTALLED.