User with Sysadmin rights fails login attempt (SSIS)

  • 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?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Anything useful in the remote server's ERRORLOG?

    Are you able to log on interactively (SSMS, SQLCMD...)?

    -- Gianluca Sartori

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Oh, my. I think I found the problem. Will post in a moment if I'm right.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • Ditto to that.

    Some days I just LURV SQL. If you know what I mean. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply