Setting Certificate Backup Permissions for an Instance–#SQLNewBlogger

,

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was testing some security change, and I made a backup of my certificate from one instance. I did this on a share that I’ll use to move the files to another instance. In this case, I had these files in the folder.

2019-08-23 14_31_13-SQL

The problem is that by default, SQL Server locks down permissions, as it should. That means that the other instance couldn’t access the files, as its service account didn’t have permissions.

I fixed this by opening the security tab for the file. I had to do this for each, but I’ll show one here.

2019-08-23 14_31_22-FinanceCert.cer Properties

I clicked “Advanced” to get a more detailed view of permissions.

2019-08-23 14_31_28-Advanced Security Settings for FinanceCert.cer

Again, I really need to approve this view of the security settings. Once I acknowledge the UAC dialog, I can see this:

2019-08-23 14_31_38-Advanced Security Settings for FinanceCert.cer

What I needed to do here was add permissions for another service account. Clicking Add lets me select a principal.

2019-08-23 14_31_46-Permission Entry for FinanceCert.cer

For a local account, I need to give the “NT Service” prefix to my account, despite this not being shown as part of the service account settings.

2019-08-23 14_31_57-Permission Entry for FinanceCert.cer

When you click Check Names, this will shorten. If you enter this short version, it won’t work.

2019-08-23 14_32_01-Permission Entry for FinanceCert.cer

In my case, this account really just needs Read permissions.

2019-08-23 14_32_05-Permission Entry for FinanceCert.cer

Click OK, and I see it listed.

2019-08-23 14_32_10-Advanced Security Settings for FinanceCert.cer

That’s it. Now my SQL2017 instance can access the backup and create the certificate.

SQLNewBlogger

This is something I’d expect most people working with SQL Server on Windows would be easily able to do, but showing some knowledge here gives confidence in your abilities.

This took longer to get screenshots than to write. You could easily do something similar.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate