如何通过Spring Boot XML配置+HikariCP连接带私钥认证的Snowflake JDBC

问题场景 #

使用HikariCP连接池通过JDBC连接Snowflake，采用Spring XML配置Bean，私钥认证方式，运行时抛出Missing password错误。

配置文件 #

snowflake.url=jdbc:snowflake://{account_name}.snowflakecomputing.com/?warehouse=${snowflake.warehouse}&db=${snowflake.database}&schema=${snowflake.schema}&role=${snowflake.role}

Spring XML配置 #

<bean id="dbUtil" class="utilities.DbUtils">
    <constructor-arg>
        <bean id="snowflakeDataSource" class="com.zaxxer.hikari.HikariDataSource">
            <property name="driverClassName" value="net.snowflake.client.jdbc.SnowflakeDriver"/>
            <property name="jdbcUrl" value="${snowflake.url}"/>
            <property name="username" value="${snowflake.user}"/>
            <property name="dataSourceProperties">
                <props>
                    <prop key="privateKeyFile">${snowflake.privateKeyPath}</prop>
                    <prop key="privateKeyPassphrase">${snowflake.privateKeyPassphrase}</prop>
                </props>
            </property>
        </bean>
    </constructor-arg>
</bean>

报错信息 #

HikariPool-1 - Exception during pool initialization.

net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Missing password.
at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initialize(DefaultSFConnectionHandler.java:117)
at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initializeConnection(DefaultSFConnectionHandler.java:85)
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:180)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:100)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
at org.apache.commons.dbutils.AbstractQueryRunner.prepareConnection(AbstractQueryRunner.java:319)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:327)

解决方案 #

原因分析 #

HikariCP的dataSourceProperties配置无法被Snowflake JDBC驱动直接识别，驱动仍默认等待密码输入，而私钥认证的参数需要直接通过JDBC URL传递才能被正确解析。

解决步骤 #

1. 将用户名移至JDBC URL
   修改配置文件的URL，添加用户参数，同时删除Spring XML中单独的username配置项：

   snowflake.url=jdbc:snowflake://{account_name}.snowflakecomputing.com/?user=${snowflake.user}&warehouse=${snowflake.warehouse}&db=${snowflake.database}&schema=${snowflake.schema}&role=${snowflake.role}
   

2. 将私钥参数附加到JDBC URL
   把私钥文件路径、密码短语直接加入URL，删除Spring XML中的dataSourceProperties配置块：

   snowflake.url=jdbc:snowflake://{account_name}.snowflakecomputing.com/?user=${snowflake.user}&warehouse=${snowflake.warehouse}&db=${snowflake.database}&schema=${snowflake.schema}&role=${snowflake.role}&privateKeyFile=${snowflake.privateKeyPath}&privateKeyPassphrase=${snowflake.privateKeyPassphrase}
   

3. 最终Spring XML配置

   <bean id="dbUtil" class="utilities.DbUtils">
       <constructor-arg>
           <bean id="snowflakeDataSource" class="com.zaxxer.hikari.HikariDataSource">
               <property name="driverClassName" value="net.snowflake.client.jdbc.SnowflakeDriver"/>
               <property name="jdbcUrl" value="${snowflake.url}"/>
           </bean>
       </constructor-arg>
   </bean>
   

补充说明 #

如果私钥没有设置密码短语，可直接移除&privateKeyPassphrase=${snowflake.privateKeyPassphrase}参数。同时要确保私钥文件路径正确，应用进程拥有该文件的读取权限。

内容的提问来源于stack exchange，提问作者Saravanan