2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim
  • 8,669
  • 31
  • 105
  • 183
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Nov 09 '20 at 20:17
  • Thanks. I actually use "usp" but had no idea that the name affected performance. – Tim Nov 09 '20 at 20:19
  • Really interesting question. I'm surprised you can't just parameterize the open; this *feels* like a timing issue around competing open/close; very awkward – Marc Gravell Nov 09 '20 at 20:57

1 Answers1

0

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]
L Angel Chamosa Ethical Hacker
  • 1