The Last Pickle

Following on from my previous post I’ve created a procedure to read encrypted data using the same principles: Executing the procedure in the context of a least privilege user and elevating the permissions using code signing as follows.

create procedure sales_api.usp_getCCTrans
with
    execute as 'lpu_code_context'
as
begin
    begin try
        open symmetric key
            cc_key
        decryption by
            asymmetric key encryption_root

        select
            name_on_card,
            amount,
            occurred,
            cast(DecryptByKey( card_number) as varchar(max))
        from
            sales_data.cc_trans

        close symmetric key cc_key
    end try
    begin catch
        if (exists (select * from sys.openkeys where key_name = 'cc_key'))
        begin
            close symmetric key cc_key
        end

        print 'ERROR: ' + cast(ERROR_NUMBER() as varchar(50)) + ' - ' + ERROR_MESSAGE() 

    end catch
end
go

-- we know we need to so sign the proc
add signature to
    sales_api.usp_getCCTrans
by
    certificate internal_code
go

-- grant the app_user permission to exec the proc
grant execute on object::sales_api.usp_getCCTrans to app_user
go This works, and does not leak open symmetric keys can be seen with a simple test. 

execute as user ='app_user'
exec sales_api.usp_getCCTransrevert
select * from sys.openkeys

This works well if the procedure is called from TSQL code; execution context is elevated, the necessary key is opened, a result set is created for the caller and the elevated context is removed. However if this procedure is called from .net code using the Sql Native Client its possible for the client to execute TSQL code *while* the result set is being returned to the caller through the expediency of Multiple Active Result Sets. MARS allows a client to iterate over a result set that has been partially delivered to the client and make and another call to the db on the same connection.

The second call will have the original execution context, the one used in the connection string, and not the elevated context the first call is now running it. It will though share the same session context as the first call, meaning session level data such as the open symmetric keys are available. To test this out I filled my original table with around 32k rows to make sure there was enough records to force multiple packets to be returned and ran the following c#.

static void Main()
{
    //get a connection with MARS enabled
    SqlConnection con = new SqlConnection(
        "server=(local);database=secure;uid=app_user;pwd=pword;MultipleActiveResultSets=true");

    SqlCommand cmdGet = new SqlCommand("sales_api.usp_getCCTrans", con);
    con.Open();
    SqlDataReader rdrGet = cmdGet.ExecuteReader();

    while (rdrGet.Read())
    {
        //we have one value from the result set, test another call to the server
        SqlCommand cmdInner = new SqlCommand("select user_name() as u", con);
        cmdInner.CommandType = CommandType.Text;

        //user name is app_user
        Console.WriteLine("Inner call user name is: " + (string)cmdInner.ExecuteScalar());

        //check when keys are open
        // the cc_key is
        cmdInner.CommandText = "select key_name from sys.openkeys";
        SqlDataReader rdrInner = cmdInner.ExecuteReader();
        while (rdrInner.Read())
        {
            Console.WriteLine("Key " + rdrInner.GetString(0) + " is open");
        }
        rdrInner.Close();

        //an open leaked key, lets try to use it
        // fails the user app_user does not have permission to select from the table
        cmdInner.CommandText = "select cast(DecryptByKey( card_number) as varchar(max)) from sales_data.cc_trans";
        rdrInner = cmdInner.ExecuteReader();
        while (rdrInner.Read())
        {
            Console.WriteLine("got card number = " + rdrInner.GetString(0));
            break;
        }
        rdrInner.Close();
        break;
    }
    rdrGet.Close();
    Console.WriteLine("Press return");
    Console.ReadLine();
}

By using a MARS connection the client app is able to view which keys are open and decrypt any data it has access to. In this case the limited permissions of the app_user account meant the select statement was stopped, but this clearly is a problem. If the account used by the application has permission to select from the table it can decrypt the data. Grant the following permission to test this:

grant select on object::sales_data.cc_trans to app_user

But there is another way to decrypt data, rather than manually opening the asymmetric key the *DecryptByKeyAutoAsymKey* function can be used which will open the asymmetric key, perform the decryption and close the key. I modified the get query above to use the Auto function and removed the explicit open and close. Calling the procedure in both TSQL (as above) and C# using MARS there were no leaked keys. My assumption here is that the function is called once for each row and has to decrypt the asymmetric key and open the symmetric key for each call, which should incur a small performance penalty when compared to holding the keys open. This can be observed by examining the “Estimated CPU Cost” of the Compute Scalar operation in the Execution Plan, in my tests the auto method was roughly double that of the manual.

So I take two things away from this. First always have the application connect with a user account that only has permission to call procedures and second consider using the auto functions to decrypt data possibly after testing performance.