June 28, 2012 at 8:45 am
Experts,
I have two different sql servers in same domain.
We can't change the authentication mode of both the SQL servers. It's WINDOWS AUTHENTICATION mode only. Cant make it to mixed mode, we are not supposed to.
A linked server is created from first one to the second one. I created a job which pulls data from linked server and put it in the first server. works fine from management studio but fails when I schedule as job.
"Executed as user: 'service accnt name'. Login failed for user 'service accnt name'. [SQLSTATE 28000] (Error 18456). The step failed."
We don't know the password of service account under sql server agent is running.
But we have a login with sysadmin right. I tried to map the service account with this, it allowed me to save and it runs as the mapped login also. but fails with following error.
"The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452). The step failed."
Can anyone help please...... Its very urgent.
Thanks in advance.
Smith.
June 29, 2012 at 1:18 am
.. Not even a single response... !!!!
Has anyone come across this issue ever ?
Thanks
Smith.
June 29, 2012 at 2:45 am
Ensure that SPN's are configured on both servers as it could be a Kerberos double hop authentication issue.
July 23, 2019 at 5:41 pm
Hi Smith, I have the same issue as this. Were you able to fix this and how?
July 23, 2019 at 6:31 pm
Hi Smith, I have the same issue as this. Were you able to fix this and how?
There are two different errors in the post - not sure if you are experiencing both. For login failures, check the SQL Server error log and check the state code associated with the error. You can find details for the different state codes in the following article:
https://sqlblog.org/2011/01/14/troubleshooting-error-18456
Sue
July 23, 2019 at 7:03 pm
This kind of issue is one reason I avoid using linked servers for ETL type processes.
If you can use Integration Services (SSIS) that would be a much easier implementation than using a linked server - and could also be configured to avoid issues with a growing transaction log - and improve overall performance of the load process.
If you don't have access to Integration Services (SSIS) - you could look at scripting a process that runs BCP to extract and load the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2019 at 8:02 pm
I forgot to mention. This is happening after I backup the database from 2005 and restores back to 2016 and scripts out the SQL jobs from 2005 and bring over....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply