Mssql-docker: Some mssql-conf setting changes don't work in a container because mssql-conf assumes that sqlservr is running as a service

[twright-msft]

One example error from running mssql-con set-collation:
"Failed to connect to bus: No such file or directory
Sudo: unknown user: mssql
Sudo: unable to initialize policy plugin
An error occurred changing the collation. Check the error logs at /var/opt/mssql/log/
The server will be restarted with the previous collation value.
Failed to connect to bus: No such file or directory "

[twright-msft]

When we make the changes to resolve these issues, we will also make it possible to pass in any environment variable set settings in the mssql-conf settings.

[twright-msft]

We are targeting resolving this type of issue in the CTP 2.0 release coming up in mid-April. The plan is to allow settings to be passed in as either environment variables or you can mount a mssql-conf ini file. When sqlservr starts up it will look in the environment variables and ini file. Precedence in application will be 1) env var, 2) ini file, 3) registry in SQL PAL/default where 1 is the highest precedent/winner. Feedback welcome.

[necrosisbb]

I know this is being worked on, I am just wondering is it possible to change the collation of the mssql database in the linux docker image before start up in CTP1.4? I have been trying to do this as as I need to so before deploying a database on start up (with sqlpackage) and tried to do so by using an alternative /opt/mssql/bin/sqlservr.sh but couldn't get it to work. Is it a case that the only option is to wait til CTP 2.0

[twright-msft]

@necrosisbb - It's not possible _before_ startup right now (at least not easily using env vars). We were hoping to get that in for the 2.0 release but it didnt quite make it so we are targeting 2.1 now.

There is one option you could try for now though. If you create your own image and copy in a mssql.conf file at build time then sqlservr will use it when it starts up. Its not ideal but it could do the trick for now.

[mgrzeszczak]

@twright-msft
How can I change collation after the container is started?
I'm using this command:

sudo /opt/mssql/bin/mssql-conf set-collation

And I'm getting a following error (not to mention I had to create user mssql and give him permissions to some folders):
sqlservr: Another instance of the application is already running.

But I can't stop the running instance because this immediately kills the docker container.

[twright-msft]

@mgrzeszczak We are introducing a change in CTP 2.1 (this week, Wednesday if all goes according to plan) that will allow you to specify many of these configuration settings - port, collation, etc. - as env vars at docker run time. Then, when the sqlservr process starts inside of the container it will look for the environment variables first and use them.

[mgrzeszczak]

@twright-msft
Does it work already? I tried setting SQLSERVR_COLLATION=Polish_CS_AI as env variable on 2.1 and it still doesn't change the collation in the database.

[twright-msft]

Collation is not one of the options in CTP 2.1 unfortunately. Here is a complete list of the options available in 2.1: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables

I'll try to get MSSQL_COLLATION added as an option for CTP 2.2!

For now, one option is to create a mssql.conf file on your host and -v mount it into your container at /var/opt/mssql and then put the collation setting in the mssql.conf file. That should work for now.

[litichevskiydv]

Recently I met the problem of changing collation and found such workaround:

1. Start container in interactive mode with/bin/bash as an entry point
   docker run -p 1433:1433 -e 'SA_PASSWORD=<Password>' -e 'ACCEPT_EULA=Y' -i -d microsoft/mssql-server-linux /bin/bash
2. In the container, run the command
   /opt/mssql/bin/sqlservr --force-setup -q <CollationName>
3. Create new image from the running container
   docker commit <ContainerId> <ImageName> --change='CMD ["/opt/mssql/bin/sqlservr"]'
4. Run new container from the created image

[litichevskiydv]

I've just tried new image version (command docker run -p 1433:1433 -e 'SA_PASSWORD=Password12!' -e 'ACCEPT_EULA=Y' -e 'MSSQL_COLLATION=Cyrillic_General_CI_AS' -d microsoft/mssql-server-linux) and got a deadlock. Logs from container lie here as attachment.
info.txt

[twright-msft]

Yes, unfortunately, this is a known issue in RC1 documented in the release notes. It will be fixed in the RC2 release scheduled for the first week of August.
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes#a-idrc1-rc1-july-2017-a
Apologies for the inconvenience.

[syedhassaanahmed]

@twright-msft Using Docker for Windows, I'm unable to set the collation using 2017-latest image.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -e "MSSQL_PID=Developer" -e " MSSQL_COLLATION=Latin1_General_BIN2" -p 1401:1433 --name sql1 -d microsoft/mssql-server-linux:2017-latest

After running the above, when I attach bash and try to login using /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>' I get

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'SA'..

I've checked responses on #15 and #94 .. I'm using double quotes, the password is strong enough and also there are no volume mounts. I CAN login if I don't specify MSSQL_COLLATION=Latin1_General_BIN2" but then trying to set the collation using sudo /opt/mssql/bin/mssql-conf set-collation fails with

/opt/mssql/lib/mssql-conf/invokesqlservr.sh: line 13: sudo: command not found

[PhilipOrleans]

I made the setup work, and chose a paid version, Enterprise. I have MSDN, and the Action Pack, why SQL Server 2017 for Linux is not included yet?

[tomh3000]

@syedhassaanahmed - I too encountered "Login failed for user 'SA'" when setting MSSQL_COLLATION - the solution was to login as 'sa' lowercase. Makes sense when you think about it I guess.

[twright-msft]

@PhilipOrleans - The reason it is not on MSDN is because SQL Server on Linux container images and packages are publicly available to anybody. SQL Server on Linux packages or containers are designed to be configurable to be any edition you want. You still need to have the software use rights if you are using a paid edition though.

@syedhassaanahmed - glad you were able to resolve your issue. That does make sense actually. Closing this issue out.

[TadiPrasad]

we are able to change Sql server container coalition to use Binary collation with docker run -p 1433:1433 -e 'SA_PASSWORD=Password12!' -e 'ACCEPT_EULA=Y' -e 'MSSQL_COLLATION=Cyrillic_General_CI_AS' -d microsoft/mssql-server-linux But unfortunately BCP (bulk copy program) inside the container IN\OUT not working. These are required to load bulk data to container. Any BUG in Docker or any solution ?