Create Certificate Error

Question

Create Certificate Error

amy26

SSCrazy Eights

Points: 8926
More actions
June 15, 2016 at 6:52 am

#327456

Anybody know why the following code produces the error I have listed? 🙁
CREATE CERTIFICATE DatabaseMasterKeyCer
FROM FILE = 'd:\directory\SQLDatabaseEncryption.army.mil.cer'
WITH PRIVATE KEY (FILE = 'd:\directory\Certs\DatabaseMasterKeyCer.pvk',
DECRYPTION BY PASSWORD = 'My$Strong$Password$123');
GO
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.
I have the master key encrypted right now with a password and it is not allowed to be that way. I have to use a DoD certificate. Our IT support folks created me a certificate but now I can't seem to get it registered in SQL.
I am running this on my local machine where I have full control and admin rights.
Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

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

jasona.work SSC Guru Points: 50117 More actions · Answer 1

To clarify something: Is the SQL Server you're attempting to load this certificate to, also local to your PC, or is it a different system?

If it's a different system, you'll need to copy the certificate files to that system, and adjust the paths accordingly.

If it's local to your PC, make sure the SQL Server service account has access to the path and files for the certificate.

Out of curiosity, is it a STIG check mandating the DoD certificate to encrypt the master key? If so, which STIG is it?

Jason A

amy26 SSCrazy Eights Points: 8926 More actions · Answer 2

Yes, I'm trying to do it on my local database not to another server. I will check the startup accounts for the services... I think on my local I haven't changed them from default so that could be it.

Yes, its for SQL 2012 STIG (we are still running 2008 R2 though). STIG #V-41417.

jasona.work SSC Guru Points: 50117 More actions · Answer 3

amy26 (6/15/2016)
Yes, I'm trying to do it on my local database not to another server. I will check the startup accounts for the services... I think on my local I haven't changed them from default so that could be it.
Yes, its for SQL 2012 STIG (we are still running 2008 R2 though). STIG #V-41417.

Then yes, I'd check that the SQL service account has access to the folders, I'd bet that's the problem.

Ah, so you're using Symmetric Keys, one of the devs I work with is looking at that here. So far he's still in the "will we / won't we" stage.

amy26 SSCrazy Eights Points: 8926 More actions · Answer 4

BAH! It didn't fix it when I changed the service to my domain admin account. I wish the error message was more specific... I assume a certificate is a certificate... does it matter that someone else created the cert for me?

The only thing I have to change is the database master key is currently "by password" ... so this is really bugging me that its not an easy fix...grumble.

jasona.work SSC Guru Points: 50117 More actions · Answer 5

amy26 (6/15/2016)
BAH! It didn't fix it when I changed the service to my domain admin account. I wish the error message was more specific... I assume a certificate is a certificate... does it matter that someone else created the cert for me?
The only thing I have to change is the database master key is currently "by password" ... so this is really bugging me that its not an easy fix...grumble.

Hmm.

I don't think it should matter that someone else created the cert.

Looking at the MSDN page for CREATE CERTIFICATE, at this point I'd start looking at esoteric possible causes. Might be the key isn't a "valid" length?

Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 3,456 bits. The length of an imported private key must be an integer multiple of 64 bits.

Other than that, I'm not sure at this point.

amy26 SSCrazy Eights Points: 8926 More actions · Answer 6

Isn't the private key being created by SQL Server? Does that have to already exist too?

jasona.work SSC Guru Points: 50117 More actions · Answer 7

If you're backing up a certificate, then SQL creates both the certificate and key files.

So if you don't have the private key file, if you just have the cert file, that's likely the problem. I did a quick search, it doesn't look like you can import a cert without the PVK as well...

amy26 SSCrazy Eights Points: 8926 More actions · Answer 8

Ugh, so how do I get one? Do I ask the person who made the cert to give me a private key?

jasona.work SSC Guru Points: 50117 More actions · Answer 9

Yeah, I'd start there. I've not run into this, yet, mostly because the only certs I've needed have been for encrypting the network connection, so those go into the OS.