SQL Server – Fixing ‘File is Not Valid or Does Not Exist’ Error on Reimport

sql serversql-server-2012transparent-data-encryption

We all know that the error "file is not valid or does not exist; or you do not have permissions for it" nearly always means one of two things:

  • There's a typo in the path or filename
  • The service account running SQL Server does not have permissions on the directory or file(s)

However, I am getting this error in a situation where neither of these usual causes is possible. To confirm what I'm seeing, I distilled the situation down into an example where I export a certificate from SQL Server, and then immediately try to reimport the exact same certificate from the same location. This generates the given error for reasons that have me stumped.

Because SQL Server can write out the files, we know it has permissions to the directory and the files. (And yes, I already checked that we don't have a bizarre permission scenario where the account has write permissions only but not read permissions.) And we know that there are no typos in the path or filename, because they are literally copy-pasted character-for-character from the lines just above.

Here is the SQL used to generate this example. I will also upload a screen capture of the output. I could sure use any help– I'm completely stumped at this point.

    -- make a cert, export it, and drop it
CREATE CERTIFICATE CertForTDE WITH SUBJECT='CertForExport';
GO

BACKUP CERTIFICATE CertForTDE
TO FILE = 'E:\Output\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='E:\Output\SelfSignedTdeCert.pvk', 
    ENCRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);
GO

DROP CERTIFICATE CertForTDE;
GO

-- attempt to reimport from same location causes "file is not valid or does not exist"!
CREATE CERTIFICATE CertForTDE
FROM FILE = 'E:\Output\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='E:\Output\SelfSignedTdeCert.pvk', 
    ENCRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);
GO

screen capture of SQL, the files it outputs, and the error it generates

Best Answer

You didn't decrypt the key file. This

CREATE CERTIFICATE CertForTDE
FROM FILE = 'E:\Output\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='E:\Output\SelfSignedTdeCert.pvk', 
    ENCRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);

Should be

CREATE CERTIFICATE CertForTDE
FROM FILE = 'c:\temp\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='c:\temp\SelfSignedTdeCert.pvk', 
    DECRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);

Here's a complete repro:

use master
go
drop database certtest
go
create database certtest

go
use certtest
go
create master key encryption by password = 'ABCDabcd1234!@#$'
go
CREATE CERTIFICATE CertForTDE WITH SUBJECT='CertForExport';
GO

BACKUP CERTIFICATE CertForTDE
TO FILE = 'c:\temp\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='c:\temp\SelfSignedTdeCert.pvk', 
    ENCRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);
GO

DROP CERTIFICATE CertForTDE;
GO

-- attempt to reimport from same location causes "file is not valid or does not exist"!
CREATE CERTIFICATE CertForTDE
FROM FILE = 'c:\temp\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='c:\temp\SelfSignedTdeCert.pvk', 
    ENCRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);
--Msg 15208, Level 16, State 12, Line 28
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

GO

-- attempt to reimport from same location causes "file is not valid or does not exist"!
CREATE CERTIFICATE CertForTDE
FROM FILE = 'c:\temp\SelfSignedTdeCert.cer'
WITH PRIVATE KEY (
    FILE='c:\temp\SelfSignedTdeCert.pvk', 
    DECRYPTION BY PASSWORD='ABCDabcd1234!@#$'
);
--succeeds
Related Question