May 2, 2014 at 7:12 am
I have a monthly package that runs from a web server and accesses a database server. The account that runs the package is a member of the sysadmin role on the database server. For several months (including last month), this package has run without issue (successfully and everything). This month, we are suddenly having login issues.
This is the job log error message:
Error: 2014-05-02 08:20:53.74
Code: 0xC0202009
Source: MyCM Connection manager "LinkedServer.MyDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login failed for user 'MyDomain\SysadminUser'.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot open database "MyDB" requested by the login. The login failed.".
End Error
Error: 2014-05-02 08:20:53.74
Code: 0xC020801C
Source: SSISTask MyDB SSISTask [176]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "LinkedServer.MyDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
I have verified the permissions, verified that the config file is pointing to the right server and am still confused. Why would a sysadmin user have problems logging into a database? I added (just for kicks and giggles) datareader and datawriter database permissions in the User Mapping screen, but it hasn't helped.
Any thoughts as to what could be the issue?
May 2, 2014 at 7:35 am
Anything useful in the remote server's ERRORLOG?
Are you able to log on interactively (SSMS, SQLCMD...)?
-- Gianluca Sartori
May 2, 2014 at 7:54 am
I remoted into the app server (not web server, I keep saying that wrong) with the login in question. I logged into SSMS using windows authentication and opened up the database server. I browsed through object explorer on the databases and tables in question no issue. I opened up a few tables with no problem.
The SQL error log's ever so helpful "error" from around the same time of the error (this happened about a minute earlier than the package failure, though) is "Error: 18456, Severity: 14, State: 11".
The SQL Agent log has nothing from around that time.
I need a wall for which to pound my head against.
May 2, 2014 at 8:15 am
Oh, my. I think I found the problem. Will post in a moment if I'm right.
May 2, 2014 at 8:35 am
Brandie Tarvin (5/2/2014)
I remoted into the app server (not web server, I keep saying that wrong) with the login in question. I logged into SSMS using windows authentication and opened up the database server. I browsed through object explorer on the databases and tables in question no issue. I opened up a few tables with no problem.The SQL error log's ever so helpful "error" from around the same time of the error (this happened about a minute earlier than the package failure, though) is "Error: 18456, Severity: 14, State: 11".
The SQL Agent log has nothing from around that time.
I need a wall for which to pound my head against.
Error: 18456, Severity: 14, State: 11
State 11 corresponds to “Valid login but server access failure” which again points to the same fact that the login is valid but is missing certain security privileges which would grant it access to the instance.
http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 2, 2014 at 9:05 am
After several attempts of resolving the "security issue," I pulled the package and config file down to my desktop. When I loaded the package into my solution, BIDS complained about being unable to read or load the config file and warned that it might be corrupted. I couldn't see anything wrong with the file, but on a hunch, I regenerated it from scratch.
And now everything works without a problem.
The problem was in-package connection managers pointing to a defunct server that used to be overridden by the config file until it somehow got corrupted. Go figure.
Thanks for your attempt at helping, though. I appreciate it.
May 2, 2014 at 9:27 am
Yes, I've seen that one before, SSIS simply chooses to ignore a dtsConfig file containing a malformed tag. There was no error regarding the config file, it just reverted back to using the default design time settings and continued running, which can be dangerous. I'd rather the package just fail upfront and then tell me why in the error log.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 2, 2014 at 10:51 am
Ditto to that.
Some days I just LURV SQL. If you know what I mean. @=)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply