Package Ownership

Ok... Now I'am panicking.... I dont know how to resolve this issue...

Package created by me, imported to sql server by dba, its executed under a different user id (all are NT logins)

Execution Command

Code Snippet

DTEXEC /SQL "\pkg1" /SERVER SERVER1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Below is the error.

Code Snippet

Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:01:51 AM

Could not load package "\pkg1" because of error 0xC0014062.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'pkg1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.

Source:

Started: 9:01:51 AM

Finished: 9:01:51 AM

Elapsed: 0.156 seconds

Please some body help me on this.
Thanks

And what happens when you execute it with your ID?|||

It fails.

Scenarios:-
1. I Develop, I Import, I Run - every thing works fine.

2. I Develop, DBA Imports to SQL SERVER, I Run - Package fails
3. I Develop, DBA Imports to SQL SERVER, Another user runs - Package fails

4. I Develop, DBA Imports to SQL SERVER, DBA Runs - Package runs.

Finally I figuring it out that either imported user has to run or system admin has to run?

Thanks

|||It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive.|||

Phil Brammer wrote:

It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive.

One other thing... Are you importing to MSDB or the file system? I ask because if you are importing into MSDB, one other thing to try is to set the security upon import to "Rely on SQL Server Roles and Storage" or something like that.|||

Phil Brammer wrote:

It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive

One other thing... Are you importing to MSDB or the file system? I ask because if you are importing into MSDB, one other thing to try is to set the security upon import to "Rely on SQL Server Roles and Storage" or something like that.

We are importing to MSDB. But even file system was giving the same problem.

I already set this property to dontsavesensitive in the designer.

This property is also available when we import the package to sql server so far we left it blank, i'm going to try to set this as dontsavesensitive in this.

Thanks

|||This link provided very useful information to me: http://support.microsoft.com/kb/918760/

|||

Got this resolved by giving the executing user the role of db_dtsoperator. Now the package is fialing with different error as below.

Code Snippet

Failed to decrypt protected XML node "DTS:Password" 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

This error is coming even when I have set the protection level as Dont Save Sensitive in the dtsx file (in VS 2005). Should I also set this when I import to MSDB?

Thanks

sql