Hello experts,
Could someone point me to some ideas about troubleshooting this error that I am getting when trying to run a SQL Agent job that executes an SSIS package? I have tried one recommendation that I saw about using SQL proxies and credentials and it didn't resolve the error but I am not sure I am setting up the permissions correctly. This setup works on the existing db server so I am not sure what is different on the new server to trigger the error.
I can provide further details as needed, but the basic facts are that I scripted the job on one server and then tried to change the relevant configuration details on the new db server. I made the server and package names generic since I am posting to the internet.
Also, this db server is one of the first that has been put behind our web application firewall so I am not sure if that is causing the issue.
Thanks for any help!
- webrunner
Started: 6:09:44 PM
Failed to execute IS server package because of error 0x80131904. Server: DBSERVER, Package path: \SSISDB\Project\Project_Export\Package.dtsx, Environment reference Id: NULL.
Description: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Source: .Net SqlClient Data Provider
Started: 6:09:44 PM
Finished: 6:09:44 PM
Elapsed: 0.266 seconds
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 20, 2017 at 9:26 am
Have you reviewed the SSIS catalog deployment?
Are the Environment variables setup and mapped correctly to the deployed SSIS project? Do they match the server that's properly working?
Can you run the package on the server via DTEXECGUI or BIDS?
I would troubleshoot by trying to isolate where the security issue starts to fail.
1. Executing package manually via DTEXECGUI, DTEXEC, or BIDS
2. Execute via SQL agent via DTEXEC
3. Execute manually via SSIS catalog
4. Execute via SQL agent via SSIS catalog
June 13, 2017 at 2:34 pm
caffeinated - Saturday, May 20, 2017 9:26 AMHave you reviewed the SSIS catalog deployment?
Are the Environment variables setup and mapped correctly to the deployed SSIS project? Do they match the server that's properly working?Can you run the package on the server via DTEXECGUI or BIDS?
I would troubleshoot by trying to isolate where the security issue starts to fail.
1. Executing package manually via DTEXECGUI, DTEXEC, or BIDS
2. Execute via SQL agent via DTEXEC
3. Execute manually via SSIS catalog
4. Execute via SQL agent via SSIS catalog
Thanks for your help. Sorry I took a while to test the steps you recommended.
The SSIS project worked when run using steps 1 and 3 but failed using step 4 (through the Agent job). There seems to something in the way I have (mis-)configured the Agent job that keeps throwing the error 'Failed to execute IS server package because of error 0x80131904. Server: _______, Package path: \SSISDB\____\_______\Package.dtsx, Environment reference Id: NULL.
Description: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.'
I even tried logging in to the SQL Server as the service account it says is executing the job. I can run the SSIS package successfully from the Intergration Services Catalog, but not with the Agent job.
I am not sure how to run step 2 'Execute via SQL agent via DTEXEC' - do you mean create a job that runs the DTEXEC utility via a command line command?
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
I had found that my Job was executing against a DNS name and not the SQL Server Name. So Check your SQL server name : select @@servername and make sure you use this name in referring to the SQL server where the catalog is located and not some or other DNS name. I have found that it will also succeed using the DNS name to call the packaged from a different SQL server, but if the job runs on the local SQL server calling a package on the same SQL server then the server name must refer to the value in @@Servername.
July 6, 2020 at 3:11 pm
Thanks Stephan,
The error I saw turned out to be due to a communication issue between the SQL Server and the domain controller. So it wasn't really a SQL issue. But I will keep the info you shared for future reference.
Thanks again.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 29, 2023 at 3:08 am
Can you post your full solution? what steps you performed to make communication issue resolved betweeen sql server and domain controller.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply