SSIS 2008 R2 - Failed Login but correct credentails

Question

SSIS 2008 R2 - Failed Login but correct credentails

taybre

SSC Eights!

Points: 916
More actions
August 17, 2015 at 5:34 am

#303484

Hi SSC,
I've searched for a solution and haven't found anything on this forum relating so new topic.
Background:
- SSIS package runs successfully in BIDS with login credentials.
- Protection lvl of package = "DontsaveSensitive"
- Import package through SSMS
- Create job and specify said package
Problem:
- Run job, fails, "Login failed for user or you might not have permission"
I know the credentials are correct as I can login in.
The user has "sysadmin", "dbcreator" and "public" roles
On related db, user has "db_accessadmin", "db_datareader", "db_datawriter" and "db_owner" roles
No schmemas owned by user.
If I create a job with a simple query, it works.
What I've tried:
Created credentials that are mapped to my AD account (admin rights) on the server
Created proxy for user that is mapped said credentials
Specified job to execute package using proxy
When I execute job, stills fails with same error.
Job history message:
Executed as user: [AD ACCOUNT]. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:50:09 Error: 2015-08-17 11:50:09.94 Code: 0xC0202009 Source: msc import Connection manager "[connection in BIDS]" 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 '[sql login]'.". End Error Error: 2015-08-17 11:50:09.94 Code: 0xC00291EC Source: Create Staging Execute SQL Task Description: Failed to acquire connection "[connection in BIDS]". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_
Any help would be much appreciated
taybre
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look in the SQL error log (the instance you're trying to connect to). What's the full login failure message from the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

taybre SSC Eights! Points: 916 More actions · Answer 2

GilaMonster (8/17/2015)
Look in the SQL error log (the instance you're trying to connect to). What's the full login failure message from the error log?

I've just checked the error log and it says:

"Log in failed for user [xxx] Reason: Password did not match the log in provided [CLIENT: <local machine>]"

This confuses me as I can connect to the database using the user login details and when I run the package in BIDS it work's 100%

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 3

taybre (8/17/2015)
GilaMonster (8/17/2015)
Look in the SQL error log (the instance you're trying to connect to). What's the full login failure message from the error log?
I've just checked the error log and it says:
"Log in failed for user [xxx] Reason: Password did not match the log in provided [CLIENT: <local machine>]"
This confuses me as I can connect to the database using the user login details and when I run the package in BIDS it work's 100%

i belive the issue is this:

Executed as user: [AD ACCOUNT].

when you run the package yourself, the sql user and password were encrypted for your specific login (ie mydomain\taybre)

when it is run as a different user, the password cannot be decrypted, and passes an empty string(i think) for the password) resulting in password fail.

if possible, i would simply remove the sql username and password, and use windows credentials.

if that is not possible, either make the person who compiled the SSIS package the owner of the job that runs the package, or open the package as that specific AD account, re-enter the password,and save it again, so that AD account can decrypt the password.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

taybre SSC Eights! Points: 916 More actions · Answer 4

Lowell (8/17/2015)
taybre (8/17/2015)
GilaMonster (8/17/2015)
Look in the SQL error log (the instance you're trying to connect to). What's the full login failure message from the error log?
I've just checked the error log and it says:
"Log in failed for user [xxx] Reason: Password did not match the log in provided [CLIENT: <local machine>]"
This confuses me as I can connect to the database using the user login details and when I run the package in BIDS it work's 100%
i belive the issue is this:
Executed as user: [AD ACCOUNT].
when you run the package yourself, the sql user and password were encrypted for your specific login (ie mydomain\taybre)
when it is run as a different user, the password cannot be decrypted, and passes an empty string(i think) for the password) resulting in password fail.
if possible, i would simply remove the sql username and password, and use windows credentials.
if that is not possible, either make the person who compiled the SSIS package the owner of the job that runs the package, or open the package as that specific AD account, re-enter the password,and save it again, so that AD account can decrypt the password.

I will try running the package with the AD account to see if that works. Ideally I need a specific sql login to run the package (for security reasons and company policy)

Will post results.

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

taybre SSC Eights! Points: 916 More actions · Answer 5

I changed everything to run on the AD.

The connection in the SSIS package = Windows Auth

Job = Windows Auth

Run as = proxy (using credential which is set to Windows Auth)

How do I set the "run as" option in the job step to a SQL login without creating a proxy. The only thing in the drop down menu is "SQL Server Agent Service Account" and "myProxy"

If I select "SQL Server Agent Service Account", job fails and in the history it says "executed as [domain\servername]...."

Any suggestions?

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 6

i believe if you set the owner of the job as your AD account, it would work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

taybre SSC Eights! Points: 916 More actions · Answer 7

Lowell (8/17/2015)
i believe if you set the owner of the job as your AD account, it would work.

I tried this, both as the SQL login and as the AD account. Both give same error.

I am now seeing if I can execute the package using xp_cmdshell - having to grant execute on xp_cmdshell to SQL login (for now)

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

taybre SSC Eights! Points: 916 More actions · Answer 8

SOLVED:

Although I couldn't get the job to successfully execute the SSIS package, I did get the job to execute a TSQL statement containing xp_cmdshell.

I had to grant execute on xp_cmdshell for my SQL login.

I also had to add a proxy for xp_cmdshell through sp_xp_cmdshell_proxy_account using my AD account.

At the moment this works but this solution does not meet my security concerns and I will be creating an AD login specifically with execute on xp_cmdshell in the future.

Thank you for your help everyone.

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/