Lesson Learned #261: Java vs Permission denied: connect

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer got the following error message using Java code:

 

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host servername.database.windows.net, port 1433 has failed. Error: "Permission denied: connect. Verify the connect

ion 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."

 

or

 

"Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host nodename.tenantring.northeurope1-a.worker.database.windows.net (redirected from servername.database.windows.

net), port 11001 has failed. Error: "Permission denied: connect. 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:2462)

at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:668)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2695)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)"

 

 

 

I would like to share with you what was my lessons learned here.

 

 

 

The first thing is to isolate the problem running the suggested Java code to see the results:

 

 

 

 

 

 

 

package testconnectionms;

 

import java.sql.*;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

import javax.lang.model.util.ElementScanner6;

import javax.sql.DataSource;

 

/**

* Simple connection test.

*

*/

public class SQLTest

{

static {

try {

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

} catch (Exception ex) {

System.err.println("Unable to load JDBC driver");

ex.printStackTrace();

System.exit(1);

}

}

 

public static void main(String[] args)

throws SQLException

{

String username = "username";

String password = "Password";

String envname = "env";

String seconds = "10";

 

System.out.println("Arguements are: username="+username+",password="+password+",envname="+envname+",seconds="+seconds);

 

String url = String.format("jdbc:sqlserver://servername.database.windows.net:1433;database=dbname;sslProtocol=TLSv1.2", envname,envname);

 

System.out.println("-- Connecting to " + url);

 

long start = System.currentTimeMillis();

Connection connection = DriverManager.getConnection(url, username, password);

 

if (false) {

 

}

 

final long time1 = System.currentTimeMillis();

long time2 = time1;

long cnt = 0;

Integer secInt = Integer.valueOf(seconds);

 

PreparedStatement st = connection.prepareStatement("SELECT * from demo");

st.setQueryTimeout(20);

while (time1 + (secInt.intValue() * 1000) >= time2)

{

cnt++;

 

 

ResultSet rs = st.executeQuery();

rs.next();

rs.close();

 

 

time2 = System.currentTimeMillis();

}

st.close();

 

Long statementsCount = Long.valueOf(cnt);

Long statementsPerSecond = Long.valueOf(cnt / ((time2 - time1) / 1000));

Double timePerStatement = Double.valueOf(1000.0 / (cnt / ((time2 - time1) / 1000.0)));

 

System.out.println("statementsCount="+statementsCount);

System.out.println("statementsPerSecond="+statementsPerSecond);

System.out.println("timePerStatement="+timePerStatement);

}

 

private static void usage(String msg)

{

System.err.println("Usage: SQLTest -username username -password password -envname envname -seconds seconds");

System.err.println(msg);

}

}

 

 

 

 

 

 

 

The error message: Permission denied: connect reports that when when trying to open the port any firewall/NSG is blocking this outbound port, either, 1433 or redirect port 11000-12000.

 

 

 

If the issue is regarding with port 1433 means that the initial connection with port 1433 is rejecting, if the error message any port from 11000-12000 means that the issue is rejecting any of this port.

 

 

 

As a workaround, if your customer only has the port 1433 as outbound open, please, change the connection policy to Proxy meanwhile their networking team is opening as outbound connection from 11000-12000.

 

 

 

For reference: Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Micr...

 

 

 

Enjoy!

 

Continue reading...