April 10, 2019 at 1:01 pm
Hello fellows,
I have been wondering and reading about certificates for backup encryption but i haven't found any blog talking about a third party certificate to encrypt them, what i have seen is creating third party certificates to encrypt database network transmissions using SSL.
so the question is
Can i use a third party certificate, create it at the SQL Level and use it for backup encryption instead of creating one at the SQL Server level?
From what i have been reading i don't think its possible. the only possible thing is using a third party certificate for network communication using SSL.
As for encrypting the backup the only way is to create a certificate at the SQL Level and use it to encrypt the backups.(Obv taking the certificate and key backups in case of anything)
Thanks you for reading this post.
Greetings.
April 10, 2019 at 2:46 pm
Well that's an interesting question. I would think you could, because the backup encrypt docs say use CREATE CERTIFICATE. That can be used to import a third party cert, so I suspect this would work. Not in a place to check this right now, but I'll see if I can later.
The 3rd party cert for SSL sometimes has restrictions because the host name is in the cert, which is used to verify the place you're connecting is correct. Many other cert auth mechanisms don't verify this, so a cert is a cert. I know I've used certs for encryption of data that I've gotten from third parties or created from the Windows SDK.
April 10, 2019 at 3:53 pm
Thanks for replying!
The 3rd party certs you've used for encryption have been at the backup level or using TDE?
I'm reading right now PowerShell command New-SelfSignedCertificate to see if i can generate a x.509 cert which is the one Microsoft SQL Server uses.
I quote MSFT Docs on CREATE CERTIFICATE requirements:
"The Private Key must be <= 2500 bytes in encrypted format. Private keys generated by SQL Server are 1024 bits long through SQL Server 2014 (12.x) and are 2048 bits long beginning with SQL Server 2016 (13.x). Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 4,096 bits. The length of an imported private key must be an integer multiple of 64 bits. Certificates used for TDE are limited to a private key size of 3456 bits."
2nd paragraph of
If i find anything i'll let you know.
April 12, 2019 at 1:21 am
OK, I did some work on this and got an article I'll publish next week, but the short answer is that this is both easy and complex.
I've used certs for TDE and column encryption. Getting a cert from makecert or SQL Server is easy. getting one from LetsEncrypt, PoSh, or elsewhere is harder. The main issue is that you need a DER format with a private key file to load the certificate. If you use PoSh, you export this as a PFK. I haven't gotten this to separate out. There's a PVKConverter.exe utility from MS, but I can't get this to work. I also tried working with OpenSSL,but I can't get the right format, and really, I ran out of time.
Whether you use PoSh or makecert, it doesn't matter. What you just need is to protect the private key file and the password to protect your system. Managing those across time is hard, especially with passwords and staff turnover. Once you get that settled, the rest is easy.
For loading the cert, CREATE CERTIFICATE as you would for TDE works.
HTH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply