0

I have a SQL Server 2016 database configured with AE in Production environment. Column Master key Certificate is available(under Windows Certificate Store -> Local Machine location) in a different server other than database server and the web server hosting ASP.NET application also has the Certificate to encrypt/decrypt data.

For troubleshooting or operational support purpose, we usually backup and restore this Production database to our staging/UAT environment on need basis.

After restoring the database, web app is NOT working as expected since it's missing the Certificate used to configure AE in Production environment.

As per the security policy, client is NOT willing to bring the AE Certificate from Windows Certificate Store to the target environment.

In this regard, what are the best practices to be followed to make the web app working when we restore database from one environment to another? Or is there any other approaches to make the web app working without bringing the Certificate?

Improve this question

1 Answer 1

Reset to default
3

See this description of how Always Encrypted works. Basically, SQL Server does not have access to the keys required to decrypt data into plain text. This work is performed by the client driver and in your scenario, the client driver will go looking for the certificate to decrypt the data for the web application but on failing to find the correct certificate, the data will not be decrypted.

This won't cause a failure in itself, as you can see from this example if you don't have column encryption support enabled in the driver you still get results back, but when that data isn't decrypted the client driver simply returns the encrypted binary value.

It is likely, however, that your web application is relying on unencrypted data to do more than simply present a result set, and for this, you will need the certificate. If the client refuses to allow the certificate to be shared in lower environments, then you cannot use restored database copies from production in your non-production environments.

Improve this answer
4
  • Thanks for your nice explanation. I was thinking an alternative approach like copying the database to staging server, using the PROD Cert to decrypt the columns and re-encrypt it using staging CERT. This way we don't have to bring over the PROD CERT anywhere and the app still can run using a staging specific CERT
    – Sri
    Nov 22, 2019 at 22:36
  • That would be a suitable solution. Check out this article on rotating keys, which is essentially what you need to do in the stage environment.
    – HandyD
    Nov 24, 2019 at 22:17
  • Thanks for your guidance. I would not prefer to keep those PROD CMK/CEK within the database for security reasons. My approach is first, to decrypt those specific columns to plain-text using PROD CERT in staging environment. Second, logon to staging database server, delete the current CMK/CEK keys from staging database and then create new CMK/CEK to re-encrypt those columns so that I could avoid the rotation process on staging environment. Is this approach safe to proceed? Our intention is bring over the database from PROD to staging for troubleshooting purpose on need basis.
    – Sri
    Nov 25, 2019 at 15:58
  • You will still need the PROD CMK accessible in staging in order to decrypt the data when you decrypt to plain text, so the process of decrypting to plain text then re-encrypting with new keys is simply extra work but still requires the PROD CMK. If you restore to staging and rotate the CMK to use a staging CMK you can then delete the PROD CMK from the staging server without breaking decryption, but you will still need the PROD CMK available in staging temporarily, there is no way around that.
    – HandyD
    Nov 25, 2019 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.