Symmetric Key Problem with Permissions

Question

I have symmetric key named "PasswordKey" in a database in a default instance. The key works correctly in the default instance. However, I have a report server in an instance named "sqlexpress" that also needs to decrypt a column using the same key. I get an error that states: Cannot find the symmetric key 'PasswordKey', because it does not exist or you do not have permission.

 

What permission does the symmetric key need? Someone suggested granting permission to the database role but what role in the named instance needs permission?

 

grant references on symmetric key :: PasswordKey to user

Who is the user if the report server is in an instance named sqlexpress?

Answer 1

  I haven’t had a chance to work with report server, but I would assume it may use the service account for the service unless there is a proxy account configuration.

 

  If my assumption is incorrect, you may try to use SQL Server profiler to monitor the identity of the Repro server when it connect to the default SQL Server instance.

 

  I hope this information helps.

 

-Raul Garcia

  SDE/T

  SQL Server Engine

 

Answer 2

The problem was with the user who was logging into the symmetric key. I used a SQLExpress profiler found at http://sqlprofiler.googlepages.com/ to find out who was logging in when the report ran. Then I granted permissions to the symmetric key like this: grant references on symmetric key:asswordKey to user