SSB exceptions list in cross-server communication

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