JDBC Query - Not able to connect to sql server database in Azure VM

asked 2018-06-28 21:41:18 -0500

aravikumar gravatar image

We are trying to use streamsets on a ubuntu based Azure VM to connect to SQL server database on an Azure VM using jdbc connector(mssql-jdbc-6.4.0.jre.jar).

We get the below error message while trying to connect to the sql server database

QUERY_EXECUTOR_002 - Can't open connection: JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.PoolInitializationException: Exception during pool initialization: The TCP/IP connection to the <hostname>, port 1433 has failed. Error: "<hostname>. 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.".

We are able to connect to the PaaS instance Azure SQL server database (.windows.database.net) after adding the below line to java.policy file in $JAVA_HOME/jre/lib/security directory

permission java.net.SocketPermission "a55a3fbdc1b3.tr2.australiasoutheast1-a.worker.database.windows.net:11029", "connect,resolve";

Details 1) Have added below entries as per config doc to $JAVA_HOME/jre/lib/security/java.policy file (restarted streamsets)

    permission java.net.SocketPermission "<hostname>:1433", "connect,resolve";
    permission java.util.PropertyPermission "java.home", "read";
    permission java.util.PropertyPermission "java.specification.version", "read";

2) Have added below entries to provide permission for external libraries in sdc-security.policy file (restarted streamsets)

    // For JARs to be available to all stage libraries
    grant codebase "file://${sdc.dist.dir}/libs-common-lib/-" {
    permission java.security.AllPermission;
    };
   // user defined external libraries
   grant codebase "file:///opt/sdc-extras/-" {
  permission java.security.AllPermission;
   };

3) SQL server login credentials have been verfied and is able to login using different tools.

4) jdbc connector (mssql-jdbc-6.4.0.jre.jar) is able to connect to SQL server DB using different tools

5) nc -v -z -w2 <ipaddress of="" sql="" server="" vm=""> 1433 returned "Connection to <ipaddress> 1433 port [tcp/ms-sql-s] succeeded!"

6) below jdbc connection url is used. Which throws the error mentioned above

        jdbc:sqlserver://<hostname>:1433;database=dw;user=<username>;password=<password>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=<hostname>;loginTimeout=30;

7) was able to connect to PaaS instance Azure SQL server database using below jdbc connection url

   jdbc:sqlserver://<hostname>.windows.database.net:1433;database=dw;user=<username>;password=<password>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.windows.database.net;loginTimeout=30;

Has anyone encountered this issue before ? We are more than happy to try out any idea. Please help!

edit retag flag offensive close merge delete