September 23, 2014 at 7:58 am
I have a main package that runs severals packages each loading a table.
Each package has a connection in connection manager to connect to that database/table.
Userid is my userid using SQL Server Authentication with the password saved in the connection.
I can run the main package in Visio Studion with runs all the packages and loads the tables with no problem.
The packages have been deployed to the Server.
I set up a job to run under SQL Server Agent. I can set up the job .. and try to execute the job, but it gets an error:
Date12/5/2013 9:23:21 AM
LogJob History (Quik_Load)
Step ID1
ServerDHR99AMYDHRDBVM\OPIINSTANCE
Job NameQuik_Load
Step NameLOAD
Duration00:03:32
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: DHR99AMYDHRDBVM\SYSTEM. ...0.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:23:21 AM Error: 2013-12-05 09:23:21.14 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: 2013-12-05 09:23:21.14 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: 2013-12-05 09:23:21.34 Code: 0xC0202009 Source: LOAD Connection manager "DHR99AMYDBVM.CsQuikPr" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'wste199e'.". End Error Error: 2013-12-05 09:23:21.34 Code: 0xC0202009 Source: LOAD Connection manager "DHR99AMYDBVM.CsQuikPr" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'wste199e'.". End Error Error: 2013-12-05 09:23:21.34 Code: 0xC0202009 Source: LOAD Connection manager "DHR99AMYDBVM.CsQuikPr" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'wste199e'.". End Error Error: 2013-12-05 09:23:21.36 Code: 0xC0202009 Source: LOAD Connection manager "DHR99AMYDBVM.CsQuikPr" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'wste199e'.". End Error Error: 2013-12-05 09:23:42.42 Code: 0xC0202009 Source: LOAD Connection manager "DHR99AMYDBVM.CsQuikPr" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'wste199e'.". End Error Error: 2013-12-05 09:23:42.42 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "DHR99AMYDBVM.CsQuikPr". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2013-12-05 09:23:42.42 Code: 0xC002F304 Source: OnError Send Mail Load Tables Send Mail Task Description: An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.131.89.54:25". End Error Error: 2013-12-05 09:23:42.43 Code: 0xC0202009 Source: LOAD Connection manager "DHR99AMYDBVM.CsQuikPr" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login fail... The package execution fa... The step failed.
September 23, 2014 at 9:24 am
wayne.stevens (9/23/2014)
"Login failed for user 'wste199e'.".
Ensure Login wste199e has desired access to perform the tasks in the package.
OR
Modify the package to use windows authentication in connection manager, and at the properties of the SSIS project, under security -> Protection Level -> chose do not save any sensitive data.
Then deploy package to server, When you schedule the Package from agent job, it will now run with sql agent service account of the sql server where its running from .
You now need to ensure desired permission to sql agent service account and obviously agent service account should have permission.
September 23, 2014 at 11:24 am
Thanks for the help .. what permissions do I need to make sure that is set for the SQL AGENT SERVICE account.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply