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.
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.
I clicked “Advanced” to get a more detailed view of permissions.
Again, I really need to approve this view of the security settings. Once I acknowledge the UAC dialog, I can see this:
What I needed to do here was add permissions for another service account. Clicking Add lets me select a principal.
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.
When you click Check Names, this will shorten. If you enter this short version, it won’t work.
In my case, this account really just needs Read permissions.
Click OK, and I see it listed.
That’s it. Now my SQL2017 instance can access the backup and create the certificate.
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.