Tcp provider error code 0x2749

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this: root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master...

@manicmonkey

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:

root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master -i /opt/setup.sql
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Dockerfile

FROM microsoft/mssql-server-linux:2017-CU1

ADD entrypoint.sh setup.sql /opt/
RUN chmod u+x /opt/entrypoint.sh

ENV ACCEPT_EULA Y
ENV SA_PASSWORD <password>

ENTRYPOINT /opt/entrypoint.sh

entrypoint.sh

#!/bin/bash

echo -e "$(date +%F %T.%N) t SQL Server entrypoint..."
/opt/mssql/bin/sqlservr &

# Note - we were originally using netcat to detect port 1433 being bound, but it caused SQL Server to crash, hence the sleep...
sleep 60s

echo -e "$(date +%F %T.%N) t Database server has started, creating database"
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d master -i /opt/setup.sql

echo -e "$(date +%F %T.%N) t Created database"
while true; do sleep 1000; done

After starting the container and seeing the problem occurring during the entry script I’ve exec’d in and it’s currently reproducible. See attached strace after running:

strace /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d master -i /opt/setup.sql

mssql_tcp_error_strace.txt

azizunsal, nick-kk, RafaeLeal, villainoustourist, VassilisPallas, dahousecat, pme123, drewhagen, octagonal, bsor-dev, and 8 more reacted with thumbs up emoji

@twright-msft

Maybe try 127.0.0.1 instead of localhost?

jaindee, guozequn, juancamilo100, llwt, teknus, techspud, jcrowegitHu8, ParfaitG, BreadSpoon, pmenager, and 8 more reacted with thumbs up emoji
KushtrimPacaj and jondoetsch27 reacted with laugh emoji
NEPTLIANG, omatviiv, moxia, JanMouwes, china-zyy, Blue-Pink, mradcliffe, primeNumberAndMe, RabinMallick, CasperHogenboom, and rodrigofbm reacted with confused emoji

@borgdylan

This is happening with CU5 on non-docker installs as well

@twright-msft

@borgdylan

I fixed the issue, by uninstalling CU5 and re-installing using the GDR repository.

@akshayd29

@borgdylan Can you please list the steps to fix the issue? How do you uninstall CU5 and reinstall using GDR?

@borgdylan

@catmeme

I tried every single image available, and continued to receive intermittent issues with this container starting. The good news, is that it only seems to fail once and can do a retry… as such, the solution I went with was an entrypoint script that retries what I want in the case of a failure.

This container is not stable, the intermittent failures seem to be 1 of 3. This solution I propose is a hack, but it’s a hack that’s work for me on our build server.

#!/usr/bin/env bash

# this seems to help (eyeroll)
sleep 10

echo "Creating database"

import_data() {
    #/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U SA -P ${SA_PASSWORD} -i /application/setup.sql
    /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U SA -P ${SA_PASSWORD} 
        -Q 'RESTORE DATABASE MyDatabase FROM DISK = "/var/opt/mssql/backup/MyDatabaseBackup.bak" WITH MOVE "mydatabase" TO "/var/opt/mssql/data/MyDatabase.mdf", MOVE "MyDatabase_log" TO "/var/opt/mssql/data/MyDatabase_log.ldf"'
}

import_data || (sleep 5 && import_data)

The last line basically says, «try this, if it fails, wait 5 seconds and try it again»

We went from every other build failing, or more, to 20 builds in a row, no failures… but the log output includes the failure that’s caught, and retried.

@jkone27

got the same issue, running windows 10, trying to run some sql scripts at build time in a docker file, e execute this command as a first one in the image — in the windows containers version seems to work ad build time (no need for delayed entry point execution for setup sql scripts):


FROM microsoft/mssql-server-linux:latest
#mcr.microsoft.com/mssql/server:2017-GDR-ubuntu
ENV SA_PASSWORD "Somep@ssw0rdyouLike"
ENV ACCEPT_EULA "Y"

#set bash ad default term
SHELL ["/bin/bash", "-c"]

#add sql tools to path
ENV PATH="$PATH:/opt/mssql-tools/bin:/opt/mssql/bin" 

RUN sqlcmd -S 127.0.0.1 -U SA -P $SA_PASSWORD -Q "SELECT 1"

will trigger

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

something is wrong?

@dantheman999301

Just want to chime in that I was also seeing this, doing something very similar to what @jkone27 was doing.

I was using Azure DevOps hosting build agents with the Docker task to run SQL Server for integration tests.

I think the missing crucial command that was needed was for me to up the memory on the container to 3GB, from whatever the default is that Docker uses. After I had changed that it seemed to work every time.

Hope it helps someone!

@aharpervc

I was using Azure DevOps hosting build agents with the Docker task to run SQL Server for integration tests.
I think the missing crucial command that was needed was for me to up the memory on the container to 3GB, from whatever the default is that Docker uses. After I had changed that it seemed to work every time.

@dantheman999301 how did you do that?

@octagonal

After spending nearly a day trying every proposed solution I ended up just buying an RDS MSSQL micro instance on AWS ($20/month) ¯_(ツ)_/¯

This issue makes mssql-docker completely unusable for me.

@jkone27

I tried to build the image with -m 3G, didn’t solve.

to check you can build an image

docker build -t «testimage» —rm -f «DockerFile» -m 3G .

# DockerFile
FROM mcr.microsoft.com/mssql/server:latest #(or microsoft/mssql-server-linux:latest)
ENV SA_PASSWORD «P@ssword1433»
ENV ACCEPT_EULA «Y»
RUN ps -aux

it will show no SQL server processes running,
whereas you ‘ll see running process in the windows container version.

the only workaround for me is to do it with script :
actually run the image,
apply changes and commit to a new image,
which loses the purpose of docker file though…

@dantheman999301

Yeah it stopped working for us, but the failures have gone down.

Completely out of ideas now 😒 Makes using it in build pipelines incredibly frustrating.

@anderslevin

@maurei

For me to fix this issue, it was as simple as properly using bash sleep, allowing the mssql server within the container to actually start up before trying to logon.

FROM microsoft/mssql-server-linux
ARG password
ARG accept
ARG backup

ENV MSSQL_SA_PASSWORD=$password
ENV ACCEPT_EULA=$accept

RUN /opt/mssql/bin/sqlservr --accept-eula & (echo "awaiting server bootup" && sleep 15 && echo "lets try to logon"  && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $password)

@wejdross

hello all, in my case helped
sqlcmd -S 127.0.0.1 -U blah -P 'blah'
and
sqlcmd -S exapmple.com -U blah -P 'blah'

Hope it helps You guys! ;)

@dprasanthv

I tried every single image available, and continued to receive intermittent issues with this container starting. The good news, is that it only seems to fail once and can do a retry… as such, the solution I went with was an entrypoint script that retries what I want in the case of a failure.

This container is not stable, the intermittent failures seem to be 1 of 3. This solution I propose is a hack, but it’s a hack that’s work for me on our build server.

#!/usr/bin/env bash

# this seems to help (eyeroll)
sleep 10

echo "Creating database"

import_data() {
    #/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U SA -P ${SA_PASSWORD} -i /application/setup.sql
    /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U SA -P ${SA_PASSWORD} 
        -Q 'RESTORE DATABASE MyDatabase FROM DISK = "/var/opt/mssql/backup/MyDatabaseBackup.bak" WITH MOVE "mydatabase" TO "/var/opt/mssql/data/MyDatabase.mdf", MOVE "MyDatabase_log" TO "/var/opt/mssql/data/MyDatabase_log.ldf"'
}

import_data || (sleep 5 && import_data)

The last line basically says, «try this, if it fails, wait 5 seconds and try it again»

We went from every other build failing, or more, to 20 builds in a row, no failures… but the log output includes the failure that’s caught, and retried.

I didn’t get even single build failure. I made it sleep for 10 sec and it is able to connect

@kierenj

I’m not sure this is server-related. I’m getting the same error running sqlcmd from the command line.
Maybe it’s sqlcmd related?

My dockerfile:

FROM mcr.microsoft.com/mssql-tools:latest
RUN curl -sL https://aka.ms/InstallAzureCLIDeb | bash
CMD [ "/bin/bash" ]

I run:

sqlcmd -H tcp:my_sql_azure_host.database.windows.net,1433 -U MyUser -P MyPass -d MyDb -q "SELECT 1+1" -N

And always get:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

(On local Docker — on Win 10 latest build)

The server is running, the firewall is open — I can connect via my local Azure Data Studio. But not from inside docker?

@twright-msft would you let me know if I should raise a separate issue for this’un please?

@marklude

Using complex password fixed the issue.

@Garwin4j

Yeah putting a sleep worked for me.

entrypoint.sh:

sleep 25 && /opt/mssql-tools/bin/sqlcmd -S db -U sa -P Password -i /restore_db.sql & /opt/mssql/bin/sqlservr

Sidenote: put the /opt/mssql/bin/sqlservr after the script command because the services seems to die after the script command is run if it is first.

@MathewsThankachan

Garwin, is it possible to post the entire dockerfile of yours please

@will2877

Hi Guys!

I’m having this issue in the following Setup:
Client:
Version 17.5.0002.1 Linux on Debian Buster (10)

Server:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) — 13.0.4604.0 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : **TCP Provider: Error code 0x2749.** Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

might this be a backward compatinility isse?

@croblesm

I experienced this issue in the past, the best workaround I found so far is to use an env variable for the sleep command. A container does not take the same time to start on my laptop than in a server, so I figured out how much time it took for the environment and created a script that can use variables

My entry point looks like this:

/db_scripts/DBA/sql_deployment.sh $1 $2 & /opt/mssql/bin/sqlservr

My deployment script is a little bit complex in really is some kind of script launcher, it takes care of hiding the SA password (received from the Dockerfile), also uses functions to deploy different things from repositories that are downloaded to my container during the creation depending on the environment I want to build.

Regards,

@alequeshow

From what I experienced building from a custom Dockerfile is that the sql startup and the sql commands should be made in the same layer. So you must execute all database commands in the same Shell Script. In my case I created a sql-startup.sh:

#!/bin/bash

/opt/mssql/bin/sqlservr --accept-eula --sa-password Passw0rd & (echo "awaiting server bootup" && sleep 15 && echo "--Done")

cd /var/opt/myapp

sleep 15

import_data() {
    echo "... Attempt to create and restore database"

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Passw0rd -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/MY_DB.bak'"

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Passw0rd-Q "RESTORE DATABASE MY_DB FROM DISK = '/var/opt/mssql/backup/MY_DB.bak' WITH MOVE 'MY_DB' TO '/var/opt/mssql/data/MY_DB.mdf', MOVE 'MY_DB_DATA' TO '/var/opt/mssql/data/MY_DB.ndf', MOVE 'MY_DB_log' TO '/var/opt/mssql/data/MY_DB.ldf', MOVE 'MY_DB_INDEX' TO '/var/opt/mssql/data/MY_DB_INDEX.ndf'"
}

import_data || (sleep 5 && import_data)

echo "Start running SQL Scripts"

ls

for fs in *.sql
do
    echo "Executing script $fs"
    /opt/mssql-tools/bin/sqlcmd -S localhost -d MY_DB -U sa -P Passw0rd -i "$fs"
done

echo "--Done"`

@ghost

@UlissesMeira

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:

root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master -i /opt/setup.sql
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Dockerfile

FROM microsoft/mssql-server-linux:2017-CU1

ADD entrypoint.sh setup.sql /opt/
RUN chmod u+x /opt/entrypoint.sh

ENV ACCEPT_EULA Y
ENV SA_PASSWORD <password>

ENTRYPOINT /opt/entrypoint.sh

entrypoint.sh

#!/bin/bash

echo -e "$(date +%F %T.%N) t SQL Server entrypoint..."
/opt/mssql/bin/sqlservr &

# Note - we were originally using netcat to detect port 1433 being bound, but it caused SQL Server to crash, hence the sleep...
sleep 60s

echo -e "$(date +%F %T.%N) t Database server has started, creating database"
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d master -i /opt/setup.sql

echo -e "$(date +%F %T.%N) t Created database"
while true; do sleep 1000; done

After starting the container and seeing the problem occurring during the entry script I’ve exec’d in and it’s currently reproducible. See attached strace after running:

strace /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d master -i /opt/setup.sql

mssql_tcp_error_strace.txt

Hi

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:

root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master -i /opt/setup.sql
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Dockerfile

FROM microsoft/mssql-server-linux:2017-CU1

ADD entrypoint.sh setup.sql /opt/
RUN chmod u+x /opt/entrypoint.sh

ENV ACCEPT_EULA Y
ENV SA_PASSWORD <password>

ENTRYPOINT /opt/entrypoint.sh

entrypoint.sh

#!/bin/bash

echo -e "$(date +%F %T.%N) t SQL Server entrypoint..."
/opt/mssql/bin/sqlservr &

# Note - we were originally using netcat to detect port 1433 being bound, but it caused SQL Server to crash, hence the sleep...
sleep 60s

echo -e "$(date +%F %T.%N) t Database server has started, creating database"
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d master -i /opt/setup.sql

echo -e "$(date +%F %T.%N) t Created database"
while true; do sleep 1000; done

After starting the container and seeing the problem occurring during the entry script I’ve exec’d in and it’s currently reproducible. See attached strace after running:

strace /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <password> -d master -i /opt/setup.sql

mssql_tcp_error_strace.txt

Hi manicmonkey ! what did you do to solve it? Do you remember?
tks

@Garwin4j

Garwin, is it possible to post the entire dockerfile of yours please

@MathewsThankachan I don’t know what to say… I am literally just seeing this request today. Forgive me. I created a repository with my dockerfile, I know it is late but if it can help anyone, here it is:

https://github.com/Garwin4j/sql-server-docker

@mazilu88

Using complex password fixed the issue.

This fixed the issue for me

@jammerful

I’m also running into this issue intermittently, is the only fix to put in a sleep?

@Tutuviz

Just want to chime in that I was also seeing this, doing something very similar to what @jkone27 was doing.

I was using Azure DevOps hosting build agents with the Docker task to run SQL Server for integration tests.

I think the missing crucial command that was needed was for me to up the memory on the container to 3GB, from whatever the default is that Docker uses. After I had changed that it seemed to work every time.

Hope it helps someone!

This (after some good hours) helped me, I had to delete everything and allocate even more memory

Источник

Содержание

  1. Cannot connect to MSSQL on local network — Centos / php72 / mssql2017 #703
  2. Comments
  3. php -m|grep sqlsrv
  4. Login timeout expired. TCP Provider: Error code 0x2749 #203
  5. Comments
  6. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired

Cannot connect to MSSQL on local network — Centos / php72 / mssql2017 #703

+## Driver version or file name
+Please tell us what the PHP driver version or file name is.
pdo_sqlsrv, sqlsrv
+## SQL Server version
+Please tell us what the SQL Server version is.
Sql Server Express 2017
+## Client operating system
+Please tell us what oprating system the client program is running on.
Centos 7
+## PHP version
+Please tell us which version of PHP you are running.
php 7.2
+## Microsoft ODBC Driver version
+Please tell us which version of the Microsoft ODBC Driver you are using.
extension = sqlsrv.so
extension = pdo_sqlsrv.so
+## Table schema
+Please tell us the table schema
+
+## Problem description
+Please share more details with us.
Cannot connect. Can’t connect via code, can’t connect via sqlcmd. I can connect fine from SQL Server Management Console, though.

SQLCMD:
[root@rfmdev tmp]# sqlcmd -S 192.168.151.40 -U myuser -P ‘mypass’
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

+## Expected behavior and actual behavior
+Please tell us what should happen and what happened instead
Expected: To connect.
Actual: Did not connect.

+## Repro code
+Please share repro code with us, or tell us how to reproduce the issue.

This is a CLEAN Centos that was just installed. Installed the REMI php-72, and then sqlsrv.

php -m|grep sqlsrv

The text was updated successfully, but these errors were encountered:

Источник

Login timeout expired. TCP Provider: Error code 0x2749 #203

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:

root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master -i /opt/setup.sql
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

After starting the container and seeing the problem occurring during the entry script I’ve exec’d in and it’s currently reproducible. See attached strace after running:

strace /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P

-d master -i /opt/setup.sql

The text was updated successfully, but these errors were encountered:

Maybe try 127.0.0.1 instead of localhost?

This is happening with CU5 on non-docker installs as well

This is a generic error message when sqlcmd can’t connect because the server is not online.
Because your Entrypoint is your entrypoint.sh script my guess is that sqlservr is failing to start for some reason and yet your container will continue to live on because PID 1 is still alive. Please check the logs for more info. Troubleshooting info:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide#access-the-log-files

I fixed the issue, by uninstalling CU5 and re-installing using the GDR repository.

@borgdylan Can you please list the steps to fix the issue? How do you uninstall CU5 and reinstall using GDR?

I tried every single image available, and continued to receive intermittent issues with this container starting. The good news, is that it only seems to fail once and can do a retry. as such, the solution I went with was an entrypoint script that retries what I want in the case of a failure.

This container is not stable, the intermittent failures seem to be 1 of 3. This solution I propose is a hack, but it’s a hack that’s work for me on our build server.

The last line basically says, «try this, if it fails, wait 5 seconds and try it again»

We went from every other build failing, or more, to 20 builds in a row, no failures. but the log output includes the failure that’s caught, and retried.

got the same issue, running windows 10, trying to run some sql scripts at build time in a docker file, e execute this command as a first one in the image — in the windows containers version seems to work ad build time (no need for delayed entry point execution for setup sql scripts):

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

something is wrong?

Just want to chime in that I was also seeing this, doing something very similar to what @jkone27 was doing.

I was using Azure DevOps hosting build agents with the Docker task to run SQL Server for integration tests.

I think the missing crucial command that was needed was for me to up the memory on the container to 3GB, from whatever the default is that Docker uses. After I had changed that it seemed to work every time.

Hope it helps someone!

I was using Azure DevOps hosting build agents with the Docker task to run SQL Server for integration tests.
I think the missing crucial command that was needed was for me to up the memory on the container to 3GB, from whatever the default is that Docker uses. After I had changed that it seemed to work every time.

After spending nearly a day trying every proposed solution I ended up just buying an RDS MSSQL micro instance on AWS ($20/month) ¯_(ツ)_/¯

This issue makes mssql-docker completely unusable for me.

I tried to build the image with -m 3G, didn’t solve.

to check you can build an image

docker build -t «testimage» —rm -f «DockerFile» -m 3G .

# DockerFile
FROM mcr.microsoft.com/mssql/server:latest #(or microsoft/mssql-server-linux:latest)
ENV SA_PASSWORD «P@ssword1433»
ENV ACCEPT_EULA «Y»
RUN ps -aux

it will show no SQL server processes running,
whereas you ‘ll see running process in the windows container version.

the only workaround for me is to do it with script :
actually run the image,
apply changes and commit to a new image,
which loses the purpose of docker file though.

Yeah it stopped working for us, but the failures have gone down.

Completely out of ideas now 😒 Makes using it in build pipelines incredibly frustrating.

I had the same issue and fixed it by switching from AUFS to overlay2
https://docs.docker.com/storage/storagedriver/overlayfs-driver/

The issue for me was both this connection issue, but also that i had to restart my host to stop the container. It got totally stuck. Both issues was resolved by switching fs.

For me to fix this issue, it was as simple as properly using bash sleep , allowing the mssql server within the container to actually start up before trying to logon.

hello all, in my case helped
sqlcmd -S 127.0.0.1 -U blah -P ‘blah’
and
sqlcmd -S exapmple.com -U blah -P ‘blah’

Hope it helps You guys! 😉

I tried every single image available, and continued to receive intermittent issues with this container starting. The good news, is that it only seems to fail once and can do a retry. as such, the solution I went with was an entrypoint script that retries what I want in the case of a failure.

This container is not stable, the intermittent failures seem to be 1 of 3. This solution I propose is a hack, but it’s a hack that’s work for me on our build server.

The last line basically says, «try this, if it fails, wait 5 seconds and try it again»

We went from every other build failing, or more, to 20 builds in a row, no failures. but the log output includes the failure that’s caught, and retried.

I didn’t get even single build failure. I made it sleep for 10 sec and it is able to connect

I’m not sure this is server-related. I’m getting the same error running sqlcmd from the command line.
Maybe it’s sqlcmd related?

sqlcmd -H tcp:my_sql_azure_host.database.windows.net,1433 -U MyUser -P MyPass -d MyDb -q «SELECT 1+1» -N

(On local Docker — on Win 10 latest build)

The server is running, the firewall is open — I can connect via my local Azure Data Studio. But not from inside docker?

@twright-msft would you let me know if I should raise a separate issue for this’un please?

Using complex password fixed the issue.

Yeah putting a sleep worked for me.

Sidenote: put the /opt/mssql/bin/sqlservr after the script command because the services seems to die after the script command is run if it is first.

Garwin, is it possible to post the entire dockerfile of yours please

I’m having this issue in the following Setup:
Client:
Version 17.5.0002.1 Linux on Debian Buster (10)

Server:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) — 13.0.4604.0 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : **TCP Provider: Error code 0x2749.** Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

might this be a backward compatinility isse?

I experienced this issue in the past, the best workaround I found so far is to use an env variable for the sleep command. A container does not take the same time to start on my laptop than in a server, so I figured out how much time it took for the environment and created a script that can use variables

My entry point looks like this:

My deployment script is a little bit complex in really is some kind of script launcher, it takes care of hiding the SA password (received from the Dockerfile), also uses functions to deploy different things from repositories that are downloaded to my container during the creation depending on the environment I want to build.

From what I experienced building from a custom Dockerfile is that the sql startup and the sql commands should be made in the same layer. So you must execute all database commands in the same Shell Script. In my case I created a sql-startup.sh:

In my case, SQL server did not start and my Docker image could not be built because of https://support.microsoft.com/en-ca/help/4532432/mssql-conf-tool-fails-if-ipv6-is-disabled-on-linux.
The solution was to add:

in my Dockerfile .

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:

root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master -i /opt/setup.sql
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

After starting the container and seeing the problem occurring during the entry script I’ve exec’d in and it’s currently reproducible. See attached strace after running:

strace /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P

-d master -i /opt/setup.sql

I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:

root@7f73d863b0da:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P -d master -i /opt/setup.sql
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

After starting the container and seeing the problem occurring during the entry script I’ve exec’d in and it’s currently reproducible. See attached strace after running:

strace /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P

-d master -i /opt/setup.sql

Hi manicmonkey ! what did you do to solve it? Do you remember?
tks

Источник

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired

I have installed SQL Server 2019 on Linux platform and had done all the necessary steps and I was able to login to SQL Server via sqlcmd some time ago but suddenly I have started seeing below error when connecting to SQL Server via sqlcmd. i have verified for port as well and its open. please help me to fix this issue.

]# sqlcmd -Slocalhost -Usa -PXXXXX
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

]# firewall-cmd —list-ports
1433/tcp

We have not received a response from you. Did the reply could help you? If the response helped, do «Accept Answer«. If it doesn’t work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Hi Seeya,
Sorry for delayed response, i had already tried using 127.0.0.1 but it did not help. SQL Server is also up and running.
PFB snap and let me know if i need to check anything else.

]$ sqlcmd -S127.0.0.1 -Usa -PXXXXX
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Источник

Источник

thx Olaf:

  I am difficult to reproduce that error, since it was tested at 14, now I already upgrade to 15; but the following

is clearly show connection error:(again, if any of you supply me either high level program(sql server management) or low level instruction/code to modify that master.mdf to make my ubuntu’s directory all under sql server account’s control, I am happly to
do that, I guess every ms progammers all know in unix, so long

you are root/superuser, just chmod 777 (director or file); you make all open)

——-

eric@eric-Vostro-1500:~/JavaPrograms/cis206/FinalExamTest$ service mssql-server start
eric@eric-Vostro-1500:~/JavaPrograms/cis206/FinalExamTest$ systemctl status mssql-server
● mssql-server.service — Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2019-05-31 19:38:43 PDT; 5s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 5266 (sqlservr)
    Tasks: 1
   Memory: 124.5M
   CGroup: /system.slice/mssql-server.service
           └─5266 /opt/mssql/bin/sqlservr

May 31 19:38:43 eric-Vostro-1500 systemd[1]: Started Microsoft SQL Server Database Engine.
eric@eric-Vostro-1500:~/JavaPrograms/cis206/FinalExamTest$ sudo sqlcmd -S localhost -U SA
[sudo] password for eric:
sudo: sqlcmd: command not found
eric@eric-Vostro-1500:~/JavaPrograms/cis206/FinalExamTest$ sudo su
root@eric-Vostro-1500:/home/eric/JavaPrograms/cis206/FinalExamTest# sqlcmd -S localhost -U SA
Password:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured
to allow remote connections. For more information see SQL Server Books Online..
——————————————————————————————————————————————————-

again, can i assume I don’t have hardware issue about 2GM RAM is too low to make connection/play mssql(15)?

also, I try set network.tcpport 1433

but, when I do netstat -tulpn

it did not show my tcp(actually all, tcp, tcp6, udp) be associate with 1433; it did not change anything

some github article believe it is because ubuntu18.04(probably 18.10 too) using tcp dynamic ip

which different 16.04; so mssql used worked at ubuntu16 now not work at 18; unless using some external

software (like Laravel) to modify/set it to the right one.  which I do download and install but don’t how to use it.

I also download and install Azusa’s sql studio data(when I do netstat -tulpn; it show its existence at tcp), but

when I click connection, of course, it reply by Error (35) and (40)

I am stand along on my laptop, sometimes with internet sometimes without internet; so i  guess I will use

localhost/127.0.0.1/eric-Vostro-1500   as my servername(can I?)

looking to see your help again soon

Источник

I updated my server today with all pending update and after reboot I was unable to connect to SQL server. Prior to reboot I’ve also enabled the firewall but now it’s disabled again and server is after another reboot. When I try to connect locally using

sqlcmd -S localhost

I get error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Server itself is up and running:

hyperqbe@slaro:/etc$ sudo systemctl status mssql-server
[sudo] hasło użytkownika hyperqbe:
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2019-08-08 15:06:28 CEST; 19min ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 7259 (sqlservr)
    Tasks: 158
   CGroup: /system.slice/mssql-server.service
           ├─7259 /opt/mssql/bin/sqlservr
           └─7288 /opt/mssql/bin/sqlservr

sie 08 15:06:38 slaro sqlservr[7259]: [95B blob data]
sie 08 15:06:38 slaro sqlservr[7259]: [91B blob data]
sie 08 15:06:38 slaro sqlservr[7259]: [145B blob data]
sie 08 15:06:38 slaro sqlservr[7259]: [61B blob data]
sie 08 15:06:39 slaro sqlservr[7259]: [96B blob data]
sie 08 15:06:39 slaro sqlservr[7259]: [66B blob data]
sie 08 15:06:40 slaro sqlservr[7259]: [96B blob data]
sie 08 15:06:40 slaro sqlservr[7259]: [100B blob data]
sie 08 15:06:40 slaro sqlservr[7259]: [71B blob data]
sie 08 15:06:40 slaro sqlservr[7259]: [124B blob data]

I’m unable to connect remotely from other machine as well:

TITLE: Connect to Server
------------------------------
Cannot connect to 192.168.1.141.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - Istniejące połączenie zostało gwałtownie zamknięte przez zdalnego hosta.) (Microsoft SQL Server, Error: 10054)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

As far as I know there were no errors during update process.

I tried running sudo /opt/mssql/bin/mssql-conf setup and chose Express version  but after choosing the language and providing SA password it still stated: «This is an evaluation
version.  There are [163] days left in the evaluation period.«

Here are the last entries from errorlog:

2019-08-08 15:06:34.89 Server      Microsoft SQL Server 2019 (CTP3.2) - 15.0.1800.32 (X64)
        Jul 17 2019 21:29:33
        Copyright (C) 2019 Microsoft Corporation
        Express Edition (64-bit) on Linux (Ubuntu 18.04.3 LTS) <X64>
2019-08-08 15:06:34.89 Server      UTC adjustment: 2:00
2019-08-08 15:06:34.89 Server      (c) Microsoft Corporation.
2019-08-08 15:06:34.89 Server      All rights reserved.
2019-08-08 15:06:34.89 Server      Server process ID is 32.
2019-08-08 15:06:34.89 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2019-08-08 15:06:34.89 Server      Registry startup parameters:
         -d /var/opt/mssql/data/master.mdf
         -l /var/opt/mssql/data/mastlog.ldf
         -e /var/opt/mssql/log/errorlog
2019-08-08 15:06:34.90 Server      SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2019-08-08 15:06:34.90 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2019-08-08 15:06:34.90 Server      Detected 12800 MB of RAM. This is an informational message; no user action is required.
2019-08-08 15:06:34.90 Server      Using conventional memory in the memory manager.
2019-08-08 15:06:34.90 Server      Page exclusion bitmap is enabled.
2019-08-08 15:06:35.01 Server      Buffer pool extension is not supported on Linux platform.
2019-08-08 15:06:35.02 Server      Buffer Pool: Allocating 2097152 bytes for 1963331 hashPages.
2019-08-08 15:06:35.16 Server      Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2019-08-08 15:06:35.69 Server      Buffer pool extension is already disabled. No action is necessary.
2019-08-08 15:06:35.99 Server      Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2019-08-08 15:06:36.01 Server      Query Store settings initialized with enabled = 1,
2019-08-08 15:06:36.07 Server      The maximum number of dedicated administrator connections for this instance is '1'
2019-08-08 15:06:36.07 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2019-08-08 15:06:36.11 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2019-08-08 15:06:36.17 Server      In-Memory OLTP initialized on lowend machine.
2019-08-08 15:06:36.37 Server      CLR version v4.0.30319 loaded.
2019-08-08 15:06:36.57 Server      [INFO] Created Extended Events session 'hkenginexesession'

2019-08-08 15:06:36.58 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2019-08-08 15:06:36.59 Server      Total Log Writer threads: 2. This is an informational message; no user action is required.
2019-08-08 15:06:36.72 Server      clflushopt is selected for pmem flush operation.
2019-08-08 15:06:36.72 Server      Software Usage Metrics is disabled.
2019-08-08 15:06:36.97 spid11s     [1]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2019-08-08 15:06:36.98 spid11s     Starting up database 'master'.
2019-08-08 15:06:37.58 Server      Common language runtime (CLR) functionality initialized.
2019-08-08 15:06:37.77 spid11s     SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2019-08-08 15:06:37.78 spid11s     SQL Server Audit has started the audits. This is an informational message. No user action is required.
2019-08-08 15:06:38.05 Server      Failed to verify the Authenticode signature of 'C:binnsecforwarder.dll'. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support.
2019-08-08 15:06:38.11 spid11s     SQL Trace ID 1 was started by login "sa".
2019-08-08 15:06:38.27 spid11s     Server name is 'slaro'. This is an informational message only. No user action is required.
2019-08-08 15:06:38.29 spid29s     Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2019-08-08 15:06:38.29 spid29s     Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2019-08-08 15:06:38.30 spid11s     [4]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2019-08-08 15:06:38.30 spid11s     Starting up database 'msdb'.
2019-08-08 15:06:38.32 spid12s     [32767]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2019-08-08 15:06:38.32 spid12s     Starting up database 'mssqlsystemresource'.
2019-08-08 15:06:38.33 spid12s     The resource database build version is 15.00.1800. This is an informational message only. No user action is required.
2019-08-08 15:06:38.36 spid12s     [3]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2019-08-08 15:06:38.36 spid12s     Starting up database 'model'.
2019-08-08 15:06:38.40 Server      Failed to verify the Authenticode signature of 'C:binnmsoledbsql.dll'. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support.
2019-08-08 15:06:38.48 spid28s     A self-generated certificate was successfully loaded for encryption.
2019-08-08 15:06:38.51 Server      Error: 37308, Severity: 16, State: 1.
2019-08-08 15:06:38.51 Server      Loaded None enclave for always encrypted.
2019-08-08 15:06:38.51 spid28s     Server is listening on [ 192.168.1.141 <ipv4> 1433].
2019-08-08 15:06:38.54 spid28s     Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2019-08-08 15:06:38.54 spid28s     Error: 39002, Severity: 16, State: 1.
2019-08-08 15:06:38.54 spid28s     SQL failed to boot extensibility for error code 0x80070005.
2019-08-08 15:06:38.55 spid28s     InitializeXdbPkgLauncher failed. ErrorCode: 0x80004005.
2019-08-08 15:06:38.55 spid28s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2019-08-08 15:06:38.85 spid12s     Clearing tempdb database.
2019-08-08 15:06:39.88 spid12s     [2]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2019-08-08 15:06:39.89 spid12s     Starting up database 'tempdb'.
2019-08-08 15:06:40.26 spid29s     The Service Broker endpoint is in disabled or stopped state.
2019-08-08 15:06:40.26 spid29s     The Database Mirroring endpoint is in disabled or stopped state.
2019-08-08 15:06:40.27 spid29s     Service Broker manager has started.
2019-08-08 15:06:40.27 spid11s     Recovery is complete. This is an informational message only. No user action is required.
2019-08-08 15:27:16.30 spid55      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2019-08-08 15:27:16.30 spid55      Starting up database 'ProgramMagazynowy'.
2019-08-08 15:27:16.53 spid55      Parallel redo is started for database 'ProgramMagazynowy' with worker pool size [2].
201
Источник

I tried to establish SQL Server in Docker because I wanted to learn SQL again.

Table of contents

  1. Create a Dockerfile and run
  2. Use compose file to mount a host directory
  3. Connect with SQL Server Management Studio
    1. Connect to the SQL server in Docker container
    2. Create a Dabase
  4. Play with SQL
  5. End

Create a Dockerfile and run

It’s possible to run SQL Server in Docker container without Dockerfile. Following command is used in Docker hub.

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

However, it’s command is long to repeat it. I created a Dockerfile for it. You can find the original file here.

FROM mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-20.04
ENV ACCEPT_EULA=Y 
SA_PASSWORD=passworD142! 
MSSQL_PID=Developer
EXPOSE 1433:1433

Then, run it.

$cd src/Docker/images/sql-server
docker build -t sqlserver:2019-cu11 .
#1 [internal] load build definition from Dockerfile
...

$ docker images
REPOSITORY  TAG         IMAGE ID        CREATED         SIZE
sqlserver   2019-cu11   8581ad6577d3    4 weeks ago     1.5GB

$ docker run -d --name sqlserver-2019 sqlserver:2019-cu11
e336c2e8753205ad52f9a7ffbb23b5e7b69318c2b042460ecb07428e01641f3d

$ docker ps
CONTAINER ID   IMAGE                 COMMAND                  CREATED         STATUS         PORTS                NAMES
e336c2e87532   sqlserver:2019-cu11   "/opt/mssql/bin/perm窶ｦ"   7 seconds ago   Up 6 seconds   1433/tcp, 1433/tcp   sqlserver-2019

It downloaded the image from Docker Hub and build was successful. Let’s connect to it.

$ docker exec -it sqlserver-2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
the input device is not a TTY.  If you are using mintty, try prefixing the command with 'winpty'

$ winpty docker exec -it sqlserver-2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
OCI runtime exec failed: exec failed: container_linux.go:380: starting container process caused: exec: "C:/Program Files/Git/opt/mssql-tools/bin/sqlcmd":
stat C:/Program Files/Git/opt/mssql-tools/bin/sqlcmd: no such file or directory: unknown

I use Git-bash and it’s necessary to add winpty when -it option is specified. The reason why C:/Program Files/Git was added is because my PC is windows. To solve this problem we need to add additional slash //opt/mssql-tools/bin/sqlcmd.

$ winpty docker exec -it sqlserver-2019 //opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..

I found a mistake in the Dockerfile. SA_PASSWORD was A_PASSWORD (the contents above was already fixed). I didn’t try but if the password doesn’t match the requirement I think the same message is shown. Following is the requirement.

A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

https://hub.docker.com/_/microsoft-mssql-server

I got another error messages after rebuilding it.

$ docker run -d --name sqlserver-2019 sqlserver:2019-cu11
e3b18f0944686da0ca2f291430a9d30b6b8fbc7e8cbcf451f4ef22c4d6a98d61

$ winpty docker exec -it sqlserver-2019 //opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

But it worked when I tried it again. I guess the server hadn’t started up yet. Let’s login and check the existing tables.

$ winpty docker exec -it sqlserver-2019 //opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
1> select name from sysobjects where xtype = 'U';
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
trace_xe_action_map
trace_xe_event_map
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
MSreplication_options

(7 rows affected)

Use compose file to mount a host directory

The data that we create will be deleted without Docker volume when the container is deleted. Let’s mount a host directory to make the data persistent. It’s possible to set them all on a command line but I don’t like long command. Therefore, I created docker-compose.yml file. The Dockerfile created above is actually unnecessary if using this compose file. Original file exists here.

version: "3.7"

services: 

    sql-server:
        image: mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-20.04
        container_name: sql-server-2019
        environment:
            - ACCEPT_EULA=Y
            - SA_PASSWORD=passworD142!
            - MSSQL_PID=Developer
        ports: 
            - "1433:1433"
        volumes: 
            - ./data/data:/var/opt/mssql/data
            - ./data/log:/var/opt/mssql/log
            - ./data/secrets:/var/opt/mssql/secrets

volumes section in this file mounts host directory to the container. Those 3 directories need to be mounted.

Local Container
/data/data /var/opt/mssql/data
/data/log /var/opt/mssql/log
/data/secrets /var/opt/mssql/secrets

Connect with SQL Server Management Studio

It’s hard to do everything on command line. I downloaded and installed SSMS (SQL Server Management Studio).

Download SQL Server Management Studio (SSMS) — SQL Server Management Studio (SSMS)

Download the latest version of SQL Server Management Studio (SSMS).

Connect to the SQL server in Docker container

Open Microsoft SQL Server Management Studio 18 and input connection data. If SQL server is running on your host machine you may be able to connect to the server running in Docker. Stop the SQL server running on the host machine if you cannot connect even after you change the exposed port.

ssms-login-screen

I couldn’t connect at first because the specified port number in compose file was 1443 instead of 1433. Be careful…

Create a Dabase

I didn’t change anything. All values are default except for database name.

create-database

Play with SQL

I tried to create 3 tables.

CREATE TABLE Product (
    id int NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL UNIQUE,
    price int,
);

CREATE TABLE Shop (
    id int NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL UNIQUE,
    address varchar(255) NOT NULL,
    telephoneNumber varchar(255),
);

CREATE TABLE Stock (
    shopId int FOREIGN KEY REFERENCES Shop(id),
    productId int FOREIGN KEY REFERENCES Product(id),
    price int,
);

ALTER TABLE Stock ADD count int DEFAULT 0;

Then, data insert.

INSERT INTO Product
VALUES 
    (1, 'pen-black', 5),
    (2, 'pen-white', 5),
    (3, 'Super eraser', 7),
    (4, 'Secret Notebook', 10),
    (5, 'Sketch Book', 8);

INSERT INTO Shop
VALUES
    (1, 'shop1', 'street 1', '11111'),
    (2, 'shop2', 'street 2', '22222'),
    (3, 'shop3', 'street 3', '3333333');


INSERT INTO Stock
VALUES
    (1, 2, null, 12),
    (1, 3, 20, 11),
    (1, 4, null, 5),
    (2, 2, null, 9),
    (2, 4, null, 24),
    (2, 5, 9, 30),
    (3, 1, 7, 100),
    (3, 2, 8, 78);

Check the result.

SELECT * FROM Product;
SELECT * FROM Shop;
SELECT * FROM Stock;

End

It was very easy to establish SQL server. We can establish it as many servers as we want if we change the exposed port number. Furthermore, we can easily delete the whole data and establish the clean environment for test.

Источник

Понравилась статья? Поделить с друзьями:
  • Tearing на мониторе как исправить
  • Tcp out radmin vpn как исправить
  • Teardown как изменить язык
  • Tcp optimizer error hkey local machine
  • Teardown как изменить разрешение экрана