Proper way to pass symmetric key using System.Data.SqlClient to SQL Server stored procedure that invokes exec sp_executesql

Viewed 155

I have a column encrypted by a password-protected symmetric key in SQL Server 2017. On the server I can call sp_foo from another stored procedure like this:

declare @symkey_password varchar(50) = 'Where would we be without stackoverflow'
exec sp_foo @id, @symkey_password

and in sp_foo we have this:

 create procedure sp_foo
     @id int, @symkey_password varchar(50)
 as
 begin
     set @openkey = 'open symmetric key MY_SYM_KEY decryption by password = ' + quotename(@symkey_password,'''') + ';'
     exec sp_executesql @openkey;

     select name, convert(varchar(100), decryptbykey(myencryptedcolumn)) as plaintext
     from T 
     where id = @id   

     CLOSE SYMMETRIC KEY MY_SYM_KEY;   
end

That works just fine server-side.

However, client-side, when I pass @id and @symkey_password as standard parameters in a SqlCommand, and attempt to use SqlDataAdapter.Fill:

string pass = "Where would we be.... etc etc";

var c = new SqlCommand();
c.CommandText = "sp_foo";
c.CommandType = CommandType.StoredProcedure;

c.Parameters.Add(new SqlParameter("@id",12345);
c.Parameters.Add(new SqlParameter("@symkey_password, SqlDbType.VarChar, 50);
c.Parameters["@symkey_password"].Value = pass;

var DA = new SqlAdapter(c);

DataTable T;
DA.Fill(T);

sometimes I get the following error:

maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

and sometimes I get this one:

The key is not encrypted using the specified decryptor. The key 'MY_SYM_KEY' is not open. Please open the key before using it.

What is the proper way to call a stored procedure client-side in C# when the stored procedure invokes sp_executesql?

1 Answers

you need to open your symetrik key MY_SYM_KEY before all sql statements in your stored procedure

OPEN SYMMETRIC KEY MY_SYM_KEY
    DECRYPTION BY CERTIFICATE [your certificate name]
Related