This post was originally published here
In my last post, I described how can you fix the issue regarding:
Executed as user: BIZDEMOsaspereira. 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 SERVICESQLSERVERAGENT. 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.
This error can be related to several possible problems and a common solution you will find in SQL Server forums and post is that you should Drop and re-create the linked server will resolved this problem.
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 your solution deployment?
And I thought that it might be necessary to restart the SQL services so that all the settings of the previous command execution have the correct effect – execution of the commands: sp_dropserver and sp_addserver necessary to fix the issue reported in the previous post.
And it was!
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.