使用Azure SSO通过JDBC连接私有Snowflake数据库时认证失败

问题描述 #

使用Azure SSO凭证可在浏览器正常登录Snowflake，但通过Java JDBC代码连接时，抛出用户名/密码错误的认证异常。

Java代码 #

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)

解决方案 #

• 核心原因：浏览器的Azure SSO是基于OAuth2.0的跳转认证流程，而JDBC代码里直接传user和password走的是Snowflake原生密码认证，两者不兼容，因此会触发认证失败。

• 调整认证方式：

  1. 外部浏览器认证（交互式）：
     修改连接属性，移除user和password，添加authenticator=externalbrowser，代码调整如下：

     private static Connection getConnection() throws SQLException {
         try {
             Class.forName("net.snowflake.client.jdbc.SnowflakeDriver");
         } catch (ClassNotFoundException ex) {
             System.err.println("Driver not found");
         }
         Properties properties = new Properties();
         properties.put("account", "R_E_ACC");
         properties.put("db", "B_P_DB");
         properties.put("schema", "T_ADHOC");
         properties.put("authenticator", "externalbrowser"); // 启用外部浏览器SSO认证
     
         String connectStr = System.getenv("SF_JDBC_CONNECT_STRING");
         if (connectStr == null) {
             connectStr = "jdbc:snowflake://domain.privatelink.snowflakecomputing.com";
         }
         return DriverManager.getConnection(connectStr, properties);
     }
     

     运行代码时会自动弹出浏览器，完成Azure SSO认证后即可建立连接。

  2. OAuth令牌认证（无交互式）：
     如果需要无交互运行，先通过Azure AD获取有效OAuth令牌，然后在连接属性中设置：

     properties.put("authenticator", "oauth");
     properties.put("token", "<你的Azure AD OAuth令牌>");
     

• 额外检查项：

  • 确认JDBC连接字符串的privatelink域名与浏览器访问的Snowflake实例完全一致
  • 升级Snowflake JDBC驱动到最新稳定版，旧版本可能存在SSO认证兼容问题

内容的提问来源于stack exchange，提问作者Mr.R