question

LindaDoan-6846 avatar image
0 Votes"
LindaDoan-6846 asked LindaDoan-6846 commented

SSIS Package in SSDT 2017 fails in SQL Server Job Agent with Proxy Account 'Login Failed for user xxxx....'

Dear Support,

I have spent the past few months working on this and I've tried everything that is recommended from internet resources and I am still unable to get the following issue resolved.

I need to schedule an SSIS package in Visual Studio (SSDT) 2017 to run and here's what I've done after months of working on this:

1) In SSMS, created an extract using the Data Export Wizard and saved the package to the File System on the local drive
2) In Logins, created login and granted the login sysadmin and public server role, granted db_owner database role, granted SqlAgentRole, SqlagentOperatorRole,SqlagentUserRole to this account
3) Created server credential for this login
4) Created a proxy account for this login
5) Created a server agent job with the owner being my login account (admin)
6) Created the step for this job > setup the type for SQL server integration services package > Run as the login proxy account > package source = file system > for execution options = 32-bit runtime checked
7) in SSDT created a project > open and added the SSIS package > change the Runtime64bit to False > encryption is set to user key

Tested the package in Visual Studio = Works fine > No errors

When execute the job at job step, it errors with:

".... Connection manager "SourceConnectionOLEDB"..... "Microsoft SQL Server Native Client 11.0".... "Login failed for user 'xxxxxxx" SSIS error Code DTS_E_CANNOTCQUIRECONNECTIONFROMCONNECTIONMANAGER..

I've deleted and recreated the account as well as the job many times and redo the SSIS packages without any luck.

Please assist as I am now against tight deadline and need to get this resolved ASAP. Thank you

sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi All,

I have solved the issue! For the majority of the users out there, your servers are on a network. I've finally found out that my server environment is different, it's 3rd party hosted and my two servers are not on a network or domain, therefore, the account that I was using was local to just that server.

Solution > RDP into the SQL Server with the account > in SSMS create the package using the Data export wizard and set it for SQL Authentication > in SSDT 2017 > create the project/solution to use the ssis package > change the 64bitruntime to false and sql server environment from 2019 to 2016 > in SSMS create the job using the same account as the owner and the proxy account which is the same account and WALA! successful job

To summarize, the account use for this type of environment has to be the same everywhere on the server.... two months of hair pulling is now over....

Thanks to all who provided suggestions...

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered LindaDoan-6846 commented

Hi @LindaDoan-6846,

Have you tried below resolution? If not you may have a try.

  1. Change all the connection managers in the package to use windows authentication.

Note: This is not an option while communicating with third party datasources which does not support windows authentication like Oracle.

  1. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.

  2. Create a configuration file to provide the connection information during Package runtime.

More details please refer: ssis-error-code-dts_e_oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Zoe,

Thanks for the information but I've tried everything. Just to confirm, I deleted the package from the server and re-create a new package using the data export wizard. Then created a new Visual Studio 2017 project solutions with the package information. I'm logged in as the Administrator and also use Windows Authentication.

I created a sql server agent scheduled job and the owner is the Administrator account. In my job step, it is running as the proxy account which has all the credentials I've granted it. When I run this job, it errors with the 'Login failed for user xxxxx'...

In Visual Studio, when I execute the package, it works just fine.

I did every possible combinations and it is just not working. What else am I missing for this error and this account? Thanks

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered LindaDoan-6846 commented

'Login failed for user xxxxx'

So you use a SQL login, not Windows credentials? I wonder a bit, because you always wrote you use your account? Is the SQL Server running in mixed mode to allow SQL logins, at all?


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi OlafHelper,

Thanks for your feedback. I've spent over a couple of months and unsuccessful

I RDP onto the server with my account (Administrator)
I logged onto SSMS using my account (Administrator)
I created the SSIS package from the Data Export Wizard using my account (Administrator)
I created a SQL login for an account (for example: abc) and grant this account (abc) all the sql rights, roles it needs as recommended from all forums. I tested this account by logging onto SSMS with a password and it works fine.
I created a credential for this abc account and add it as the principal
I created a proxy account and added this abc account
I created a Visual Studio 2017 project solution and added this SSIS package onto this project. I tried no encryption as well as with encryption and this works fine when i executed it from SSDT.
I created a sql agent job with the owner being my account (Administrator) and assigned the job step to use the proxy account.

0 Votes 0 ·