|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Monday, August 30, 2010 8:06 AM
Points: 121,
Visits: 97
|
|
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 v
alid 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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 7,
Visits: 4
|
|
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.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, August 30, 2010 8:06 AM
Points: 121,
Visits: 97
|
|
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.
http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_thread/thread/c55536ffed28bb99/76f078ca18a270a5%2376f078ca18a270a5
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Friday, August 20, 2010 6:28 AM
Points: 1,540,
Visits: 191
|
|
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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, August 30, 2010 8:06 AM
Points: 121,
Visits: 97
|
|
| 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?
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Friday, August 20, 2010 6:28 AM
Points: 1,540,
Visits: 191
|
|
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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, August 30, 2010 8:06 AM
Points: 121,
Visits: 97
|
|
| 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?
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Friday, August 20, 2010 6:28 AM
Points: 1,540,
Visits: 191
|
|
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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, August 30, 2010 8:06 AM
Points: 121,
Visits: 97
|
|
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.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Friday, January 01, 2010 6:00 AM
Points: 222,
Visits: 64
|
|
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 <<<'
PRINT '>>> This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
PRINT ''
PRINT '>>> Create Objects <<<'
PRINT ''
--################################################## Check and Drop Existing ########################################################################
DECLARE
@proxy_name SYSNAME,
@subsystem_name SYSNAME,
@UserName SYSNAME,
@credential_name SYSNAME,
@RowCount INT
SET @credential_name = 'TestProxy2'
SET @proxy_name = 'TestSSISUser2'
SET @subsystem_name = 'CmdExec'
SET @UserName = 'MyLapTop\TestSSISUser2'
--################################################## CREATE Login ###################################################################################
SET NOCOUNT ON
USE [master]
IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = @UserName)
CREATE LOGIN [MyLapTop\TestSSISUser2] FROM WINDOWS WITH DEFAULT_DATABASE = [MyDB]
--################################################## CREATE User and Grant Rights on DBS ############################################################
USE [MyDB]
IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'MyLapTop\TestSSISUser2')
CREATE USER [MyLapTop\TestSSISUser2] FROM LOGIN [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [DB_DataReader], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [DB_DataWriter], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], [MyLapTop\TestSSISUser2]
GRANT EXECUTE ON [sp_dts_addlogentry] to [MyLapTop\TestSSISUser2]
USE [msdb]
IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'MyLapTop\TestSSISUser2')
CREATE USER [MyLapTop\TestSSISUser2] FROM LOGIN [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [db_dtsadmin], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [db_dtsltduser], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [db_dtsoperator], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], [MyLapTop\TestSSISUser2]
EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], [MyLapTop\TestSSISUser2]
--################################################## CREATE Credential ##############################################################################
USE [master]
IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = @credential_name)
CREATE CREDENTIAL [TestProxy2] WITH IDENTITY = 'MyLapTop\TestSSISUser2', secret = 't3st'
--################################################## CREATE Proxy ###################################################################################
USE [msdb]
DECLARE @ProxyTable TABLE (subsystem_id int ,subsystem_name sysname,proxy_id int,proxy_name sysname)
INSERT INTO @ProxyTable EXEC sp_enum_proxy_for_subsystem --@proxy_name=@proxy_name, @subsystem_name=@subsystem_name
select proxy_name from @ProxyTable WHERE proxy_name = @proxy_name AND subsystem_name = @subsystem_name
SELECT @RowCount = @@ROWCOUNT
IF @RowCount = 0 BEGIN
PRINT 'Creating Proxy'
EXEC SP_ADD_PROXY @proxy_name=@proxy_name, @credential_name=@credential_name
END
DECLARE @LoginProxyTable TABLE (proxy_id int,proxy_name sysname, flags int, [name] sysname, sid varbinary(128),principal_id int)
INSERT INTO @LoginProxyTable EXEC sp_enum_login_for_proxy --@proxy_name=@proxy_name, @name=@UserName
select proxy_name from @LoginProxyTable WHERE proxy_name = @proxy_name AND [name] = @UserName
SELECT @RowCount = @@ROWCOUNT
IF @RowCount = 0 BEGIN
PRINT 'Granting Login to Proxy'
EXEC SP_GRANT_LOGIN_TO_PROXY @login_name=@UserName, @proxy_name=@proxy_name
END
EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @proxy_name=@proxy_name, @subsystem_name=@subsystem_name
EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @proxy_name=@proxy_name, @subsystem_name=@subsystem_name
--###################################################### end script ##################################################################################
PRINT '>>> This execution on server: ['+@@SERVERNAME+'] ended at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
-------------------------------
|
|
|
|
