The TRUSTWORHY bit database property in SQL Server 2005

In SQL Server 2005 we introduced a new database property named TRUSTWORTHY bit (TW bit for short) at the database level in order to work as a safeguard to reduce the default surface area regarding some powerful new features: EXECUTE AS USER and CLR assemblies. These new features are really powerful, but without the TW bit they would be ultimately under the control of the DBO and can potentially be misused to escalate privileges in the system.

When the TW bit is OFF
· EXECUTE AS USER:

· If the TW bit is OFF, EXECUTE AS USER will be bound to the DB and the server-scoped permissions of the impersonated user and roles will be used as deny-only. Access to other DBs will be limited to access as guest.

· CLR:

· Only assemblies marked as SAFE can be created and used.

But when the TW bit is ON, it doesn't blindly change the behavior of these features, instead it allows the system to verify permissions on the DBO (based on the SID) on the server or on another database to allow extended functionality.

When the TW bit is ON
· EXECUTE AS USER:

· DBO role: authenticator. The DBO will be part of the token as an authenticator; this means that the DBO will “vouch” for the impersonated token, and it is up to the server or another DB to trust this DBO.

· For cross-DB access, the DBO requires AUTHENTICATE permission. The impersonated context will be valid for the target DB, but still sandboxed from the server

· For access across the server, the DBO requires AUTHENTICATE SERVER permission. The impersonated context will be valid across the server (and all the DBs). *Read security Note*

· CLR :

· DBO role: establish the assembly types allowed in the DB

· For using assemblies marked as EXTERNAL_ACCESS in the DB, the DBO requires EXTERNAL_ACCESS permission. *Read security Note*

· For using assemblies marked as UNSAFE in the DB, the DBO requires UNSAFE permission. *Read security Note*


When the DBO is a low privileged principal in the system, and the DBO has good control on the DB (i.e. All the privileged principals in the DB are indeed trusted individuals), the TW bit works as expected and it can be used to establish a trusted environment with the features I just mentioned.

Security Note: The set of functionality marked with the *Read Security Note* text are indeed very powerful and can be easily misused if not taken into account. These features assume that the DBO (and privileged principals in the DB) are indeed fully trusted as they could in essence escalate their own privileges to sysadmin.

Limitations of the TW bit
The main limitation of this mechanism is the level of granularity: The whole DB is marked as TW or not, and it is not possible to distinguish between scenarios within the same DB. This presents a problem to the DBAs: either you trust the DBO (and everyone the DBO has entrusted with elevated permissions on the DB) or not, there is no middle ground.


Also notice the overloaded significance of the TW bit. It may be possible that you only want to use one of the features it is gating, for example, the creation of EXTERNAL ACCESS assemblies, but because of its boolean nature, it would also allow the rest of the features. At that point the permissions granted to the DBO are the ones that will truly enable/disable any of these features.


The main risk: TW bit ON & DBO is sysadmin
The risk with using the TW bit arises not from enabling it, but in conjunction to the nature of DBO. In practice, the first condition I mentioned (DBO is a low privileged user) is rarely true, and under these circumstances (DBO is a sysadmin & the TW bit enabled) all of the features I mentioned on the TW ON section are immediately enabled on the DB, even if the DBA only desired to use one of them.

If the desired behavior is indeed to enable all these features on the DB, and the privileged users on the DB are indeed trusted, there is no need to worry as the system will behave as expected, but it would be a good idea to keep such DBs closely monitored. Notice that in SQL Server 2005, out-of-the-box we already have a scenario with the TW bit ON & sysadmin: msdb. MSDB is considered a system DB with various system modules, and the assumption is that DBAs are already protecting msdb as they would protect other system DBs such as master DB.

Unfortunately the case above is rarely the desired behavior, and based cases we have observed, we detected two different kinds of mistakes when enabling the WT bit:

· Something breaks: The application works perfectly during development/testing, but once it is deployed on a tightly controlled environment the application breaks.

· Nothing breaks: The application works, but the attack surface area has been increased accidentally.

The solution for the first type of mistakes is simple: Grant the appropriate permissions to the DBO. It is the second type scenario the one that we should really worry about as it may not be obvious and it could allow a rogue user in the DB to escalate his privileges and take control of SQL Server.


Conclusions

The TW bit is used as a mechanism to control the surface area on very powerful features, but it relies on the DBO to be following least privilege principles, and not being a sysadmin. Having the combination of a sysadmin being the DBO on a DB that has been marked as TW bit = ON is a very dangerous one and we recommend to avoid it whenever it is possible.

As an alternative to the TW bit, in SQL Server 2005 we also introduced the notion of module signing, and the digital signatures can be used to extend the impersonation context beyond the DBand to establish an explicit trust on signed assemblies.