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:
- Launch SSMS choosing Run as Administrator, which is necessary to allow creation of a certificate in the Local Machine store.
- Create a new Column Master Key (CMK) and pick the option to generate a new certificate in the Local Machine store
- Right click the old CMK, choose Rotate, and then select the new CMK created in Step 2
- 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
- Right click the old CMK and choose Cleanup
- If desired, delete the old CMK (it might be prudent to keep it for awhile depending on your situation)
- 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.