Can't restore a specific SQL Server database in Docker

Question

I create a SQL Server in Docker and I can successfully connect to it from SQL Server Management Studio. I copy the backup file mybackup.bak from my server to the Docker SQL container path /var/opt/mssql/backup/.

I can see this file in my SQL Server Management Studio, but when I try to restore it, I get an "access denied" error.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreUtil.GetSourceDbNames(ICollection`1 bkdevList, String credential, IBackgroundOperationContext backgroundContext)
at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseDialogController.<>c__DisplayClass8.b__7(IBackgroundOperationContext backgroundContext)

Cannot open backup device '/var/opt/mssql/backup/Keycloak.bak'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-3201-database-engine-error

Server Name: 155.155.155.155,1433
Error Number: 3201
Severity: 16
State: 2
Line Number: 1

Can you please tell me how can I give this access. It will be great help. I see this stack question but I don't know how this person didn't find access denied issue.

Answer 1

On Linux, the SQL Server service account runs under a user called mssql. By default the directory /var/opt/mssql and its contents are owned by the user and group mssql as well.

backup isn't a default folder in this directory, so presumably you created this yourself. If you created the directory, however, it won't be owned by mssql by default, and likely is/was owned by your user and the group users.

Therefore, you'll want to change the owner of said directory. I also assume you are a member of the mssql group here, and thus want the user and group owner to be the same

sudo chown mssql: /var/opt/mssql/backup -R
sudo chmod 775 /var/opt/mssql/backup -R

You likely will also want to ensure that any files created in that folder inherit the group owner, so that if you copy further backups they are are owned by the group mssql. You can do this will the following:

sudo chmod +s /var/opt/mssql/backup

Then try your RESTORE again.

If you aren't a member of the mssql group, once you change the owner of the directory you'll lose access to it. You could, therefore, add yourself to the group with the following:

sudo usermod -aG mssql {your user}

Obviously, you'll need to replace {your user} with your user. Groups, however, aren't applied immediately, you you'll need to reconnect to the host or impersonate yourself.

Alternatively, you could change the user owner of backup to yourself and have the group mssql also the owner; (though personally I think adding the mssql group to your user is a better choice):

sudo chown {youruser}:mssql /var/opt/mssql/backup -R

Answer 2

You have to grant permissions to your folder

/var/opt/mssql/backup/

See:

https://stackoverflow.com/a/45973108/214977

Start with 777, get it working, then try to back it down.