SQLException while connecting to Azure SQL Data Warehouse using JDBC

Question

I am trying to connect to Azure SQL Data Warehouse through JDBC. I am getting the following exception.

*

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host tcsqldatawh.database.windows.net, port 1433 has failed. Error: "null. 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:191)
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:242)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2280)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:493)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1387)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1068)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:904)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:451)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1014)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at testsqldw.SQLDatabaseConnection.main(SQLDatabaseConnection.java:30)

*

I have seen similar questions asked about connecting to the SQLServer DB here.

I configured the database to allow access to my IP using the process here.

Please check the code below:

package testsqldw;

import java.sql.*;  

    public class SQLDatabaseConnection {  

        // Connect to your database.  
        // Replace server name, username, and password with your credentials  
        public static void main(String[] args) {  


            String connectionString =  
                    "jdbc:sqlserver://databaseserver.database.windows.net:1433;"
                    +"database=databaseserver;"
                    +"user=username@databaseserver;"
                    + "password=password;"
                    + "encrypt=true;"
                    + "trustServerCertificate=false;"
                    + "hostNameInCertificate=*.database.windows.net;"
                    + "loginTimeout=30;";

            System.out.println("Total connection string is---\n"+connectionString);

            // Declare the JDBC objects.  
            Connection connection = null;  
            Statement statement = null;   
            ResultSet resultSet = null;  

            try {  
                connection = DriverManager.getConnection(connectionString);  

                // Create and execute a SELECT SQL statement.  
                String createSql = "create table employee(employee_id varchar(20));";  
                statement = connection.createStatement();  
                boolean status=statement.execute(createSql);

                System.out.println(status);

                // Print results from select statement  

            }  
            catch (Exception e) {  
                e.printStackTrace();  
            }  
            finally {  
                // Close the connections after the data has been handled.  

                if (statement != null) try { statement.close(); } catch(Exception e) {}  
                if (connection != null) try { connection.close(); } catch(Exception e) {}  
            }  
        }  
    }

Total connection string is

jdbc:sqlserver://databaseserver.database.windows.net:1433;database=databaseserver;user=username@databaseserver;password=password;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Please help me in resolving the issue.

johnny johnny · Accepted Answer · 2016-09-28T09:11:03

Per my experience, the issue may be caused by the following reasons:

The SQL Server which you have created may not work fine.
You may use incorrect JDBC connection string.

For the first reason, you could use some client tools to connect to the SQL server. If you could connect to the server, that indicates the SQL server is ok. If not ,you could create a new SQL Server. After then, you could create a new SQL Data Warehouse according to this URL https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-get-started-provision/. The URL also includes the firewall config method.

I use the SQL Server InTouch to connect to the SQL Server. The followings are some description images.

You could get the parameters by the Azure Portal. The port number is 1433.

The following picture indicates that your server is ok.

For the second reason, you could copy the connection string from the azure portal and modify the password only.

Hope it helps. Any concerns, please feel free to let me know.

SQLException while connecting to Azure SQL Data Warehouse using JDBC

1 Answers