Mssql-docker: attach_dbs environment variable error

[dbafromthecold]

When building a custom image via a dockerfile I am using the attach_dbs environment variable to attach a demo database: -

ENV attach_dbs="[{'dbName':'DatabaseA','dbFiles':['C:\SQLServer\DatabaseA.mdf','C:\SQLServer\DatabaseA_log.ldf']}]"

The image builds but when running a container from it, the container will exit. When viewing the container logs the following error is shown: -

ConvertFrom-Json : Unrecognized escape sequence. (39): [{'dbName':'DatabaseA','
dbFiles':['C:\SQLServer\DatabaseA.mdf','C:\SQLServer\DatabaseA_log.ldf']}]
At C:\start.ps1:38 char:30
$dbs = $attach_dbs_cleaned | ConvertFrom-Json
CategoryInfo : NotSpecified: (:) [ConvertFrom-Json], ArgumentEx
ception
FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Co
mmands.ConvertFromJsonCommand

This code has previously worked against older microsoft/mssql-server-windows images

[dbafromthecold]

I've replicated this on a separate instance of Docker for Windows (older version 17.06) using the latest version of the microsoft/mssql-server-windows image

[dbafromthecold]

Update - this works on Windows Server 2016 but not Windows 10.

Does anyone know what the pre-reqs are for this to work on my local Win 10 box?

[perrysk-msft]

Thanks for reporting this @dbafromthecold. We'll try to repro and investigate.

[dbafromthecold]

Thanks guys

[RangerChris]

Just my observations:

I'm able to launch the image if I don't provide the attach_dbs enviroment variable. And if I connect to the database and do a

CREATE DATABASE "MyDb" ON (FILENAME = N'C:\data\myDB.mdf'),(FILENAME = N'C:\data\my_log.ldf') FOR ATTACH;

Then the database is created just fine, so I guess it's the start.ps1 powershell script that is the problem. If I read it correct, then the problem is something with ' vs " in the json sent in the attach_dbs - but my powershell skills are limited ;-) So good luck.

[pbering]

Works if you use microsoft/mssql-server-windows-express:2016-sp1-windowsservercore-10.0.14393.1198 which is the latest tag that I can confirm to be working with attach-dbs

[perrysk-msft]

[Update] Sorry for the delayed response on this issue. There was a bug in the ps script that we believe is now fixed. The latest developer and express images should now work with the attach_dbs environment variable.
•microsoft/mssql-server-windows-express (https://hub.docker.com/r/microsoft/mssql-server-windows-express/)
•microsoft/mssql-server-windows-developer (https://hub.docker.com/r/microsoft/mssql-server-windows-developer/)

Could you please verify that this problem is fixed on your end before we close this issue?

Thanks,
Perry

[pbering]

Thank your! It is working again

[dbafromthecold]

Sorry but I'm still seeing this issue.

I pulled down the latest developer image but still seeing the error when attaching a database via en environment variable

[dbafromthecold]

One thing I did notice. I removed all my previous MS SQL images but one layer remained.

Do I need to remove all my MS images to re-pull all the layers down?

[perrysk-msft]

@dbafromthecold - I don't believe you need to remove all your MS images - Pulling the latest SQL image should get the fix.

• What is the docker run command you are running?
• What OS?

[dbafromthecold]

@perrysk-msft thanks for coming back. I'm running on Windows 10 and have attached the dockerfile I'm using to build an image.

To pull the image down, I'm running: -

docker pull microsoft/mssql-server-windows-developer:latest

Then to build the image (running from the location of the dockerfile): -

docker build -t testimage .

Dockerfile.zip

[pradley]

I'm having this exact same problem but its only happening through compose files not when I use docker run

[bsakel]

An ugly but working solution is something like
ENV attach_dbs="[{'dbName':'DatabaseA','dbFiles':['C:\\SQLServer\\DatabaseA.mdf','C:\\SQLServer\\DatabaseA_log.ldf']}]"

Found it -> https://github.com/Microsoft/mssql-docker/issues/90

[folencao]

I had encountered the same issue, finally, figured it out, you should use four backslashed in Dockerfile on windows 10:

ENV attach_dbs="[{'dbName':'DatabaseA','dbFiles':['C:\\\\SQLServer\\\DatabaseA.mdf','C:\\\\SQLServer\\\\DatabaseA_log.ldf']}]"

But four backslashes like above in docker compose file does not work, so finally have to only use attach_dbs in Dockerfile instead of compose file

[bstreitATX]

I tried attach_dbs using one, two and four backslashes, both in the docker-compose file and separately via Dockerfile. All of them throw the invalid escape sequence error for me using microsoft/mssql-server-windows-developer:latest

I can make this work passing in the same data via docker run, so there seems to be something about the way the data is being interpreted via the powershell script that's causing problems. I'm at a loss. Any advice?

[joshuashort]

@bstreitATX I'm not sure about docker-compose, but this might help for Dockerfile: https://docs.docker.com/engine/reference/builder/#escape

[bstreitATX]

@joshuashort -- Thank you for that one. It does shed some light on why people were recommending different things about the backslashes (and why I was seeing unexplained backticks in some of the Dockerfile examples I ran across).

Unfortunately it still doesn't seem to fix my ultimate issue, which appears to be with how values passed in via the attach_dbs environment variable are then handled in the powershell script. Others seem to have made modifications to the powershell script itself, but I'm still a little confused if this is just a known issue with using the mssql image in Windows containers or if it's something more environment-specific.

Relevant section from build step:

...
Step 5/5 : ENV attach_dbs='[{"dbName":"administration","dbFiles":["C:\mssql_data\administration.mdf","C:\mssql_data\administration_log.ldf"]}]'
 ---> Running in da5abd317727
Removing intermediate container da5abd317727
 ---> 0594d1242dc8
Successfully built 0594d1242dc8
Successfully tagged bstreit-mssql:0.0.1

Then when I bring up the container using docker-compose, I get the same error that others have posted about in https://github.com/Microsoft/mssql-docker/issues/40, https://github.com/Microsoft/mssql-docker/issues/41, and https://github.com/Microsoft/mssql-docker/issues/90 :

c:\dev\mssql-container>docker-compose up
Creating network "mssqlcontainer_default" with the default driver
Creating mssqlcontainer_db_1 ... done
Attaching to mssqlcontainer_db_1
db_1  | VERBOSE: Starting SQL Server
db_1  | VERBOSE: Changing SA login credentials
db_1  | ConvertFrom-Json : Unrecognized escape sequence. (44): [{'dbName':'administration','dbFiles':['C:\mssql_data\administration.mdf','C:\mssql_data\administration_log.ldf']}]
db_1  | At C:\start.ps1:47 char:30
db_1  | + $dbs = $attach_dbs_cleaned | ConvertFrom-Json
db_1  | +                              ~~~~~~~~~~~~~~~~
db_1  |     + CategoryInfo          : NotSpecified: (:) [ConvertFrom-Json], ArgumentException
db_1  |     + FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
db_1  |
mssqlcontainer_db_1 exited with code 1

At this point I think I'm going to abandon attempting to use attach_dbs and just go about building my own Initialization script to detect and attach DB's as described here:

https://github.com/docker/labs/tree/master/windows/sql-server

[joshuashort]

@bstreitATX Have you tried setting the escape directive to backtick and using double-backslash in the environment variable for attach_dbs? I'd be interested to know if that works.. since the error you posted seems to be coming from a JSON parser, which would expect two backslashes..

[bstreitATX]

@joshuashort - I realized that some of the Docker caching behavior was masking the problem. I was trying suggested changes to escaping and quoting, but didn't realize that the changes weren't making it through to the final image. I tried removing attach_dbs completely and I was still getting the same error, which is what made me realize something was being cached.

Using both of these may be redundant/overkill, but this took care of my caching issues:

docker build --no-cache -f Dockerfile.local -t bstreit-mssql:latest ./
docker-compose up --force-recreate

And here's the final Dockerfile syntax that worked. Basically I had to set escape to backtick, use double backslashes in the file paths, AND pay attention to single-quote vs. double-quote encapsulation and how that is passed through from Dockerfile to JSON via Powershell:

# escape=`
FROM microsoft/mssql-server-windows-developer:latest

#SET ENV VARIABLES FOR EULA, DATA_PATH (USED IN OTHER SCRIPTS) AND SA_PASSWORD (SET VIA start script in MSSQL image)
ENV ACCEPT_EULA="Y" `
    DATA_PATH="C:\mssql_data" `
    SA_PASSWORD="XXXXX" `
    attach_dbs='[{"dbName":"administration","dbFiles":["C:\\mssql_data\\administration.mdf","C:\\mssql_data\\administration_log.ldf"]}]'

My docker-compose is irrelevant, but posting it just for completeness in case anyone else runs into the same issues:

version: '3'
services:
    db:
        image: bstreit-mssql:latest
        build: 
            context: ./
            dockerfile: ./Dockerfile.local
        ports:
            - "1433:1433"
        volumes:
            - C:/dev/mssql-container/volumes/db1:C:/MSSQL_DATA
            - C:/bstreit:C:/bstreit

@joshuashort - Thanks again for the pointers.

[joshuashort]

Thanks @bstreitATX! I forgot to mention the JSON-specific string delimiters (combined with escaping rules that need to pass between a few languages), but it'll help me sleep to know that there's a combination of things that work 👍

[experimenti]

Another functional work-around after some attempts... no attention paid to powershell script:

docker-compose up --build

docker-compose.yml

version: '3.3'

  sqlservice:
    build:
      context: .
      dockerfile: ./db/Dockerfile
    ports:
      - "1333:1433"
    environment:
      - ACCEPT_EULA=Y  
      - SA_PASSWORD=TopSecret!  
    volumes:
        - C:\temp:C:/temp

Dockerfile:

FROM microsoft/mssql-server-windows-express 

ENV attach_dbs='[{"dbName":"cpsdb","dbFiles":["C:\\temp\\bigdb.mdf","C:\\temp\\bigdb.ldf"]}]'

[twright-msft]

Looks like we have a winning design here. I've updated the docs on Docker Hub to provide an example of the format required when using attach_dbs in a Dockerfile or docker-compose.