March 29, 2021 at 7:16 pm
Heya...
I have been tasked with setting up TDE on our SQL Server 2019 instance, and am doing so in our DEV environment as I have never done this before. I managed to create the master key, the certificate, and encrypt a database easily enough; so as a test I decided to try restoring the database to another server. I've been using this page as a guide: https://www.sqlshack.com/restoring-transparent-data-encryption-tde-enabled-databases-on-a-different-server/
The problem is, the following step:
CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:/TDE/bi_server_Cert.cer'
WITH PRIVATE KEY
(
FILE = 'C:/TDE/bi_server_Cert.pvk',
ENCRYPTION BY PASSWORD = 'ObviouslyNotActualPassword'
);
GO
I am getting this error message - which is apparently pretty common:
Msg 15208, Level 16, State 12, Line 20
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
From what I am seeing online in various blogs and forums is that when SQL Server creates the certificate, it sets the file permissions such that only the account that created it can access it. Fair enough. The problem is, I am getting the same error even after making sure that the account has full access. I've even gone brute-force and granted "Everyone" access to the folder and the certificate files; and I still get the same error. What am I missing?
March 30, 2021 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 6, 2021 at 9:13 pm
Figured it out... sort of.
I had a similar issue a few months ago with replication. We'd recently applied a new Group Policy and it had wiped the stored credentials of the service account that SQL Server service was running as. So I tried the same fix here: Changed the SQL Server service to run as local system, restart service, change it back to run as service account, restart service. After that, was able to restore the certificate with no problems at all.
Still not clear what exactly about what the Group Policy change did... it seems to only affect very specific things. It brought down replication on one server - but only when replication had to be restarted - and it prevented a certificate restore. But SQL Server was able to run normally, and was able to perform other routine file system functions, etc.
Anyway, if anyone has a similar issue, that was the "fix" in this case...
April 7, 2021 at 4:30 pm
When you back up a certificate the file that is created is locked down in permissions (ACL-Access Control List) to the SQL Server Service account only. If you were to go and change the permissions of the file in Security to allow Full Control for another user or just refresh the permissions for the current Service account so that Windows would copy the current SID of the AD account to the file security, then everything would work fine.
In this case it was all about who had access to the file. Ensure that the permissions are correct and everything works as expected. Verify the SQL Server service account login and make that user have full control. Let me know if you have any other issues. It is (should be) pretty simple and something small missed.
April 8, 2021 at 11:24 am
This was removed by the editor as SPAM
April 15, 2021 at 7:47 pm
Ben -
I appreciate the reply. However, in this case the same service account is being used on both servers, and I made sure that the service account had full access to the files, and I still got the error. After I changed the SQL Server service to run as Local System, restarted it, changed it back to my service account, restarted again, it began to work as expected. So, while I am quite sure that what you are saying is how it is *supposed* to work - it matches all of the other information I've been able to find - in my case, it doesn't seem to have been working correctly.
And to make things even more interesting, I am trying to replicate everything in another clone of our environment, and am having the same issue - however, with an added twist. After restarting the service, I can access the bi_server_Cert.cer file - but not the bi_server_Cert.pvk file. Or at least, it will let me create the certificate on the second server if I leave out the pvk. Unfortunately the certificate doesn't work when I do it that way.
Have a meeting scheduled with our security gurus... something is not working the way it should, just not sure what exactly.
April 15, 2021 at 7:52 pm
Thanks. I was really just illustrating that it is a permissions issue with the file. The way I have done it in a demo is to allow permissions to Everyone to ensure that it is a permissions issue. It appears that the SIDs are messed up on some of the files. ACLs are not the best way to rely on security, but when SQL backs up the certificate and PVK files it does isolate the ACL to itself.
I have seen issues that you are illustrating, and I have either copied the files to another folder, like the backup folder or the Data folder where the databases are (COPY not MOVE as move will retain the permissions and copy inherits from the folder copied to) and then reattempt it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply