Pulling SQL Server DB Decrypted Column into ASP.Net Page Fails

We have a stored procedure that returns DB encrypted columns decrypted via DecryptByKeyAutoCert(). This is called via our asp.net page (via System.Data.SqlClient.SqlCommand) and bound to a Repeater control. Any columns that go through the decryption procedure fail to show on our asp.net page. Unencrypted columns returned by the procedure display fine. The DecryptByKeyAutoCert returns a varbinary so I run the result through CONVERT, 开发者_如何学JAVAto varchar.

IE.

SELECT CONVERT(varchar(50), DecryptByKeyAutoCert( CERT_ID('ourCertId') , NULL, b.Address2 )) AS Address2
     ...

This stored procedure works fine when testing in SSMS. Any ideas?

---

Does the ASP.Net app pool account connection have the permission and key access to the decryption key? What is ourCertId encrypted with? Your test from SSMS will attempt to decrypt under a different user credentials than ASP.Net would.

---

Check that the ASP.Net app is not relying on some table metadata to render the columns. It may be thinking that the column Address2 is a Varbinary when you have now converted it to a Varchar. To be sure, try creating a view using the decrypted columns, and using the simple view as the source for the Repeater.

---

As pointed out by Remus Rusanu (Thank you) need to make sure permissions are GRANTed. I had missed these for the user used.

From the MSDN

Permissions Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.