雪花jdbc驱动程序抛出net.snowflake.client.jdbc.SnowflakeSQLException:jwt令牌无效

2022-03-10 00:00:00 snowflake-cloud-data-platform java

我通过遵循this生成了RSA密钥对,并且还使用以下代码片段连接了Snowflake。
内部生成的JWT令牌似乎确实基于jwt.io有效,但是在建立连接时,客户端抛出以下错误:

net.snowflake.client.jdbc.SnowflakeSQLException: JWT token is invalid.

什么可能导致Snowflake服务器返回错误?

↓Sample Code稍加修改:

import org.bouncycastle.asn1.pkcs.PrivateKeyInfo;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.bouncycastle.openssl.PEMParser;
import org.bouncycastle.openssl.jcajce.JcaPEMKeyConverter;
import org.bouncycastle.openssl.jcajce.JceOpenSSLPKCS8DecryptorProviderBuilder;
import org.bouncycastle.operator.InputDecryptorProvider;
import org.bouncycastle.operator.OperatorCreationException;
import org.bouncycastle.pkcs.PKCS8EncryptedPrivateKeyInfo;
import org.bouncycastle.pkcs.PKCSException;

import java.io.FileReader;
import java.io.IOException;
import java.nio.file.Paths;
import java.security.PrivateKey;
import java.security.Security;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Properties;

public class TestJdbc
{
  // Path to the private key file that you generated earlier.
  private static final String PRIVATE_KEY_FILE = "/<path>/rsa_key.p8";

  public static class PrivateKeyReader
  {

    // If you generated an encrypted private key, implement this method to return
    // the passphrase for decrypting your private key.
    private static String getPrivateKeyPassphrase() {
      return "<private_key_passphrase>";
    }

    public static PrivateKey get(String filename)
            throws Exception
    {
      PrivateKeyInfo privateKeyInfo = null;
      Security.addProvider(new BouncyCastleProvider());
      // Read an object from the private key file.
      PEMParser pemParser = new PEMParser(new FileReader(Paths.get(filename).toFile()));
      Object pemObject = pemParser.readObject();
      if (pemObject instanceof PKCS8EncryptedPrivateKeyInfo) {
        // Handle the case where the private key is encrypted.
        PKCS8EncryptedPrivateKeyInfo encryptedPrivateKeyInfo = (PKCS8EncryptedPrivateKeyInfo) pemObject;
        String passphrase = getPrivateKeyPassphrase();
        InputDecryptorProvider pkcs8Prov = new JceOpenSSLPKCS8DecryptorProviderBuilder().build(passphrase.toCharArray());
        privateKeyInfo = encryptedPrivateKeyInfo.decryptPrivateKeyInfo(pkcs8Prov);
      } else if (pemObject instanceof PrivateKeyInfo) {
        // Handle the case where the private key is unencrypted.
        privateKeyInfo = (PrivateKeyInfo) pemObject;
      }
      pemParser.close();
      JcaPEMKeyConverter converter = new JcaPEMKeyConverter().setProvider(BouncyCastleProvider.PROVIDER_NAME);
      return converter.getPrivateKey(privateKeyInfo);
    }
  }

  public static void main(String[] args)
      throws Exception
  {
    String url = "jdbc:snowflake://<account>.snowflakecomputing.com";
    SnowflakeBasicDataSource ds = new SnowflakeBasicDataSource();
    ds.setWarehouse("<warehouse_name>");
    ds.setDatabaseName("<database_name>");
    ds.setSchema("<schema_name>");
    ds.setUser("<user>");
    ds.setPrivateKey(PrivateKeyReader.get(PRIVATE_KEY_FILE));
    ds.setRole("<role_name>");
    Connection conn = ds.getConnection();
    Statement stat = conn.createStatement();
    ResultSet res = stat.executeQuery("select 1");
    res.next();
    System.out.println(res.getString(1));
    conn.close();
  }
}

↓日志输出:

2021-01-21 02:26:50.442 n.s.c.core.SFSession FINE open:528 - input: server=https://<account>.snowflakecomputing.com:443/, account=<account>, user=<user>, password=****  role=null, database=<database>, schema=<schema>, warehouse=<warehouse>, validate_default_parameters=null, authenticator=SNOWFLAKE_JWT, ocsp_mode=FAIL_OPEN, passcode_in_password=null, passcode=****  private_key=(not null), use_proxy=null, proxy_host=null, proxy_port=null, proxy_user=null, proxy_password=****  disable_socks_proxy=null, application=null, app_id=JDBC, app_version=3.12.17, login_timeout=null, network_timeout=null, query_timeout=null, tracing=all, private_key_file=null, private_key_file_pwd=****  session_parameters: client_store_temporary_credential=null
2021-01-21 02:26:50.538 n.s.c.core.HttpUtil FINE executeRequestInternal:485 - Pool: [leased: 0; pending: 0; available: 2; max: 300] Executing: POST https://<account>.snowflakecomputing.com:443/session/v1/login-request?databaseName=<database>&schemaName=<schema>&warehouse=<warehouse>&requestId=351ecacf-e674-4a84-ac3c-388ca8ff84fa HTTP/1.1
2021-01-21 02:26:50.539 n.s.c.jdbc.RestRequest FINE execute:115 - Retry count: 0
2021-01-21 02:26:50.674 n.s.c.jdbc.RestRequest FINE execute:199 - HTTP response code: 200
2021-01-21 02:26:50.676 n.s.c.core.HttpUtil FINE executeRequestInternal:533 - Pool: [leased: 0; pending: 0; available: 2; max: 300] Request returned for: POST https://<account>.snowflakecomputing.com:443/session/v1/login-request?databaseName=<database>&schemaName=<schema>&warehouse=<warehouse>&requestId=351ecacf-e674-4a84-ac3c-388ca8ff84fa HTTP/1.1
2021-01-21 02:26:50.681 n.s.c.core.SessionUtil FINE newSession:582 - response = {
  "data" : {
  "nextAction" : null,
  "pwdChangeInfo" : null,
  "inFlightCtx" : null,
  "redirectUrl" : null,
  "licenseAgreementPDFFilePath" : null,
  "licenseAgreementHTMLFilePath" : null,
  "authnMethod" : "KEYPAIR",
  "oAuthSessionStorageData" : null,
  "relayState" : null
},
  "code" : "390144",
  "message" : "JWT token is invalid.",
  "success" : false,
  "headers" : null
}
2021-01-21 02:26:50.689 n.s.c.jdbc.SnowflakeSQLException FINE <init>:40 - Snowflake exception: JWT token is invalid., sqlState:08001, vendorCode:390,144, queryId:
2021-01-21 02:26:50.690 n.s.c.jdbc.SnowflakeBasicDataSource SEVERE getConnection:98 - Failed to create a connection for threeshake at jdbc:snowflake://<account>.snowflakecomputing.com: net.snowflake.client.jdbc.SnowflakeSQLException: JWT token is invalid.
2021-01-21 02:26:52.464 n.s.c.core.EventHandler FINE flushEventBuffer:424 - Flushing eventBuffer

解决方案

原来设置账户时,不能包含地域段。
假设全帐户名为";xy12345.us-East-1-gov.aws";,则必须按如下方式设置帐户属性。

ds.setAccount("xy12345")

相关文章