Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

User with Sysadmin rights fails login attempt (SSIS) Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 5,576, Visits: 6,360
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1567020
Posted Friday, May 2, 2014 7:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 4,323, Visits: 10,572
Anything useful in the remote server's ERRORLOG?
Are you able to log on interactively (SSMS, SQLCMD...)?


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1567029
Posted Friday, May 2, 2014 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 5,576, Visits: 6,360
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1567037
Posted Friday, May 2, 2014 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 5,576, Visits: 6,360
Oh, my. I think I found the problem. Will post in a moment if I'm right.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1567048
Posted Friday, May 2, 2014 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 1,660, Visits: 4,753
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
Post #1567061
Posted Friday, May 2, 2014 9:05 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 5,576, Visits: 6,360
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1567076
Posted Friday, May 2, 2014 9:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 1,660, Visits: 4,753
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.
Post #1567083
Posted Friday, May 2, 2014 10:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 5,576, Visits: 6,360
Ditto to that.

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


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1567120
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse