locked
Symmetric Key Problem in Reporting Services RRS feed

  • Question

  • User734793401 posted

    I have a symmetric key that is used to decrypt one column of data. I have installed the key on both the report server and the reportserver tempDB. From the Visual Studio report designer interface I can run the report and the encrypted column decrypts and displays. When I deploy the code to the server I get this error: Cannot find the symmetric key 'key', because it does not exist or you do not have permission.

    What does it mean and how can I fix it?

    Friday, August 3, 2007 2:21 PM

Answers

  • User734793401 posted

    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::PasswordKey to user

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 7, 2007 10:55 AM
  • User734793401 posted

    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::PasswordKey to user

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 7, 2007 11:50 AM

All replies

  • User-900677162 posted

    HI,davidanderson:

    You should grant view definition access on the symmetric key to the DB role.

    Here is a better approach to  implement data encryption in SQL 2005

    http://www.sqlservercentral.com/columnists/mgood/3058.asp 

    Monday, August 6, 2007 4:06 AM
  • User734793401 posted

    I'm looking for the correct syntax for doing that.

    Would it be grant view definition on symmetric key::PasswordKey to RSExecRole ?

    I'm not sure which role I should be granting the permission to.

    Monday, August 6, 2007 7:56 AM
  • User-900677162 posted

    HI,davidanderson:

    Yes. You can create a new user role and grant the permission to it.

    Tuesday, August 7, 2007 3:13 AM
  • User734793401 posted

    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::PasswordKey to user

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 7, 2007 10:55 AM
  • User734793401 posted

    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::PasswordKey to user

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 7, 2007 11:50 AM