SQL Server Service Broker Issue – Set TRUSTWORTHY On

Securityservice-brokersql serversql-server-2012

I've got a database running on a SQL-Server 2012 DBMS. Consider that the server running SQL Server is not part of the network domain.
When I execute this sql command

ALTER DATABASE myDatabase SET TRUSTWORTHY ON;

a service broker queue (early setted and working) receives messages but the activeted stored procedure associated to the queue doesn't start. Messages continue to flow in to the queue, but nothing else happens.
In the SQL SERVER log I've found this message

The activated proc 'proofSchema.mySP' running on queue 'proofSCHEMA.myQueue' output the following: 'The database owner SID recorded in the master database differs from the database owner SID recorded in database 'myDatabase'. You should correct this situation by resetting the owner of database 'myDatabase' using the ALTER AUTHORIZATION statement.'

If I run this same configuration on a machine inside a network domain, everything works.

I can't understand what is happening and why trustworthy crush with servi broker queue.

Best Answer

Within each database there exists a dbo User. This User (Database-level) always exists, but the SID (Security IDentifier) that it maps to is not always the same; it will map to whatever Login (Instance-level) is specified either when creating the Database of when altered to have a new "Database Owner". The dbo User is one of the entries in sys.database_principals.

When initially setting the Database Owner during CREATE DATABASE, or when changing it later, the SID of the "owner" is not only placed into sys.database_principals, but is also recorded in master.sys.databases. If the Database never leaves the Instance in which it was created, there should never be a mismatch between the SID values in sys.database_principals and master.sys.databases. But, if the Database is ever restored or attached to (or from) another Instance, then it is possible that the SID values won't match. You can check the values in both places using the following queries:

USE [tempdb]; -- Change to whatever DB you want to check

SELECT      msd.owner_sid,
            msp.[name]
FROM        [master].[sys].[databases] msd
INNER JOIN  [master].[sys].[server_principals] msp
        ON  msp.[sid] = msd.[owner_sid]
WHERE       msd.[database_id] = DB_ID();

SELECT sdp.[sid]
FROM   [sys].[database_principals] sdp
WHERE  sdp.[name] = N'dbo';

Now, by default, TRUSTWORTHY is set to OFF and permissions on operations involving Impersonation (i.e. EXECUTE AS) are confined to the database where the operation was executed from. While impersonating a DB User, attempting to access another DB (or even Server/Instance-level resources, I believe), SQL Server will assume that the current DB User's SID (i.e. the one being impersonated) has a matching Login so it can take on those permissions. This is blocked when TRUSTWORTHY is OFF, but setting it to ON lifts the Database-level quarantine and allows Impersonation to extend beyond the initial Database. The SID of the dbo User might certainly exist as a Login, but if it is not the same SID that is mapped as the database owner in sys.databases, then that is a clear indication that something is not right (and most likely that that DB came from another Instance), and that there might possibly be malicious intent in the requested operation.

Related Question