SQL Server – Permissions Issue in Docker SQL Server 2017 While Restoring Certificate

certificatedockerlinuxsql serversql-server-2017

Docker SQL Server 2017 container @latest. Using master database.

The error I am facing is the following:

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

The closest thing I have found to this exact question is this issue on Stackoverflow. However the answer doesn't work for me. This question has a similar answer.

I have also tried the instructions here, and here.

So going through the parts of the error:

  1. I have recreated the files twice, so I don't think it's the "invalid" part. And it's obviously not the "does not exist" part (if I put in the wrong password, it tells me it's the wrong password).
  2. I have backed up and restored the SMK and Master Key without issue, so I don't think it's the permissions issue. The files have the exact same permissions.

I can't get the certificate to restore no matter what I try. I have searched the GitHub issues to no avail so I don't think it's a bug. I must be doing something wrong.

Relevant code:

--on Prod
BACKUP CERTIFICATE sqlserver_backup_cert
TO FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.cer'
WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.key',
    ENCRYPTION BY PASSWORD = 'foobar'
    )

--on Test
CREATE CERTIFICATE sqlserver_backup_cert
FROM FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.crt'
WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.key',
    DECRYPTION BY PASSWORD = 'foobar'
    )

It's noteworthy that /var/opt/mssql/certs is a Docker volume. However I have also tried creating my own directory inside the container and using docker cp. No change.

Best Answer

Is the code shown under "Relevant code:" (in the question) the exact code being executed, as in copied and pasted into here and not re-typed? I ask because the file extension on the sqlserver_backup_cert file is different between the BACKUP and CREATE (i.e. restore) statements. It is backed-up as .cer but then you are looking for .crt in the CREATE.

Also, have you tried skipping files altogether and using hex bytes (i.e. a VARBINARY literal)? You would do the following:

On Prod

SELECT CERTENCODED(CERT_ID(N'sqlserver_backup_cert')) AS [CertificateAndPublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PrivateKeyTest'),
                      'new_password_for_extract(A)',
                      'current_password(B)') AS [PrivateKey];

On Test

CREATE CERTIFICATE [sqlserver_backup_cert]
    FROM BINARY = {output of CERTENCODED}
    WITH PRIVATE KEY (
        BINARY = {output of CERTPRIVATEKEY},
                 DECRYPTION BY PASSWORD = 'new_password_for_extract(A)',
                 ENCRYPTION BY PASSWORD = 'current_password(B)'
                     );

UPDATE

O.P. has confirmed that the issue was indeed the filename extension mismatch.

Related Question