Linked Server

Question

Linked Server

Janda Nel

SSC Eights!

Points: 882
More actions
August 3, 2017 at 1:25 am

#323039

Good morning
I Have a job on my server which write data to a linked server.It is scheduled to run every day.It worked for about a year.Now when the job runs,i get the error message:
Message
Executed as user:xxxx. SQL Server Network Interfaces: The logon attempt failed [SQLSTATE 42000] (Error 780) OLE DB provider "SQLNCLI11" for linked server "xxxx" returned message "Cannot generate SSPI context". [SQLSTATE 01000] (Error 7412). The step failed.
When I run the step manually from within the studio manager ,it works .
We have not done any changes on any of the two servers.
I also checked the service account which runs the job,have sysadmin rights,and read and write access to the destination database

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Janda Nel - Thursday, August 3, 2017 1:25 AM
Good morning
I Have a job on my server which write data to a linked server.It is scheduled to run every day.It worked for about a year.Now when the job runs,i get the error message:
Message
Executed as user:xxxx. SQL Server Network Interfaces: The logon attempt failed [SQLSTATE 42000] (Error 780) OLE DB provider "SQLNCLI11" for linked server "xxxx" returned message "Cannot generate SSPI context". [SQLSTATE 01000] (Error 7412). The step failed.
When I run the step manually from within the studio manager ,it works .
We have not done any changes on any of the two servers.
I also checked the service account which runs the job,have sysadmin rights,and read and write access to the destination database

What are the security settings for the linked server itself?

Sue

Lowell SSC Guru Points: 323518 More actions · Answer 2

If there were no known changes, I would assume that the Primary Domain Controller was not available for a moment, and just rerun the job.
if it fails again, that's where i would start looking at the specifics: was the service account that runs SQL changed?
if ti did not change, was the service recently restarted? look in the SQL Log, and confirm the Service Principal Names were generated correctly at start up; is there something that looks like this?

The SQL Server Network Interface library could not register the Service Principal Name (SPN)
[ MSSQLSvc/MyServerName.MyDomain.org ] for the SQL Server service. Windows return code: 0xffffffff,
state: 43. Failure to register a SPN might cause integrated authentication to use NTLM instead of
Kerberos. This is an informational message. Further action is only required if Kerberos authentication
is required by authentication policies and if the SPN has not been manually registered.

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!

Janda Nel SSC Eights! Points: 882 More actions · Answer 3

the security is "be made using the login's current security context"

i will check the sql logs for an error message something like you sayid.Because the jobs is still failing,almost for a week now.The service accounts was also not changed,i checked.
The linked server was last restarted at 8 June.Maybe we should restart it for a change.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

You may want to check the SPNs for the server with the linked server. An easy way to check is using the Kerberos Configuration Manager - you can download it from here:
Kerberos Configuration Manager for SQL Server

Sue

Arsh SSCertifiable Points: 6354 More actions · Answer 5

I would suggest to see if the password has been changed .And also the SPNs , which generally are causes if created from other server login script.

Arshad

Janda Nel SSC Eights! Points: 882 More actions · Answer 6

thanks everyone,We found the problem.The service account which starts the service,password expired.The account was created wrongly to expire.Restarted service and everything is working again.