Using Volumes with 2019

dave-fl commented 4 years ago

What is the suggested way to use a volume at path: /var/opt/mssql/. Since 2019 operates as a non-root user by default I am getting the following error:

2019-12-07 21:52:16.89 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2019-12-07 21:52:16.94 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

Compose file is like so:

version: "3.7"
services:
  sqlserver_2019:
    image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
    hostname: sqlserver_2019
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Passw0rd
    volumes:
      - type: volume
        source: data
        target: /var/opt/mssql/data
volumes:
  data:
    external:
      name: sqlserver_2019

bguessaoui commented 4 years ago

Hello,

same issue here,

Any news?

alemhnan commented 4 years ago

Same here

Henkolicious commented 4 years ago

Same issue here.

croblesm commented 4 years ago

What is the suggested way to use a volume at path: /var/opt/mssql/. Since 2019 operates as a non-root user by default I am getting the following error:

2019-12-07 21:52:16.89 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2019-12-07 21:52:16.94 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

Compose file is like so:

version: "3.7"
services:
  sqlserver_2019:
    image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
    hostname: sqlserver_2019
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Passw0rd
    volumes:
      - type: volume
        source: data
        target: /var/opt/mssql/data
volumes:
  data:
    external:
      name: sqlserver_2019

Hello there,

I think there was a little problem when declaring the volume name at the end. Here you have the YML syntax:

version: "3.7"
services:
  sqlserver_2019:
    image: mcr.microsoft.com/mssql/server:2019-CU2-ubuntu-16.04
    hostname: sqlserver_2019
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Passw0rd
    volumes:
      - type: volume
        source: data
        target: /var/opt/mssql
volumes:
  data:

Testing the volume by creating a new database called "VolTest":

[dba mastery] $  sqlcmd -S localhost -USA -P'YourStrong!Passw0rd'
1> create database VolTest;
2> go
1> select name from sys.databases;
2> go
name                                                                                                                            
------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
VolTest

A few points to remark here:

You don't need to use docker-compose to create a single container, it works best for a compose solution :)
The /var/opt/mssql is the path where both MDF and LDF files are created, you can create one volume for each one if needed.
I used the latest SQL Server 2019 image for Ubuntu 2019-CU2-ubuntu-16.04. You can check the complete tab list here.

Cheers,

darkguy2008 commented 4 years ago

What if you don't want to use a named volume? I don't really like Docker telling me where my data should be, I like my data to be in the same folder where I have the project. So, in my case:

docker -v
Docker version 19.03.11, build 42e35e61f3

docker-compose.yml:

version: '3.6'
services:

  db:
    restart: always
    image: mcr.microsoft.com/mssql/server:latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=BLAH
      - MSSQL_PID=Express
    ports:
        - 1433:1433
    volumes:
      - ./data:/var/opt/mssql/data
    logging:
      driver: "json-file"
      options:
        max-size: "500k"
        max-file: "5"

I get this same dreaded error, so I can't start up my DB:

db_1  | 2020-06-02 02:18:32.94 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-06-02 02:18:33.14 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
db_1  | ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

What is the solution then?

darkguy2008 commented 4 years ago

Well I found the solution: Run as root -> https://github.com/microsoft/mssql-docker/issues/13#issuecomment-641904197

marcozm commented 3 years ago

I found the solution in spanish https://www.eiximenis.dev/posts/2020-06-26-sql-server-docker-no-se-ejecuta-en-root/

mrjamiebowman commented 3 years ago

Okay, so Marcozm's link worked for me.

Basically, I have a Dockerfile that changes the user to root by running this command "USER root" and now everything works fine.

By default, I believe it runs under the user "mssql".

emomicrowave commented 3 years ago

Unfortunately sometimes running a container as root is not an option - for example in an Openshift or Kubernetes cluster. For anyone googling and stumbling on this issue like I did - it's a permissions issue. By default /var/opt/mssql/data and /var/opt/sqlsever don't the necessary permissions and owners.

Since I was using a custom Dockerfile anyway, adding the following lines solved my issue. Some of them might be redundant / unnecessary though.

USER root

RUN mkdir -p -m 777 /var/opt/mssql && chgrp -R 0 /var/opt/mssql
RUN mkdir -p -m 777 /var/opt/mssql/data && chgrp -R 0 /var/opt/mssql/data
RUN mkdir -p -m 777 /var/opt/sqlserver && chgrp -R 0 /var/opt/sqlserver

RUN chmod -R 777 /var/opt/mssql /var/opt/sqlserver /var/opt/mssql/data
RUN chown -R mssql:0 /var/opt/mssql /var/opt/sqlserver /var/opt/mssql/data

USER mssql

amvin87-zz commented 3 years ago

SQL Server 2019 by default runs as a non root container. Here is a sample dockerfile where we create a custom container https://github.com/microsoft/mssql-docker/blob/master/linux/preview/SLES/dockerfile you can see we give permissions and ownership to the mssql user and group ( which is the default user/group) that runs the SQL 2019 containers. Recommendation is always to run the container as non root.

G-ManiX commented 3 years ago

I found the solution in spanish https://www.eiximenis.dev/posts/2020-06-26-sql-server-docker-no-se-ejecuta-en-root/

This worked for me

moti-malka commented 3 years ago

Just add user: root

  sql:
    image: "mcr.microsoft.com/mssql/server"
    user: root
    environment:
      - SA_PASSWORD=${SQLPASS}
      - ACCEPT_EULA="Y"
    ports: 
      - 1433:1433 
    volumes:
      - /home/moti/data:/var/opt/mssql/data
      - /home/log:/var/opt/mssql/log
      - /home/secrets:/var/opt/mssql/secrets

nicholasyin commented 2 years ago

Do I have to create the 'mssql' user explicitly in the host?

moti-malka commented 2 years ago

@nicholasyin the user sa is built in.

michac commented 2 years ago

I solved this problem by making the entire /var/opt/mssql folder a volume instead of just the data subdirectory.

alexlukic commented 2 years ago

If you need to run your container as non root user, see what is your current user on system with coomand: id uid=502(myuser) gid=20(mygroup) Because in container default user is mssql with guid 10001 then issue commnad sudo chown -R 10001:mygroup /var/opt/mssql/ in my case: sudo chown -R 10001:20 /var/opt/mssql/ sudo chmod -R 770 /var/opt/mssql/ # won't work without that!

in microsoft docs is written: chown -R 10001:0 /var/opt/mssql/ but it is not working unless you want to run ms sserver as a root with commnad sudo docker run .... (not recommanded)

And works with it! (MacOs)

RoswellCityUK commented 2 years ago

With SELinux turned on, you can use those free commands to allow container to have access to volume:

sudo chcon -Rh svirt_sandbox_file_t /path/to/volume/directory
sudo chmod -R 777 /path/to/volume/directory
sudo stat -c %a /patch/to/volume/directory

It is good to keep those always at hand.

nathanpovo commented 1 year ago

On Windows, using the root user does not work.

I get this error

This program has encountered a fatal error and cannot continue running at Mon Jan 16 11:55:07 2023
The following diagnostic information is available:

         Reason: 0x00000006
        Message: Termination of \SystemRoot\system32\AppLoader.exe was due to fatal error 0xC0000001
        Address: 0x3fffa5888b7f
    Stack Trace:
                 file://package4/windows/system32/sqlpal.dll+0x000000000000E16F
                 file://package4/windows/system32/sqlpal.dll+0x000000000000B879
                 file://package4/windows/system32/sqlpal.dll+0x0000000000088DEF
                 file://package4/windows/system32/sqlpal.dll+0x0000000000088B7F
                 file://package4/windows/system32/sqlpal.dll+0x0000000000086FE8
                 file://package4/windows/system32/sqlpal.dll+0x0000000000003D1F
                 file://package4/windows/system32/sqlpal.dll+0x0000000000202428
                 file:///windows/system32/AppLoader.exe+0x000000000000371F
                 file:///windows/system32/AppLoader.exe+0x0000000000003869
                 file:///windows/system32/AppLoader.exe+0x0000000000003889
                 file:///windows/system32/AppLoader.exe+0x000000000000A8E8
                 file:///Windows/SYSTEM32/KERNEL32.DLL+0x0000000000014414
                 file:///windows/system32/ntdll.dll+0x0000000000075541
        Modules:
                 file://package4/windows/system32/sqlpal.dll=E59EEA3917F70A1E2DB0DFD3370D69501
                 file:///windows/system32/AppLoader.exe=D7AA0798438350C25E1958B896A98C9E1
                 file:///Windows/SYSTEM32/KERNEL32.DLL=C715300FB2664729A6126A3F591E6F302
                 file:///windows/system32/ntdll.dll=45137AA3F9814512B3123991067EEE6E2
        Process: 10 - sqlservr
         Thread: 40 (application thread 0x74)
    Instance Id: c96842c8-bfbd-405f-8a90-02b24e06f42d
       Crash Id: b5185085-14b7-4590-9ce1-8c0b15d92d06
    Build stamp: f708684a2cfcb51177273c54f975ed8c62029cbc89aa962bf7d6b956f01a0c27
   Distribution: Ubuntu 20.04.5 LTS
     Processors: 8
   Total Memory: 7997534208 bytes
      Timestamp: Mon Jan 16 11:55:07 2023

When using this docker compose

version: '3.9'
services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    user: root
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>
    volumes:
      - ./mssql:/var/opt/mssql

I had to map the /var/opt/mssql/data directory directly to make it work:

version: '3.9'
services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1435:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>
    volumes:
      - ./mssqldata:/var/opt/mssql/data

PureKrome commented 1 year ago

Peeps - it works out of the box.

MSSql needs three folders to get this working. Here's a picture of my docker instance:

So there's two ways to do this.

just define the "parent" folder (aka /var/opt/mssql) and this will be stored in a single volume
define all three folders data / log / secrets (aka /var/opt/mssql/data, /var/opt/mssql/log ...) and each one has it's own volume

I prefer to keep things simple, so here's mine:

version: '3.9'
services:
  mssql:
    image: mcr.microsoft.com/mssql/server ## Latest SQL SERVER. use whatever you want/need
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>
    volumes:
      - mssql-db-vol:/var/opt/mssql
volumes:
  mssql-db-vol:

notice how i DID NOT specify user: root? Good! Notice how I used a simple docker volume (which it controls where the data is stored, etc) with the volumes: section at the bottom of the file and then did - mssql-db-vol:/var/opt/mssql to specify the link.

You could also use "bind mounts" which is a direct folder on your computer like (note: i've not tested this .. i'm just trying to use my memory for how this might work):

version: '3.9'
services:
  mssql:
    image: mcr.microsoft.com/mssql/server ## Latest SQL SERVER. use whatever you want/need
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>
    volumes:
      - ${PWD}:/var/opt/mssql

Notice how there is no volumes: section? and the ${PWD} will be the CURRENT DIRECTORY where you run the docker compose command, from.

Enjoy peeps!

microsoft / mssql-docker

Using Volumes with 2019 #542