Unable to run SSIS package as a scheduled job or with dtexec.exe
-
I have a SSIS package that was sent to me by a developer in another office. He stated that it runs fine for them in a scheduled job. I imported the package to our SQL server after modifying the connection string to match our naming. If I run the package manually in SSIS it runs fine, however if I run the package in a job via SQLAgent it fails. The ProtectionLevel on the package is set to EncryptSensitiveWithUserKey. I tried running the package with dtexec.exe in order to get better loggin and got the following error. I've been unable to decypher just what it means. Anyone experienced this error?
dtexec.exe /FILE "d:\Program Files\Microsoft SQL Server\90\DTS\Packages\Package1.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 3:25:34 PM
Error: 2006-07-13 15:25:34.66
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS
assword" 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.
End Error
-
The agent can't run an ssis package unless the protection level is set to "dontsavesensitive' or 'encrpytwithpassword'. If you change the protection level you lose the saved passwords and you need a configuration file to run it.
-
Thanks for your reply. There must be a way to store this password info in SQL Server as opposed to in a flat file. In SQL 2000 you could store the password in the the package itself. If that can't be done in SQL 2005 then I would like to find a way to avoid having the connection string stored in a file. Does anyone know about SQL configuration for SSIS packages? Thanks.
Here is an article I came across that explains the ups and downs of the different configuration methods but I couldn't find any documentation on how to actually create a config file or how to store the config in SQL Server.
-
From what I have read & understood, if you change the protection level of the package to ServerStorage, then it will store the passwords in the MSDB database & uses the database-level roles to determine access privileges. Here is some info from a Microsoft tech on a similar issue that we worked through:
3. By default the package protection level is set to “EncryptSensitiveWithUserKey” which would mean that only the package owner can execute the package and other users cannot decrypt the sensitive passwords.
Either you can use “EncryptSensitiveWithPassword” or “EncryptAllWithPassword” option however, you need to provide the password in the command line for DTexec.
OR you can use “ServerStorage” to store the package in SQL server and can run the DTexec command.
4. For all values, sensitive information is defined as: The password part of a connection string. However, if you choose an option that encrypts everything, the whole connection string will be considered sensitive. The task-generated XML nodes that are tagged as sensitive. The tagging of XML nodes is controlled by Integration Services and cannot by changed by users. Any variable is marked as sensitive. The marking of variables is controlled by Integration Services. Packages that are saved to the msdb database can also be protected by using the fixed database-level roles. Integration Services includes three fixed database-level roles for assigning permissions to packages: db_dtsadmin, db_dtsltduser, and db_dtsoperator. For more information, please refer to " Setting the Protection Level of Packages" section of BOL.
The error message 0x8009000B is only raised when the package contains the actual protected data - passwords, connection strings that can't be decrypted. If the package had 'EncryptSensitiveWithUserKey' ProtectionLevel, but does not have any sensitive data, you will not see this message.
One of our contributing issues was that we are going to 64 bit. So when we would run the package on the server through SSMS, the package would run fine but when in SQL Agent or a SP, it would fail with the error you gave. I had to reference the 32 bit dtexec file to get the package to run successfully through a agent job or a SP.
HTH,
John
-
Thanks for your reply. I've changed the protection level back to the default “EncryptSensitiveWithUserKey” (I had changed it during troubleshooting). Since this package came from an off-site developer, they may be the owner, and they are definitely listed as the creator. I am running SQL Agent with the Windows System account. Can I run SSIS packages as SQL Agent jobs when I'm using the System account or do I need to create another account to run SQL Agent and make that account owner of the package?
-
Ryan,
It depends on what you need to do in your SQL Agent jobs. If you are accessing network shares or other secured areas, then you would need it to run under a domain account user that has the appropriate security privileges. I believe best practice is to have it running under a domain user. Because of what we do with SQL agent, our runs with a domain admin user account that is also a sysadmin on the server.
HTH,
John
-
Thanks. I will give this a try, though it will need to be a local user since our SQL servers are not on a domain. Is it safe to run SQL Agent with an account that is both a local admin and a sysadmin in SQL?
-
It is safe as long as you have everything locked down. Far as extended sp's & other high powered functions. By default, public has access to several of these sp's & xp's.
Again, it all depends on what you are going to do with the SQL Agent. If you don't need local admin or sysadmin rights to do what you need to do, then don't give the service account those rights. You want to give the least amount of privileges as possible in most cases.
John
-
This package still will not execute as a step in a scheduled job. I've changed the configuration of the SSIS package since my original post and I'm getting a different error so here is the current situation:
1. Created a local Windows account (in administrators group) to run the SQL Agent service and the SSIS service. This user also has a SQL login mapped to it and is in the SysAdmin role. SQL Server service is still running under the local System account.
2. Protection Level in package is set to 'EncryptSensitiveWithUserKey'
3. Connection string in package uses a SQL login to connect to SQL server (this login is mapped a user with access to the appropriate tables and SPs in the db)
When I run this package manually in SSIS it appears to run fine. However, when I run it as a step of a scheduled job it fails with the error:
"The job failed. The job was invoked by user SA. The last step to run was step 1. Executed as user: machinename\username. The package execution failed. The step failed"
I assume that since the package runs fine in SSIS but not as a scheduled job that there must be a permissions issue somewhere. Do the accounts that run SQL Agent, SSIS, and SQL Server need to be the same? Is there something else that I can try? Thanks.
-
I'm struggling with many of the same issues 🙁
Here's my take on some of the problems you are facing:
Does the SSIS do any file operations, etc.? If so, permissions should be checkedd, althoguh it sounds like you've tried to resolve that by giving an administrator rights.
I have an SSIS package that invokes 3 other SSIS packages. Those packages chec for existance of spreadhsheets, clean out temp tables, import data from excel spreadsheets, and move spreadsheets to an archive location. This means I'm doing DB access and file oeprations. It also uses logging to SQL Log Tables.
In the reading I've done, people have said that running packages via DTEXEC command's gives more information, and that is now my experience as well. So, my agent jobs step run CmdExec with a 'DTEXEC ... ' command line. This allows me to use the 'log to history' options on the advanced tag of the job step, and that has helped a LOT!.
In addition, a lot of the reading I've done has talked about the issues of running packages from jobs, and talk about using credentials and proxies to manage this. Since I was running locally, my simple execution of the package via CmdExec worked, but I know that a colleague was having issues with running on a server. The I created the following SQL to created a CREDENTIAL that was able to execute my job. The advantage was that this uses a user that is only in the USERS group - not an admin. Also, not a SQL admin - it it more limited to the role's it needs to run that package.
I have yet to be able to deploy to a non-local machine - I'm having issues with package configuration files and struggle on... I hope this helps a bit. Good luck, and let us know how it goes.
-------------------------------
--###################################################### describe script ############################################################################
PRINT '>>> This script creates the LOGIN, USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES <<>> This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<>> Create Objects <<>> This execution on server: ['+@@SERVERNAME+'] ended at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
-------------------------------
-
I ran the package from a CMD and get the following error: (note I replaced the actual server name and DB name)
Message
Executed as user: "servername"\SSAUser. ....00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:46:59 PM Error: 2006-07-28 13:46:59.26 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS
assword" 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. End Error Log: Name: OnPreValidate Computer: "servername" Operator: "servername"\SSAUser Source Name: "dbname" Source GUID: {496BAC77-6951-4145-A3F7-2D6915276153} Execution GUID: {C4F8ECE1-EE6F-4F93-8FCB-6DFAF646E526} Message: (blank) Start Time: 2006-07-28 13:46:59 End Time: 2006-07-28 13:46:59 End Log Log: Name: OnPreValidate Computer: "servername" Operator: "servername"\SSAUser Source Name:... Process Exit Code 1. The step failed. -
To be honest, I think you are right. Am I right in saying that these packages have never run successfully at your site? They've only run from the developers sire?
SSIS defaults the ProtectionLevel to 'EncryptSensitiveWithUserKey', which is great for development - you just open it and no issues. But since that users is (imho) VERY UNLIKELY to be the userid running the package in production, or even in testing, you cannot (read should not) 'deploy' that package. I would recommend you get the developer to open the package on his machine, change the setting to 'EncryptsensitiveWithPassword'. Save it, build it (if relevant to you - i.e. you would be using the deployment mainfest), and then send you the packages. Have him send you the password in a seperate communication.
You can now open that package with the password, and when executing it from the CmdExec, specify the /DECRYPT password switch.
HTH
-
Yes, it works at the developer site but not at ours. Do you know what the syntax is for the /decrypt password switch? I haven't actually been able to find any examples of dtexec command with that switch. Thanks.
-
yeah - here's a sample. This run's a [ackage from file system. The important bit is that, while the package is still encrypted with user key, this won't help you. You have to open the package with the userid that created it, then change the protectionlevel to EncryptSensitiveWithPassword. Then the decrypt password can decrypt the sensitive parts of the package (passwords etc.).
sample:
DTEXEC /DTS "\File System\SSIS- Product Information Excel Enhancement\Import BuyList with Error Table" /SERVER yourServerName /DECRYPT YourPackagePassword /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
-
By the way, you can generate this for yourself by using the DTEXECUI (just run it from 'start','run','DTEXECUI'). You need to be able to open the package, but after you do that you can work your way through the tabs (once you've set up the EncryptSensitiveWithPassword property on the package, you'll find you get asked the password 3 or 4 times - guess they're serious about security now 🙂 ).
The CommandLine tab will show you a command.line string. You do need to make a few changes to this, because the UI keeps the password internally, and it doesn't specifiy the DTEXEC program. SO you will have something like:
/DTS "\File System\SSIS- Product Information Excel Enhancement\Import BuyList with Error Table" /SERVER yourServerName /DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
... you need to change it to have the password, and program name. i.e. change it to:
DTEXEC /DTS "\File System\SSIS- Product Information Excel Enhancement\Import BuyList with Error Table" /SERVER yourServerName /DECRYPT YourPackagePassword /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
Finally, some of the logging entries it sets up don't seem to work outside the UI, or don't work in Agent (not sure which), so I removed the /LOGGER section, for example.
HTH
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply