July 17, 2015 at 4:47 am
I have minimal SSIS knowledge, but I had inherited some legacy items in my role.
One item is a job to export information from a remote server via execution of a stored procedure to extract the required data.
I.e. SQL Server Agent job runs on Server A to run and get information from database sitting on Server B running a stored procedure the resides in the database on Server B.
The request I had was to simply amend the SQL to a provided version, which I did and manually running the sp, get the answer fine.
The problem is the pre-existing job is failing when run through SQL Server agent.
It is getting the following message.
Executed as user: <servername>\sqlagentservice. ...2.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:31:03 Error: 2015-07-17 11:31:03.98 Code: 0xC0016016 Source: Description: 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. End Error Error: 2015-07-17 11:31:04.28 Code: 0xC0202009 Source: SSIS_reports_customer_2015 Connection manager "<IP.DBName.Useraccount>" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user '<Useraccount>'.". End Error Error: 2015-07-17 11:31:04.28 Code: 0x... The package execution fa... The step failed.
I have changed the bits between the <> to keep things more private here.
It sounds like the sp change has had a knock on effect to passwords somehow, but the job itself, other than the called SP was unchanged?
Assuming a password might need retyping, where do I go to achieve that? OR what else is the problem?
Help appreciated.
July 17, 2015 at 4:59 am
This suggests a change to the package has been made.
It suggests that the package protection level (see here) has been set to encrypt sensitive with user key.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 5:00 am
--removed
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 5:29 am
So how would I go about changing it (or correcting it or removing the requirement)) ?
July 17, 2015 at 8:17 am
So opened the package and changed the setting (having finally found it) to use EncryptSensitiveWithPassword. The package executes ok now through the Execute Package Utility, but pending confirm as to whether it runs through the SQL Agent Job.
Seems a bit of a effort just because of an update to the stored procedure in SSMS to tweek a statement a bit.
Point being not changed the ssis process as such, just the coding of the sp it executes?
Bit like having to redecorate the house just because you moved a chair a bit to the left!!
July 17, 2015 at 9:09 am
Chelski_blue (7/17/2015)
So opened the package and changed the setting (having finally found it) to use EncryptSensitiveWithPassword. The package executes ok now through the Execute Package Utility, but pending confirm as to whether it runs through the SQL Agent Job.Seems a bit of a effort just because of an update to the stored procedure in SSMS to tweek a statement a bit.
Point being not changed the ssis process as such, just the coding of the sp it executes?
Bit like having to redecorate the house just because you moved a chair a bit to the left!!
As I implied earlier, your understanding is the same as mine: changing a proc slightly (but keeping the same contract) should not require any change whatsoever to the package. Something else is going on here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply