6

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.

3
  • There is now a GitHub issue for this. Sep 24, 2019 at 4:26
  • 1
    Are the UID etc for the docker etc the same? It looks like an issue where the user under which the SQL Server service doesn't have access to the .key file
    – Tom V
    Sep 24, 2019 at 5:36
  • Yes that's what I'd assume it is, but all the solutions say "check NTFS perms"–of which, there are none, in this case. Perms are 0744. The UID will not be the "same" necessarily, although in this case, both users are UID=1000. Sep 25, 2019 at 2:02

1 Answer 1

5
+50

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.

6
  • Thank you, I will check. Sep 26, 2019 at 17:36
  • 1
    I haven't had a chance to look at this system... Larger systems on fire. But I will go ahead and award you and we can work through it later. :) Sep 30, 2019 at 19:37
  • 1
    In point of fact it was the .cer vs. .crt issue. I've made an appointment with the optometrist. ;) Oct 2, 2019 at 5:19
  • 1
    @ChaimEliyah Sometimes it just takes a 2nd pair of eyes :-). Have you tried the other approach, using the VARBINARY literal? I would actually recommend that and stop messing with files in the first place. That way you can have fully-contained rollout scripts with no external dependencies. Oct 2, 2019 at 6:17
  • 1
    Okay I will give that a shot when I tackle the restore portion. And yes! L'shana tovah!!! :) Oct 2, 2019 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.