Tuesday, November 22, 2016

Permissions blocking SQL Server on Linux to open file

You're attempting to RESTORE a SQL Server database backup to your Linux installation and you get the message:

Msg 3201, Level 16, State 2, Line 17
Cannot open backup device 'C:\home\user\Downloads\AdventureWorks2012.bak'.
Operating system error 2(The system cannot find the file
Msg 3013, Level 16, State 1, Line 17
RESTORE HEADERONLY is terminating abnormally.

Check Permissions

Chances are, file and folder permissons are blocking access to the file. 

In our example, we placed the backup file in our Download folder. Regardless of the folder used, you'll need to make sure SQL Server can access the file, and that includes access to the folder. In both our CentOS 7 (1511) and Ubuntu setups, we needed to open up access starting at the top folder - Home, open up access to the Downloads folder, and finally to the backup file itself.

  • Home folder           - Allow Others to Access files
  • Downloads folder  - Allow Others to Access files
  • backup file             - Allow Others to Access files
Once you have made these changes, SQL Server should be able to access the file successfully. Below is a sample where we changed permissions on the file. Folder permissions follow a similar approach.

Naturally, for production, you'll want to use a more sophisticated approach, and probably not use the Downloads folder of a user.  Something more along the lines of:

  $ sudo chown mssql /home/user/Downloads/AdventureWorks2012.bak

  $ sudo chgrp mssql /home/user/Downloads/AdventureWorks2012.bak

Also see how to RESTORE to SQL Server on Linux:  Restore a SQL Server database to Linux.

No comments: