Click on the banner to learn about and purchase my database training on Azure

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

Views: 1.807 views
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:


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:


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:

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:

Now let's access the “dirceuresende” database through EXECUTE AS:

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

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:

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

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.


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!