0

I am trying 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 issued 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 the Microsoft PVKConverter software, as it is not certified for use on our equipment.

I'm using the standard syntax for the CREATE CERTIFICATE from example B of the MS documentation (file names and paths are obviously different):

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 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.

The master key was created successfully.

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:

    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, sa the syntax diagram specifies the DECRYPTION BY clause as being optional))

  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 the 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 cert or the 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? a) Do I have to import the certificate into the personal store on the local machine first? None of the literature mentions this. BTW both the service account running the instance and my domain account both have rights to add and delete certificates. b) Does the private key need to be encrypted? I didn't extract it as encrypted, since the clause in the CREATE CERTIFICATE syntax description describes it as optional. c) Does the name of the certificate used in the CREATE CERTIFICATE command need to match the name on the CN of the certificate? Due to the naming conventions I initially chose, the CN of the cert has embedded dashes, and in trying to match the name of the cert to the CN, I'm having to enclose the name in double quotes. Leaving out the quotes (or using single quotes) results in a syntax error.

Will M
  • 1
  • 2
  • Does the database principal you're using have the `CREATE CERTIFICATE` permission on the target database? Ref: [GRANT Database Permissions (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql) – AlwaysLearning Aug 21 '23 at 21:54
  • You mentioned that you created the master key... did you also `OPEN MASTER KEY`? – AlwaysLearning Aug 21 '23 at 21:57
  • i'm pretty sure you don't have rights to the folder :P Try using select * from OPENROWSET(bulk 'c:\Shipping\Certs\Shipping11.pvk', SINGLE_BLOB) x – siggemannen Aug 22 '23 at 11:44
  • Thank you for all the suggestions: 1) I'm running from the admin account, the one I used to install the instance. I'm not at the point where I define the DEK for a user database, so the default database when creating the certificate is master (which I have permissions on, and which I believe I wrote to when defining the master key) 2) I am able to open the master key successfully (as in it states "command completed successfully) 3) I have rights to the folder, and got results from executing the Select * from OPENROWSET command. – Will M Aug 22 '23 at 14:51

0 Answers0