I have a database in the SQL 2005 default instance and a report server in a named instance. The database has a symmetric key for decrypting one of the columns. When the report server queries the database I get this error: "Cannot find the symmetric key 'key', because it does not exist or you do not have permission."
I know the symmtric key is there because it works when queried from the same instance. Should the database and report server be in the same instance? Or is there a way to change the permissions so that the report server can access the symmetric key in the other instance?
I might note that the report server is able to access unencrypted data in the other instance. The error only occurs when I try to decrypt data using the symmetric key.
I might follow up with this information. I believe that I need to grant permission on the symmetric key. However, I don't know the syntax for granting permission for the report server to access the key. It should be something like this:
grant references on symmetric key :: PasswordKey touser
Who is the user if the report server is in an instance named sqlexpress?
|||The problem was with the user who was logging into the symmetric key. I used a SQLExpress profiler found athttp://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::PasswordKey touser
No comments:
Post a Comment