0

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.

4
  • I see it is owned by root vs mssql user chown -R mssql:mssql /var/opt/mssql/ Follow this guide docs.microsoft.com/en-us/sql/linux/… – Kin Shah Feb 19 '20 at 13:23
  • @KinShah I have tried that but it won't work because this is SQL Server 2017 in Linux which runs as root. Only from SQL Server 2019 it's running under mssql – SQLStad Feb 19 '20 at 13:32
  • any reason u are using 2017 running as root vs 2019 running as non root ? the prior is a security risk and old version. – Kin Shah Feb 19 '20 at 14:06
  • @KinShah The main reason is that not a lot of people have migrated to SQL Server 2019 yet. Also, this should never be a production scenario what I'm trying to accomplish. I also tried to run it with SQL Server 2019, and with the correct access for mssql, and I got the same error. So if I can get this to work with SQL Server 2017 I can probably apply the same resolution to SQL Server 2019 – SQLStad Feb 19 '20 at 14:18
1

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.

1
  • Thank you for your research! – Ali Razeghi Feb 5 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.