Symmetric Key Problem RRS feed

  • Question

  • I am working on a SQL Server project, with the following parameters

    Database 1: SQL Server 2008 R2 Enterprise

    - Database tables which include aes256 encrypted columns (varbinary datatype)

    - symmetric keys encrypted by certificates

    - for each table containing varbinary columns, there exists a corresponding view which decrypts the data table

    -end users not allowed direct access to this database, only allowed access using views on Database 2


    Database 2: 

    -Same server, SQL Server version as DB1

    -COnsists only of views which provide access to DB1 views, which decrypt data for authorised users

    -Users granted read access to this database


    My problem is, users woudl need to OPEN SYMMETRIC KEY on DB1 (using SSMS), a database they have no direct rights on, in order to decrypt data. Permission chaining is setup to allow users with read permission on the views in DB2 the ability to see data on DB1.

    The solution I am looking for is to allow for a scenario where users so not have to run OPEN SYMMETRIC KEY in order to decrypt data, but as this command is session specific i am doubtful if this can be achieved. We do need the 2 database configuration, for many reasons.

    Any advice is much appreciated   

    Monday, September 5, 2011 6:57 PM


  • I think I have a solution - table valued functions
    • Marked as answer by rtdh Monday, September 5, 2011 8:38 PM
    Monday, September 5, 2011 8:38 PM