Post reply

Always Encrypted with SSRS 2016

  • January 26, 2017 at 8:37 am

    #339731

    Hello all,
    I am attempting to display Always Encrypted columns in cleartext through an SSRS 2016 report.  SQL 2016 is on SP1 CU1, .NET Framework 4.6.2 is installed, and I'm using the latest version of SSDT.  Both the SQL engine and SSRS are on the same server and the Always Encrypted certificate is in that server's certificate store under both Current User - Personal and Local Computer - Personal.  The data source connection for the report is using .Net SqlClient Data Provider with Column Encryption Setting enabled.  I can see the correct cleartext values through the report preview in SSDT but after the report is built and published to SSRS, the report doesn't show any data at all for encrypted columns (the columns are blank for each row).  For the report credentials, I've tried Windows accounts (local admin and sysadmin rights), and SQL accounts (also with sysadmin rights).  From the searching I've done, there is limited information on this setup so far but the few things I've found, including from a MS rep, seem to indicate that this should work.  Has anyone implemented this successfully?  Any general thoughts/suggestions?

  • April 25, 2017 at 9:50 am

    #1939788

    The SSRS service account didn't have access to the certificate so the data couldn't be unencrypted and displayed in the report. The reason for this is that the certificate was created through SSMS in the Local User store by a user different than the one I wanted to run the SSRS service.  The Local User store only allows access to the certificate for the user who created the certificate; there is no option to change permissions.  Makes sense.  So I needed to create the certificate in the Local Machine store.  The problem is I wasn't seeing an option to do so through either the SSMS Always Encrypted wizard or the New Column Master Key process.  What I found is that, in order to reveal the option, SSMS needs to be launched using Run As Administrator, even if you are logged in as a member of the local administrators group. 

    Now that I had the ability to create a certificate in the Local Machine store, I wanted my currently encrypted columns to use a Local Machine store certificate without having to re-encrypt them. I already had many columns with lots of data encrypted under the current certificate and it would be inefficient to have to go and re-encrypt them.  A Column Master Key (CMK) rotation was the solution, as described here: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio#rotatecmk. Once that was done, I just had to apply read permissions in the certificate for the SSRS domain service account.  Here are the steps I took: 

    1. Launch SSMS choosing Run as Administrator, which is necessary to allow creation of a certificate in the Local Machine store. 
    2. Create a new Column Master Key (CMK) and pick the option to generate a new certificate in the Local Machine store
    3. Right click the old CMK, choose Rotate, and then select the new CMK created in Step 2
    4. Export the Local Machine store certificate generated in Step 2 and import it into the Local Machine store of all clients that need to access the unencrypted data
    5. Right click the old CMK and choose Cleanup
    6. If desired, delete the old CMK (it might be prudent to keep it for awhile depending on your situation)
    7. Update the permissions on the AE certificate created in the Local Machine store in Step 2 by right clicking it and selecting All Tasks – Manage Private Keys and then providing the SSRS service account read permissions on the certificate

    I was then able to see the Always Encrypted data in cleartext (unencrypted) in the SSRS report.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply