Setting Permissions for a SQL Server backup folder–#SQLNewBlogger

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

While testing a script recently, I needed to set a few backup folders for my instance. This was a striped backup, and using one folder wouldn’t make sense. In modern OSes, we can’t just create a folder and expect that our processes can read it. We need to explicitly set permissions.

It’s fairly easy for SQL Server, but since it wasn’t obvious, I decided to take a minute and document this.

If I create a folder, say c:\sqlbackup, I see this kind of thing in properties on my Window 10 machine.

2019-10-08 14_53_19-SQLBackup Properties

If I click “Edit”, I get a similar view.

2019-10-08 14_53_27-Permissions for SQLBackup

Now, my SQL Server process can’t access this. If I try to restore a backup from here, I’ll get a permissions error.

That’s fine. In the Permissions, I can click Add and I’ll get this dialog.

2019-10-08 14_53_35-Select Users or Groups

From here, I can enter “NT Service\MSSQLServer” or “NT ServiceMSSQL$SQL2017” for a named instance. My named instance is SQL2017.  Note the space in “NT Service”

2019-10-08 15_00_49-Select Users or Groups

If I click the “Check Names”, this will resolve for the built in service account.

2019-10-08 15_00_53-Select Users or Groups

Then I can click OK and set the appropriate permissions.

If your service account is something not built in, it’s usually easy to find and add, but for build in accounts, you need the “NT Service”.

SQLNewBlogger

You can take a simple thing here that you needed to solve and write about it. This took my about 5 minutes to solve, playing with different names, and then about 5 minutes to write.

Showcase your knowledge today.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.