Play framework java connection is not available to sql server database

Question

I'm stucked in a problem during connection from my java project (play framework ) to a sql server database in Azure.

In local enviroment the connection works fine.

Below parameter in my application.conf :

db.default {
     url="jdbc:sqlserver://server-name.database.windows.net:1433;database=database-name"
     encrypt=true
     trustServerCertificate=false
     hostNameInCertificate="*.database.windows.net"
     loginTimeout=30
     driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
     username="root"
     password="RootPwd"
}

This is the error received:

ERROR o.h.e.jdbc.spi.SqlExceptionHelper HikariPool-1 - Connection is not available, request timed out after 30006ms. ERROR o.h.e.jdbc.spi.SqlExceptionHelper The TCP/IP connection to the host par-sql-server.database.windows.net, port 1433 has failed. Error: "par-sql-server.database.windows.net. 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.". ERROR p.api.http.DefaultHttpErrorHandler

play.api.PlayException: Execution exception[[CompletionException: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection]] at play.api.http.HttpErrorHandlerExceptions$.$anonfun$convertToPlayException$3(HttpErrorHandler.scala:388) at scala.Option.getOrElse(Option.scala:201) at play.api.http.HttpErrorHandlerExceptions$.convertToPlayException(HttpErrorHandler.scala:388) at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:373) at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:264) at play.core.server.AkkaHttp

I have already checked that tcp/ip port 1433 is correctly available for database, and my ip is correctly setted to avoid firewall.

Any suggestions? thanks

Answer 1

Usually this error occurs when the database is not getting the connection within the default connectionTimeout property.

You can try to increase the value of connectionTimeout in Java config as below:

config.setConnectionTimeout(300000);

below is one of the sample config:

HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setConnectionTimeout(300000);
        config.setConnectionTimeout(120000);
        config.setLeakDetectionThreshold(300000);

If possible let me know the steps to reproduce this issue so that I can fix it accordingly, also based on the information you've provided these blogs (Blog1, Blog2) will help you in establishing the connection