In my last post, I described how you can fix the issue regarding:
Executed as user: BIZDEMO\saspereira. Could not find server ‘BIZDEMO’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed.).
See more about this error here: https://blog.sandro-pereira.com/2018/05/08/backup-biztalk-server-job-failed-could-not-find-server-in-sys-servers/
And I told that this was not the only issue that you will find. The truth is that if you try to execute the Backup BizTalk Server job after you fix this last problem, the job will fail again, this time with the following error:
Executed as user: NT SERVICE\SQLSERVERAGENT. Could not connect to server ‘BIZDEMO’ because ” is not defined as a remote login at the server. Verify that you have specified the correct login name. . [SQLSTATE 42000] (Error 18483). The step failed.
📝 One-Minute Brief
A troubleshooting guide that explains why the Backup BizTalk Server job fails with the error “could not connect to server because it is not defined as a remote login” and how to fix the issue by correcting SQL Server linked server and login configurations.
Cause
This error can be caused by several problems, and a common solution you will find in SQL Server forums and posts is to drop and recreate the linked server.
However, and forgive me in advance for my SQL ignorance, I don’t have any linked Server configured. My BizTalk Server Virtual Machine is a simple standalone machine with BizTalk and SQL installed… so it couldn’t be that problem!
So, after several tests, I started to think as a truly genuine old-school technical guy:
- It doesn’t work? Did you try to restart it?
Or as a true BizTalk developer guy:
- Did you restart the host after deploying your solution?
And I thought it might be necessary to restart the SQL services so that all the settings from the previous command execution take effect – the commands sp_dropserver and sp_addserver, which are necessary to fix the issue reported in the previous post.
And it was!
Solution
To fix this issue, we need to restart the SQL services.
After you restart the SQL Server Services, you will be able to run the Backup BizTalk Server job successfully.
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
