SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User with Sysadmin rights fails login attempt (SSIS)


User with Sysadmin rights fails login attempt (SSIS)

Author
Message
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
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/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.
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10110 Visits: 13351
Anything useful in the remote server's ERRORLOG?
Are you able to log on interactively (SSMS, SQLCMD...)?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
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/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
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
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/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.
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12594 Visits: 10693
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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
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/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.
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12594 Visits: 10693
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15100 Visits: 9006
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/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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search