TransWikia.com

Unable to attach SQL Server database with shared folder in docker on Linux

Database Administrators Asked by SQLStad on February 14, 2021

The problem I’m having is that my SQL Server 2017 Linux container is not able to read my database files. I guess it has something to do with permissions and I’m sure I’ve overlooked something but I can’t fugure it out.

I have created a clone with the PowerShell module called dbaclone.
This module creates and attaches a differencing disk based on another VHD making it easier to provision large databases.

The databases are available using a partition access path which will look something like this

enter image description here

With in the folder, in example “AW2017-C1” is a folder called “Data” and “Log” containing the data and the log files respectively.

The permissions on the folder are “Everyone” to have “Full Control”.

enter image description here

I have a virtual machine in VMWare Workstation which is a Linux VM. The Linux VM has Docker installed. I shared the clone folder in the VM

enter image description here

To make it easier to use I added the share to /etc/fstab to mount it automatically when the VM starts.

enter image description here

The permissions are all set that everyone should be able to read it

enter image description here

I have the following script to run the docker container

sudo docker run -e 'ACCEPT_EULA=Y' 
    -e 'SA_PASSWORD=Myp@ssw0rd' 
    -p 1433:1433 --name sql1 
    -v /home/sander/databases:/databases 
    -v /home/sander/shares/dbaclone:/var/opt/mssql/data/dbaclone 
    -d mcr.microsoft.com/mssql/server:2017-latest

The docker container will mount the directory and make it accessible for the container.

The file permissions from within the container look correct

enter image description here

Everything looks ok, but when i run the following script

USE master;

CREATE DATABASE AdventureWorks2017
ON PRIMARY
       (
           FILENAME = '/var/opt/mssql/data/dbaclone/AW2017-C1/Data/AdventureWorks2017.mdf'
       ),
       (
           FILENAME = '/var/opt/mssql/data/dbaclone/AW2017-C1/Log/AdventureWorks2017_log.ldf'
       )
FOR ATTACH;

This error comes back

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file “/var/opt/mssql/data/dbaclone/AW2017-C1/Data/AdventureWorks2017.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
Msg 1802, Level 16, State 7, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Completion time: 2020-02-19T13:07:36.1675676+01:00

This obviously has something to do with permissions but it all looks good and maybe someone has else has some pointers how to get this fixed.

One Answer

I figured out the problem with shares.

One thing I did was remove the share in VMWare because for some reason, the mount with vmhgfs did not work well with SQL Server. It just could not get the right privileges even if I tried setting them with chmod.

Instead I installed CIFS and mounted the share as

sudo mount.cifs //10.100.1.1/dbaclone/clone /data/dbaclone -o credentials=/home/sander/.dbaclonecredentials,dir_mode=0777,file_mode=0777

This mounted the share as needed.

SQL Server was able to connect to it without any permissions issues.

Answered by SQLStad on February 14, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP