SSB exceptions list in cross-server communication
Exception List & how to figure it out
1. An error occurred while receiving data: '10054(error not found)’
It displays in the transmission_status of sys.transmission_queue
It might dut to lack of CONNECT permission on the EndPoint of other SQL server instance. You need to run the SQL profile and monitor the ‘Security Audit/Aduit Broker Login’ and ‘Broker/Broker connection’ events.
Demo SQL SCRIPT:
use master
go
-- cREATE A NEW LOGIN & USER in the MASTER system database,
-- that will be used for remote connections from the other SQL server instance
Create Login SSB_Login
with password='YOUR PWD'
GO
Create User SSB_User
for login SSB_Login
GO
-- SQL Server instance's login is granted CONNECT permissions to the associated endpoint
Grant Connect on EndPoint::SSB_REMOTESERVER_EndPoint to SSB_Login
GO
2. An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
The above exception info is retrieved from the SQL Profiler or Log File Viewer.
This may occur when the database owner name (stored in the database) is not a valid login in the instance of SQL Server the database is being attached or restored to.
Use the following Transact-SQL expression to change the database owner to a valid login in the instance of SQL Server.
ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login