Using Backup Encryption with an External Certificate

Question

Using Backup Encryption with an External Certificate

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
April 25, 2019 at 12:00 am

#3139607

Comments posted to this topic are about the item Using Backup Encryption with an External Certificate

Viewing 15 posts - 1 through 15 (of 16 total)

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

CussonC Grasshopper Points: 10 More actions · Answer 1

This is a great article, and an excellent way of outlining the process.

Given a cloud-based application, do you know if it's at all possible to encrypt data (whether in a backup or cell-level encryption) where only the users can see the unencrypted data, but those who are hosting the application (and have sa access to the databases and keys) cannot decrypt it?

Thanks again for this article!

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Yes, at the very least, you can use temporary keys for data encryption. The app creates the temp key, does decrpytion/encryption, keys get dropped when session closes. The paranoid would note the key exists in memory while the user is connected, but practically, this limits the hoster from getting access.

You can also use TDE encryption with self-supplied certs in Azure. I assume in AWS, et al, as well.

CussonC Grasshopper Points: 10 More actions · Answer 3

Thanks so much Steve for your quick response! It's actually hosted in a private vendor for now, but will move to an Azure VM. TDE encryption isn't an option currently because of the SQL Server license being used (standard).

I love the idea of the temp keys for encryption in the app though! We'd have to enhance the application to do that, but that's certainly an option. One last question- in that case, we'd need the app to do the decryption before running any reports as well then, right?

Thanks again!

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

With a temp key, the decryption happens inside SQL Server. If you use a .NET namepsace, then you'd be doing the decryption on the client.

If I had to enhance the app, I'd lean towards Always Encrypted in SQL2019+, or having the encrypt/decrypt done on the client in .NET. The problems with either temp keys or .NET is that it limits indexing and searching. You can use partial hash buckets to make this better, which I talked about here (if you have access):

Maintaining High Performance When Using Encryption, PASS Summit 2014

CussonC Grasshopper Points: 10 More actions · Answer 5

Thanks again Steve, that's great information. I don't have access to that link, how can I get it?

Thanks again for being so responsive and helpful!

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

Not sure, have to join PASS and then might be a charge or maybe it's not allowed? Contact them.

I'll try to write an article on the process. Always Encrypted with SQL 2019 and Secure Enclaves is a better choice now if you can do that way. Chris Bell turned me on to the technique, and his Partial Plaintext Value is what he calls it: https://wateroxconsulting.com/archives/optimizing-protected-indexes/

Allen Newbie Points: 1 More actions · Answer 7

Hi,

It's a great article. I have a question for you. How do you manage the key if it's expired? The renewal with the same Cert name will produce the new thumbprint and will be unable to restore the database which was backup with previous Cert.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 8

Steve Jones - SSC Editor

SSC Guru

Points: 736478

August 20, 2019 at 7:53 pm

#3672909

You alter the key to use the new server certificate.

William McQuinn SSC Veteran Points: 202 More actions · Answer 9

Sorry to comment on such an old post, but this article has had the most pertinent information concerning TDE with an external certificate.

I have been trying for weeks to use an external certificate file to create a certificate for TDE encryption on a SQL Server 2019 instance. As this is for a government client, the certificate has to be issues by a trusted government authority. I cannot use makecert, and I cannot have SQL create a self-signed certificate. I also cannot install and use theMicrosoft PVKConverter software, as it is not certified for use on our equipment.

I am getting the following error:

Msg 15208, Level 16, State 10, Line 24

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Yes, the service accounts have access to the folder and the files. All the files are there in correct paths with the names spelled correctly. I've also tried the following:

1) Created the pvk from the pfx file using openssl:

pkcs12 -in {certificate-pfx-format} -nocerts -nodes -out {private-key-file-name}

2) Tried the private key with and without the password used to export the pfx file from MMC (have never explicitly encrypted the pvk file, as the syntax diagram listed in the MS article states that the encryption by password is optional if the key itself is not encrypted)

3) Tried the pvk file in its original format, with all the text prior to the BEGIN statement removed, edited with Wordpad and/or Notepad (because I've read that either can insert erroneous characters), and in RSA format (openssl rsa -in [private-key-file-name] -out [RSA_ private-key-file-name])

4) Encoding the cert in der format (openssl x509 -inform pem -in {certificatename_cer} -outform der -out {certificatename_der})

5) I sent the pfx file to someone with access to a Linux system in order to create the der and pvk file directly.

I have not tried to independently install either the cer or der encoded certificate with MMC, as I was of the understanding that the CREATE CERTIFICATE command would do that (also, that step has not been mentioned in any of the sites I have visited).

What other avenues are there for me to pursue, or is there anything I am missing from what I’ve described? I would be relieved to find out I’m missing a very fundamental, obvious, and embarrassingly stupid step, because at least I would have the solution.

William McQuinn SSC Veteran Points: 202 More actions · Answer 10

(duplicate post)

This reply was modified 1 year, 4 months ago by William McQuinn. Reason: duplicate post

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 11

William, might be good to have a new post, but did you use the CREATE CERTIFICATE syntax in SQL Server? Can you post what that did and the error?

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver16

The goal is that you load this from file, and I'd think the output of #4 would work.

William McQuinn SSC Veteran Points: 202 More actions · Answer 12

Thank you very much for your rapid response.

I've been living on that page for quite a while. I'm using this example from that page:

CREATE CERTIFICATE Shipping11 FROM FILE = 'c:\Shipping\Certs\Shipping11.cer' WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk', DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');

I have tried this both with and without the DECRYPTION BY clause, as the only password I used was for exporting the pfx file from the original certificate definition. As a side note, it's extremely frustrating for MS to require a pfx file for binding a certificate to an instance for in-flight encryption (with a huge list of prerequisites for CN, SAN, cert type, and AT_KEYSPEC values, as well as having to edit the registry for clusters), and then require a completely different type of certificate for encryption at rest, using a key file that has to be extracted via a separate, command line utility.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 13

OK, that looks good. I know that the cert formats are a little strange. The challenge I think is a lot of certs from authorities are formatted for the web, with a specific host and domain to which they're bound.

I found this, which requires the converter from MS: https://dba.stackexchange.com/questions/150869/sql-server-tde-with-pki-converting-p12-pkx-to-pvk-der-format

I suspect that you might need to work with the security folks inside your agency to get a cert they approve of, and that is in the format for SQL Server. I'm a little too busy to dig in now, but I can try to get a cert from openssl and see if I can get this working. I suspect that if I post this on my MS Distribution list, they'll come back with "use the converter"

William McQuinn SSC Veteran Points: 202 More actions · Answer 14

Thank you very much for your quick responses. I very specifically requested this cert with no reference in the CN to the server name, as I was under the impression that I would have to have the same cert on any server I needed to move and restore backups to. My usual trouble with certs has been requesting them in the wrong format (CNG vs legacy, base64 vs der encoded, etc). I even found there was a block that got put on when being moved from another platform that showed up in the properties window. I may try recreating the pvk file with encryption on the key itself, and then attempt to find a workaround for using the converter. If I find another way without using the converter, I'll be sure to post.