Can't Connect to External SQL Server From Docker Container

user_7576002 0
Viewed 5788

I've developed a SpringBoot(Java) application that calls out to an external SQL Server on port 1433. The SQL Server instance is located on-premises (not local SQL Server instances). However, it's reachable from my desktop using either IntelliJ or SQL Clients.

I am using the Microsoft SQL Server JDBC connector to communicate with the instances.

If I run the app from IntelliJ all is well, the app can call the SQL Server, execute the command and returns a resultsset.

However, now I'm trying to Dockerize the api app. The container does the usual SpringBoot initialization but when it tries to call the SQL Server I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host myexternalsqlserver.domain, port 1433 has failed. Error: "myexternalsqlserver.domain. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2434)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:659)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2546)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2216)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2067)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1204)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:825)
    at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
    at com.symetra.SdsApi.SqlConnector.getResultSet(SqlConnector.java:26)

This is my Docker file

FROM openjdk:11.0.4-jre-slim-buster
VOLUME /tmp

COPY target/myapi-1.0-SNAPSHOT.jar app.jar
EXPOSE 8080
EXPOSE 1433
ENTRYPOINT [ "sh", "-c", "java $JAVA_OPTS -Djava.security.egd=file:/dev/./urandom -Dspring.profiles.active=docker -jar /app.jar" ]

And these are the Docker commands I use to build and run the container:

docker build -t myapi . && docker run -p 8080:8080 -p 1433:1433 --name myapi myapi "java","-jar","myapi-1.0-SNAPSHOT.jar"

I don't think the mapping of port 1433 is necessary, it's just an experiment. I should be able to communicate outside of my container through to port 1433 on the host. Port 1433 isn't being blocked on the host because I have no problems running this outside the container.

Finally, this is the connection string I'm using:

//Create Connection Url.
String connectionUrl="jdbc:sqlserver://myexternalsqlserver.domain:1433;database=mydb;user=MyUser;password=MyPassword";

I'm not sure what I'm doing wrong here. I'm wondering if I need to set up Docker networking.

Thanks for your help!

spring-boot docker intellij-idea connection sql-server
2 Answers

It should work.

NB I'm assuming myexternalsqlserver.domain is not what you're using.

One way to test your container is to shell into it (or create a variant) and try resolving the SQL Server's host name:

docker run --interactive --tty openjdk:11.0.4-jre-slim-buster /bin/bash

# then from within the container's shell
apt update && apt install -y dnsutils
nslookup ${SQL_SERVER}

If that succeeds, it's your code.

If not, it's the network.

NB Your container need not publish 1443 (--publish=1433:1433) since it's consuming that port (on the SQL Server) not exposing the port itself.

user_6092900

The same issue bugged me for days and finally was able to solve it by following the steps below.

In my case the SQL server was hosted on Azure managed instance and exposed a private endpoint. The docker was able to resolve the DNS name of the Azure managed instance to a private IP address (as expected ). But this resolved IP was considered by the docker network/bridge as a locally running application because upon inspecting docker's bridge (docker network inspect bridge)The subnet of the bridge was found to be a range of IP addresses where the resolved IP of the SQL server also falls. Hence, the docker assumed the SQL server to be hosted in the same local network and not outside (here enterprise network). In my case the Windows VM I was working was already on the private network of the enterprise.

Solution:

In the docker desktop -> settings -> Docker Engine json file, add a new key value pair "bip": "<local IP address range which is outside of the resolved sql server IP>". Restart the docker desktop and the issue should be solved.

user_80708990
Related