Setting up JDBC connection to private snowflake DB with Azure SSO UserId

Viewed 86

I am trying to connect with snowflake through JDBC connection , using Azure SSO credentials and connection properties. But I can able to connect to Snowflake DB on browser with the same credentials. Through Java code I am getting exception.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class SnowFlakeTest {

    public static void main(String[] args) throws Exception {
        // get connection
        System.out.println("Create JDBC connection");
        Connection connection = getConnection();
        System.out.println("Done creating JDBC connection");
        // create statement
        System.out.println("Create JDBC statement");
        Statement statement = connection.createStatement();
        System.out.println("Done creating JDBC statement");

        // query the data
        System.out.println("Query demo");
        ResultSet resultSet = statement.executeQuery("SELECT * FROM T_ADHOC.TABLE LIMIT 10");
        System.out.println("Metadata:");
        System.out.println("================================");
        // fetch metadata
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        System.out.println("Number of columns="
                + resultSetMetaData.getColumnCount());
        for (int colIdx = 0; colIdx < resultSetMetaData.getColumnCount();
                colIdx++) {
            System.out.println("Column " + colIdx + ": type="
                    + resultSetMetaData.getColumnTypeName(colIdx + 1));
        }
        // fetch data
        System.out.println("nData:");
        System.out.println("================================");
        int rowIdx = 0;
        while (resultSet.next()) {
            System.out.println("row " + rowIdx + ", column 0: "
                    + resultSet.getString(1));
        }
        statement.close();
    }

    private static Connection getConnection() throws SQLException {
        try {
            Class.forName("net.snowflake.client.jdbc.SnowflakeDriver");
        } catch (ClassNotFoundException ex) {
            System.err.println("Driver not found");
        }
        // build connection properties
        Properties properties = new Properties();
        properties.put("user", "UserId");     // replace "" with your username
        properties.put("password", "Password"); // replace "" with your password
        properties.put("account", "R_E_ACC");  // replace "" with your account name
        properties.put("db", "B_P_DB");       // replace "" with target database name
        properties.put("schema", "T_ADHOC");   // replace "" with target schema name
        //properties.put("tracing", "on");

        // create a new connection
        String connectStr = System.getenv("SF_JDBC_CONNECT_STRING");
        // use the default connection string if it is not set in environment
        if (connectStr == null) {
            connectStr = "jdbc:snowflake://domain.privatelink.snowflakecomputing.com"; // replace accountName with your account name
        }
        return DriverManager.getConnection(connectStr, properties);
    }

}

Create JDBC connection

Exception in thread "main" net.snowflake.client.jdbc.SnowflakeSQLException: Incorrect username or password was specified.
        at net.snowflake.client.core.SessionUtil.newSession(SessionUtil.java:681)
        at net.snowflake.client.core.SessionUtil.openSession(SessionUtil.java:286)
        at net.snowflake.client.core.SFSession.open(SFSession.java:461)
        at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initialize(DefaultSFConnectionHandler.java:104)
        at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initializeConnection(DefaultSFConnectionHandler.java:79)
        at net.snowflake.client.jdbc.SnowflakeConnectionV1.initConnectionWithImpl(SnowflakeConnectionV1.java:116)
        at net.snowflake.client.jdbc.SnowflakeConnectionV1.<init>(SnowflakeConnectionV1.java:96)
        at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:176)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
        at com.apps.sample.SnowFlakeTest.getConnection(SnowFlakeTest.java:87)
        at com.apps.sample.SnowFlakeTest.main(SnowFlakeTest.java:18) ```
2 Answers

Use authenticator=externalbrowser in the connection string for the driver or under properties add one more line as below.

properties.put("authenticator", "externalbrowser");

The issue here is that you are passing the Azure SSO credentials in the Java code but not instructing Snowflake to use the SAML for SSO. So, the user name / password is being considered as local user credentials and you are seeing the error. Here, you need to pass the value "authenticator" to instruct SF to use the SSO as the authn request and there set "externalbrowser" as the value. So, when the code will be executed, a browser window will be launched where you can input the Azure User credentials which will then be passed as a token for authentication. https://docs.snowflake.com/en/user-guide/jdbc-parameters.html

Related