Open manicmonkey opened 6 years ago
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.
#!/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.
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?
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.
@dantheman999301 how did you do that?
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.
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)
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.
#!/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
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?
Using complex password fixed the issue.
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.
Garwin, is it possible to post the entire dockerfile of yours please
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?
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,
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"`
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:
ENV MSSQL_IP_ADDRESS 0.0.0.0
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..
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
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
Hi manicmonkey ! what did you do to solve it? Do you remember? tks
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:
Using complex password fixed the issue.
This fixed the issue for me
I'm also running into this issue intermittently, is the only fix to put in a sleep?
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
I had a similar problem. I was trying to execute the data seeding command from a Dockerfile. Two steps solved my problem:
sleep 15s
command to allow the SQL server image to start.localhost
or 127.0.0.1
server name, I used host.docker.internal
which allows your container to connect to the Docker host on another container.My whole Dockerfile looks like this:
FROM mcr.microsoft.com/mssql/server:2022-latest
COPY /seed-data.sql /seed-data.sql
CMD sleep 15s && /opt/mssql-tools/bin/sqlcmd -S host.docker.internal -U sa -P 'mypassword' -i seed-data.sql
The connection issues are caused due to misconfigured ENTRYPOINT
/ CMD
scripts.
By default, the sqlserver
image runs /opt/mssql/bin/sqlservr
as the main process on the container. Overriding this with ENTRYPOINT
, or by running a command which depends on SQL server will result in connection issues if not done correctly.
Microsoft provide an example on how to safely customise a db on startup. For visibility, the key components are:
Dockerfile
In the Dockerfile, copy custom scripts into the container (in this example, entrypoint.sh
and configure-db.sh
):
FROM mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04
# Bundle startup scripts
COPY ./entrypoint.sh /usr/scripts
COPY ./donfigure-db.sh /usr/scripts
# Grant permissions for our scripts to be executable
RUN chmod +x /usr/scripts/entrypoint.sh
RUN chmod +x /usr/scripts/configure-db.sh
ENTRYPOINT ["/usr/scripts/entrypoint.sh"]
entrypoint.sh
Next, the entry point file will run our setup script (configure-db.sh
) in the background and launch SQL server as the shells main process:
#!/bin/bash
# Launch startup customisations in bg:
/usr/scripts/configure-db.sh &
# Launch sql server in fg:
/opt/mssql/bin/sqlservr
configure-db.sh
Finally, our customisation script will poll SQL server (the process we just started in entrypoint.sh
) until it is ready before applying customisations (a more appropriate solution than arbitrary sleeps elsewhere on this thread):
# Wait for SQL Server to start up
# See https://github.com/microsoft/mssql-docker/blob/master/linux/preview/examples/mssql-customize/configure-db.sh
DBSTATUS=1
ERRCODE=1
i=0
while [[ $i -lt 30 && ($DBSTATUS -ne 0 || $ERRCODE -ne 0) ]]; do
i=$((i + 1))
DBSTATUS=$(/opt/mssql-tools/bin/sqlcmd -S localhost -h -1 -t 1 -U sa -P $MSSQL_SA_PASSWORD -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases")
ERRCODE=$?
sleep 1
done
# Exit the script if we do not get a successful response in time
if [[ $DBSTATUS -ne 0 || $ERRCODE -ne 0 ]]; then
echo "SQL Server took more than 30 attempts to start up or one or more databases are not in an ONLINE state."
echo "Error: $ERRCODE, DB state: $DBSTATUS"
# because this is a bg task, if we fail here, the container will still be running but our startup scripts will not be applied
# consider how you want to handle this
exit 1
fi
# Now, we can safely apply customisations with `sqlcmd`:
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -d master -Q "CREATE DATABASE HelloWorld; GO"
# alternatively, copy a `.sql` file into the container and run this instead:
# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -d master -i /usr/scripts/my-custom-startup.sql
# for more info on sqlcmd usage see
# https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16&tabs=go%2Cwindows&pivots=cs1-bash
I hope this helps :)
I am struggling to run the mssql linux docker image reliably. One of the issues which occurs intermittantly is this:
Dockerfile
entrypoint.sh
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