I have some SSIS created with a domain user account, and set the security level for the package to EncryptSensitiveWithUserKey. The credential and proxy for the package to run with SQLAgent Job has been set and used. The job is running well when we logged into the server and test on it with the domain account, but later we found out that the Jobs will fail if the domain user are actually log off from the server itself (if the domain user is currently logged in or just disconnect from the server, the Job will still running fine), it seem like it will fail to decrypt the Sensitive Data for the connection string (DB password). I do tried log in and then disconnect with other user (remote access), it seem it will fail executing the job too. Is there anything I can set base on the current security level to avoid this, or did I miss out anything? I some search on Google but not luck on this. By the way, it is on Windows Server 2003 and MS SQL 2005 64 bit

Are all your Connection Managers and other Resources set to use Integrated Windows Authentication? I'll bet not, that you have some saved passwords somewhere within your package. And when you set the Protection Level as you did, SQL Agent cannot depcrypt that password because it was not the one who encrypted it in the first place. you need Configurations, my friend. Save the Connection String info in a Configuratin table so that when it gets executed by another user/process, it will go and lookup those Conneection Strings before it tries to connect to them.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I though the reason we use the credential and proxy for the SSIS with security level EncryptSensitiveWithUserKey is to decrypt the connection password? Some of the connection is to external server, where we also didn't know the password, have to request user to key it into the connection managers and save it there in the visio studio so that we can use it later. The SSIS has been set to run with credential of the creator login, what I dont understand is, it we are logged in with the creator account into the server, which is a domain user, the SSIS run itself fine from the SQLAgent job schedule, once the user account was logged out... everything turn to fail.

The SSIS package is run with the credentials of the SQL Server Agent or Proxxy if you have one. If You have Sensitve Dta like User and Password for the external servers is deleted form the package if aonther Acccount than the Creator opens or runs the package. I agree with Todd to use the Configuration to store user and password and pass both to the Connectionstring while executing the package.

Some how it is not an options, due to the external DB it is related to banking data, we should not know the password itself. In fact the creator domain login password is key in by the user when create the credential, after the server go live, very high chances the user will change the password again. Did any one have any idea or experience what I facing now before? From my study, the creator is log in or not in to the server should not affect the job that run in the SALAgent Job since it should by itself, I have tried both file system and MSDB, both is giving me the save result on the error where it seem the encrypted password was unable to be decrypt if the creator is not logged into the server when the job is running.

Do you happen to refer to mapped drives? Talk to me now on

Sorry for the late reply, as far as I see from the drives we using, all is local disk, so I assume there is no mapped drives are in use. All the SSIS is involve data transfering and processing only, no any extra file in use. But it is a cluster server.

I have run a few more round of testing. It seem the creator proxy have no problem logging with the credential and proxy detail. But it seem it only able to decrypt the sensitive data, if there is a log in session of the server is exist, else it will return error on decrypt the Sensitive data. It will return the error: Failed to decrypt protected XML node "DTS:Password" with error 0.80070002 "The system cannot find the file specified.". ...... It is the same error if we run the Job/Package with a diffence user that not the creator of the SSIS, where the SSIS is protected with the Level - ProtectSesitiveWithUserKey. Anyone have any idea what is actually happening?

Did you tried to change the ProtectionLevel to " EncryptSensitiveWithPasswort"? In that case, the package runs after the Package Passwort ist passed to it.

EncryptSensitiveWithPassword will work. In fact I have tried all the protection method, only EncryptSensive/AllWithUserKey failed when the Creator(Domain User) account was log out. I even tried to local machine user to be the creator for testing, the SSIS will running fine even the creator logged out. One of the problem with EncryptSensitiveWithPassword is, if you right click the job and select the create query, the Password for decryption is with in it.... I have found some post that other facing the same problem before, but some how at the end, none of them found the solution, I bet all of them have no choices but to choice other protection method. Even myself have to choice relay on the database role as the protection level for now since the project can't be delay any more. I was interested if anyone can find out what is actually happening in it? From the error, I can see the domain user credential/proxy has no problem to log in, the problem occur when decrypting the sensitive - password, it that mean a domain user should not be use for the credential/proxy at all (with protection level EncryptSensitiveWithUserKey) if windows authentication is not use for the database log in?

It's best practice to not deploy packages with EncryptSensitiveWithUserKey. Why? Because you have to run the package with the same credentials as the developer. Why is this bad? (Yes, it's BAD.) Because you then need to run the packages in SQL Agent (or whatever other mechanism) with the credentials of a live person - the same person who created the package. This can cause severe problems when passwords change (you do have a password policy, right?), when their account gets locked out or disabled, or when that person leaves the company. What happens then? All your jobs start failing. Your alternative is to create a "service" account to run the packages in. But this requires you to develop the packages in that service account's context as well. Which means you've granted elevated priviliges to that service account... except you don't have the problem of "firing" the user. But now you DO have the problem of several developers having access to a fully privileged account that they know will never expire - even after they leave the company. So - your best option is to either encrypt sensitive with a password, or not store sensitive information in the package. If you have an issue with encrypting with a password, then don't store passwords in the DTSX or the job. Place them in an XML config file. "But... that's PLAIN TEXT!" you say? Place it in a secured area that only admins and the least-privileged service account have access to. Problem solved. Talk to me now on

The creator account is not a problem... main reason is, after the project done, the user will maintenance the SSIS them self, and due to the sensitive data on the external database, even we as the developer, can not get hold on the password, =.=. Currently the password was store in the connection manger in the visual studio, password is key in by the user them self. I do know that storing the connection string in a XML config file is much easy to manage and can solve a lots of the problem, but this suggestion was directly rejected, due to the user believe it will never pass their internal auditor when the system want to get live. So what we can do it to set all the SSIS protection level to relay on the server role, which at least, didn't leave any trace outside (yeah, their internal auditor will check everything, if any sensitive data like password was trace by them in plain text, we can prepare a long answer for it....). And the reason I still monitor this thread is that, I hope to know is there anyway the EncryptSensitiveWithUserKey will be working on my case, or there is no way that a domain user account can be use in such case (may be due to the registry key that use to decrypt the Sensitive data was remove from the server when the user log off? which I really not sure on, since MS didn't document down such information), it will be a good knowledge I believe

Persuing use of a user account to execute these packages is not a good practice. The auditor should have a much bigger issue with this architecture than an independently secured password file. Talk to me now on