0

A trigger has stopped working since we have changed our SQL Server to a new server. Trigger is now being corrected to point to the new location of the table on the new server. When updating fields on Database A on SQL Server A this trigger grabs some attributes and INSERT them into Database B on SQL Server B. After each attempt the application throws the error as attached. I appreciate any help. enter image description here enter image description here enter image description here

  • 1
    I suggest you add the CREATE TRIGGER statememt that is responsible for the error. Please also copy the error message in text format. – ypercubeᵀᴹ Jan 24 at 18:19
  • Seems like a privileges/access error. – ypercubeᵀᴹ Jan 24 at 18:20
  • Have you tried a simple insert from server a to server b? – McNets Jan 24 at 18:26
  • 1
    @NicoM. I think he means, right clicking the trigger in SSMS --> Create trigger to new query window --> Copy the trigger statement and adding it to your question. Generally, as much relevant information as possible is best. – Randi Vertongen Jan 24 at 18:39
  • 1
    Please don't add more images to your question, copy & paste as text. What provider are you using for your linked server? Have you updated OLEDB providers too? – McNets Jan 24 at 19:03
0

How are you testing the linked server? From SSMS on Server A? If so, that may not be fully testing the problem. You could be hitting a kerberos auth issue from your client application.

Open SSMS somewhere other than Server A or B, then connect to Server A and test your linked server again. If it fails with a login failure for NT AUTHORITY\ANONYMOUS LOGON then you're hitting a Kerberos Auth issue and need to fix your SPNs on Server A and Server B see this link for an MS tool to check and fix your SPNs.

Also, when you moved the database the owner may have changed, that could be causing problems as well. Check the database owner is a valid user mapped to a login on the SQL Server and if not map it to sa or another valid user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.