davlee1972 opened a new issue, #1422:
URL: https://github.com/apache/arrow-adbc/issues/1422
I'm trying to connect via python to Snowflake using a P8 encrypted key file,
but haven't had any success with either an encrypted or unencrypted file. I
looked through the code and I didn't see many options to increase Go
debugging/tracing to help me figure out what isn't working..
-----BEGIN ENCRYPTED PRIVATE KEY-----
or
-----BEGIN RSA PRIVATE KEY-----
With snowsql I've was able to use the rsa_key.p8 encrypted key file to
successfully connect. I verified both the fingerprint and decrypted jwt token
were also valid within Snowflake user settings and formatting:
```
snowsql --private-key-path rsa_key.p8 -a
my_privatelink.snowflakecomputing.com -u MY_LOGIN -s MY_SCHEMA -d MY_DB -r
MY_ROLE -w MY_WAREHOUSE -o log_level=DEBUG -o log_file=c:\temp\snow.log
Private Key Passphrase:
* SnowSQL * v1.2.30
Type SQL statements or !help
MY_LOGIN#MY_WAREHOUSE@MY_DB.MY_SCHEMA>
log file:
2024-01-02 19:25:21,160 (19000/MainThread) snowflake.connector.connection
INFO connection:1163 - This connection is in OCSP Fail Open Mode. TLS
Certificates would be checked for validity and revocation status. Any other
Certificate Revocation related exceptions or OCSP Responder failures would be
disregarded in favor of connectivity.
2024-01-02 19:25:21,161 (19000/MainThread) snowflake.connector.converter
DEBUG converter:146 - use_numpy: False
2024-01-02 19:25:21,161 (19000/MainThread) snowflake.connector.connection
DEBUG connection:855 - REST API object was created:
my_privatelink.snowflakecomputing.com:443
2024-01-02 19:25:21,162 (19000/MainThread) snowflake.connector.connection
DEBUG connection:835 - OCSP Cache Server is updated:
http://ocsp.my_privatelink.snowflakecomputing.com/ocsp_response_cache.json
2024-01-02 19:25:21,209 (19000/MainThread) snowflake.connector.auth.keypair
DEBUG keypair:174 - Public key fingerprint is
SHA256:qvmb???????etc.etc.etc.?????????????CYlU=
2024-01-02 19:25:21,213 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:174 - authenticate
2024-01-02 19:25:21,214 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:208 - assertion content: <bound method
AuthByKeyPair.assertion_content of
<snowflake.connector.auth.keypair.AuthByKeyPair object at 0x000002D87F0A15B0>>
2024-01-02 19:25:21,215 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:211 - account=my_account, user=MY_LOGIN, database=MY_DB,
schema=MY_SCHEMA, warehouse=MY_WAREHOUSE, role=MY_ROLE,
request_id=bbc25fe1-35a7-4c52-a889-7adc2d82108e
2024-01-02 19:25:21,215 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:244 - body['data']: {'CLIENT_APP_ID': 'SnowSQL',
'CLIENT_APP_VERSION': '1.2.30', 'SVN_REVISION': None, 'ACCOUNT_NAME':
'my_account', 'LOGIN_NAME': 'MY_LOGIN', 'CLIENT_ENVIRONMENT': {'APPLICATION':
'SnowSQL', 'OS': 'Windows', 'OS_VERSION': 'Windows-10-10.0.19045-SP0',
'PYTHON_VERSION': '3.8.18', 'PYTHON_RUNTIME': 'CPython', 'PYTHON_COMPILER':
'MSC v.1916 64 bit (AMD64)', 'OCSP_MODE': 'FAIL_OPEN', 'TRACING': 10,
'LOGIN_TIMEOUT': 120, 'NETWORK_TIMEOUT': None, 'SOCKET_TIMEOUT': None},
'AUTHENTICATOR': 'SNOWFLAKE_JWT', 'TOKEN':
'****.eyJpc?????????etc.etc.etc.???????????????tWTsw', 'SESSION_PARAMETERS':
{'ABORT_DETACHED_QUERY': False, 'AUTOCOMMIT': True,
'CLIENT_SESSION_KEEP_ALIVE': False, 'CLIENT_PREFETCH_THREADS': 4}}
2024-01-02 19:25:21,218 (19000/MainThread)
snowflake.connector.vendored.urllib3.util.retry DEBUG retry:351 -
Converted retries value: 1 -> Retry(total=1, connect=None, read=None,
redirect=None, status=None)
2024-01-02 19:25:21,219 (19000/MainThread)
snowflake.connector.vendored.urllib3.util.retry DEBUG retry:351 -
Converted retries value: 1 -> Retry(total=1, connect=None, read=None,
redirect=None, status=None)
2024-01-02 19:25:21,219 (19000/MainThread) snowflake.connector.network DEBUG
network:1217 - Session status for SessionPool
'my_privatelink.snowflakecomputing.com', SessionPool 1/1 active sessions
2024-01-02 19:25:21,220 (19000/MainThread) snowflake.connector.network DEBUG
network:868 - remaining request timeout: N/A ms, retry cnt: 1
2024-01-02 19:25:21,220 (19000/MainThread) snowflake.connector.network DEBUG
network:850 - Request guid: 6d34a410-3d28-4971-b325-3e332fa7aecb
2024-01-02 19:25:21,220 (19000/MainThread) snowflake.connector.network DEBUG
network:1058 - socket timeout: 10
2024-01-02 19:25:21,225 (19000/MainThread)
snowflake.connector.vendored.urllib3.connectionpool DEBUG connectionpool:1003 -
Starting new HTTPS connection (1): my_privatelink.snowflakecomputing.com:443
2024-01-02 19:25:22,254 (19000/MainThread)
snowflake.connector.ssl_wrap_socket DEBUG ssl_wrap_socket:79 - OCSP Mode:
FAIL_OPEN, OCSP response cache file name: None
2024-01-02 19:25:22,276 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:492 - cache directory:
C:\Users\MY_LOGIN\AppData\Local\Snowflake\Caches
2024-01-02 19:25:22,278 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:530 -
ocsp_response_cache_uri:
file://C:/Users/MY_LOGIN/AppData/Local/Snowflake/Caches/ocsp_response_cache.json
2024-01-02 19:25:22,278 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:533 -
OCSP_VALIDATION_CACHE size: 208
2024-01-02 19:25:22,278 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:332 - OCSP response
cache server is enabled:
http://ocsp.my_privatelink.snowflakecomputing.com/ocsp_response_cache.json
2024-01-02 19:25:22,279 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:345 - OCSP dynamic
cache server RETRY URL:
http://ocsp.my_privatelink.snowflakecomputing.com/retry/{0}/{1}
2024-01-02 19:25:22,279 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:966 - validating
certificate: my_privatelink.snowflakecomputing.com
2024-01-02 19:25:22,280 (19000/MainThread)
snowflake.connector.ocsp_asn1crypto DEBUG ocsp_asn1crypto:385 - # of
certificates: 3
2024-01-02 19:25:22,281 (19000/MainThread)
snowflake.connector.ocsp_asn1crypto DEBUG ocsp_asn1crypto:390 - subject:
OrderedDict([('country_name', 'US'), ('state_or_province_name', 'California'),
('locality_name', 'San Mateo'), ('organization_name', 'Snowflake Inc.'),
('common_name', '*.east-us-2.my_privatelink.snowflakecomputing.com')]), issuer:
OrderedDict([('country_name', 'US'), ('organization_name', 'DigiCert Inc'),
('common_name', 'DigiCert Global G2 TLS RSA SHA256 2020 CA1')])
2024-01-02 19:25:22,283 (19000/MainThread)
snowflake.connector.ocsp_asn1crypto DEBUG ocsp_asn1crypto:390 - subject:
OrderedDict([('country_name', 'US'), ('organization_name', 'DigiCert Inc'),
('common_name', 'DigiCert Global G2 TLS RSA SHA256 2020 CA1')]), issuer:
OrderedDict([('country_name', 'US'), ('organization_name', 'DigiCert Inc'),
('organizational_unit_name', 'www.digicert.com'), ('common_name', 'DigiCert
Global Root G2')])
2024-01-02 19:25:22,285 (19000/MainThread)
snowflake.connector.ocsp_asn1crypto DEBUG ocsp_asn1crypto:390 - subject:
OrderedDict([('country_name', 'US'), ('organization_name', 'DigiCert Inc'),
('organizational_unit_name', 'www.digicert.com'), ('common_name', 'DigiCert
Global Root G2')]), issuer: OrderedDict([('country_name', 'US'),
('organization_name', 'DigiCert Inc'), ('organizational_unit_name',
'www.digicert.com'), ('common_name', 'DigiCert Global Root G2')])
2024-01-02 19:25:22,290 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:730 - hit cache for
subject: OrderedDict([('country_name', 'US'), ('state_or_province_name',
'California'), ('locality_name', 'San Mateo'), ('organization_name', 'Snowflake
Inc.'), ('common_name', '*.east-us-2.my_privatelink.snowflakecomputing.com')])
2024-01-02 19:25:22,294 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:730 - hit cache for
subject: OrderedDict([('country_name', 'US'), ('organization_name', 'DigiCert
Inc'), ('common_name', 'DigiCert Global G2 TLS RSA SHA256 2020 CA1')])
2024-01-02 19:25:22,297 (19000/MainThread)
snowflake.connector.ocsp_snowflake DEBUG ocsp_snowflake:1023 - ok
2024-01-02 19:25:22,494 (19000/MainThread)
snowflake.connector.vendored.urllib3.connectionpool DEBUG connectionpool:456 -
https://my_privatelink.snowflakecomputing.com:443 "POST
/session/v1/login-request?request_id=bbc25fe1-35a7-4c52-a889-7adc2d82108e&databaseName=MY_DB&schemaName=MY_SCHEMA&warehouse=MY_WAREHOUSE&roleName=MY_ROLE&request_guid=6d34a410-3d28-4971-b325-3e332fa7aecb
HTTP/1.1" 200 None
2024-01-02 19:25:22,496 (19000/MainThread) snowflake.connector.network DEBUG
network:1085 - SUCCESS
2024-01-02 19:25:22,496 (19000/MainThread) snowflake.connector.network DEBUG
network:1222 - Session status for SessionPool
'my_privatelink.snowflakecomputing.com', SessionPool 0/1 active sessions
2024-01-02 19:25:22,497 (19000/MainThread) snowflake.connector.network DEBUG
network:740 - ret[code] = None, after post request
2024-01-02 19:25:22,498 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:371 - completed authentication
2024-01-02 19:25:22,498 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:418 - token = ******
2024-01-02 19:25:22,498 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:424 - master_token = ******
2024-01-02 19:25:22,499 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:430 - id_token = NULL
2024-01-02 19:25:22,499 (19000/MainThread) snowflake.connector.auth._auth
DEBUG _auth:436 - mfa_token = NULL
2024-01-02 19:25:22,501 (19000/MainThread) snowflake.cli.sqlexecute DEBUG
sqlexecute:325 - adding connection: 0
2024-01-02 19:25:22,572 (19000/MainThread) snowflake.cli.clistyle DEBUG
clistyle:111 - prompt format: MY_LOGIN#MY_WAREHOUSE@MY_DB.MY_SCHEMA>, tokens:
[(Token.TOKEN_0, 'MY_LOGIN#MY_WAREHOUSE@MY_DB.MY_SCHEMA>')]
2024-01-02 19:25:22,614 (19000/Thread-1) snowflake.connector.connection
DEBUG connection:774 - cursor
2024-01-02 19:25:22,615 (19000/Thread-2) snowflake.connector.connection
DEBUG connection:774 - cursor
2024-01-02 19:25:22,615 (19000/Thread-1) snowflake.connector.cursor DEBUG
cursor:766 - executing SQL/command
2024-01-02 19:25:22,616 (19000/Thread-3) snowflake.connector.connection
DEBUG connection:774 - cursor
2024-01-02 19:25:22,617 (19000/Thread-2) snowflake.connector.cursor DEBUG
cursor:766 - executing SQL/command
2024-01-02 19:25:22,618 (19000/Thread-4) snowflake.connector.connection
DEBUG connection:774 - cursor
2024-01-02 19:25:22,620 (19000/MainThread) __main__ DEBUG eventloop:739
- Read from terminal
```
Passing the same rsa_key.p8 file with the decryption password using the
following python code gives me an invalid JWT token error with limited
debugging info:
```
with open("rsa_key.p8") as f:
pemlines = f.read()
import adbc_driver_snowflake.dbapi
db_kwargs={
"adbc.snowflake.sql.account": "MY_PRIVATELINK",
"adbc.snowflake.sql.auth_type": "auth_jwt",
"adbc.snowflake.sql.client_option.jwt_private_key_pkcs8_value": pemlines,
"adbc.snowflake.sql.client_option.jwt_private_key_pkcs8_password":
"my_decryption_password",
"adbc.snowflake.sql.client_option.jwt_expire_timeout": "24h",
"adbc.snowflake.sql.db": "MY_DB",
"adbc.snowflake.sql.client_option.tracing": "DEBUG",
"adbc.snowflake.sql.schema": "MY_SCHEMA",
"adbc.snowflake.sql.warehouse": "MY_WAREHOUSE",
"username": "MY_LOGIN",
"adbc.snowflake.sql.role": "MY_ROLE",
}
a_conn = adbc_driver_snowflake.dbapi.connect(db_kwargs=db_kwargs)
```
Returns with these errors:
```
time="2024-01-02T11:18:26-08:00" level=info msg=OpenWithConfig
func="gosnowflake.(*defaultLogger).Info" file="log.go:150"
time="2024-01-02T11:18:26-08:00" level=info msg="Authenticating via
SNOWFLAKE_JWT" func="gosnowflake.(*defaultLogger).Infof" file="log.go:106"
time="2024-01-02T11:18:26-08:00" level=info msg="PARAMS for Auth:
&map[databaseName:[MY_DB] roleName:[MY_ROLE] schemaName:[MY_SCHEMA]
warehouse:[MY_WAREHOUSE]], https, my_privatelink.snowflakecomputing.com, 443,
1m0s, SNOWFLAKE_JWT" func=gosnowflake.authenticate file="auth.go:348"
time="2024-01-02T11:18:26-08:00" level=info msg="full URL:
https://my_privatelink.snowflakecomputing.com:443/session/v1/login-request?databaseName=MY_DB&requestId=9831866f-ae88-43be-6934-2fba36eda91c&request_guid=007fcd50-7a72-4152-6653-de829f3e6ca9&roleName=MY_ROLE&schemaName=MY_SCHEMA&warehouse=MY_WAREHOUSE";
func="gosnowflake.(*defaultLogger).Infof" file="log.go:106"
time="2024-01-02T11:18:26-08:00" level=info msg="retryHTTP.totalTimeout:
1m0s" func="gosnowflake.(*retryHTTP).execute" file="retry.go:218"
time="2024-01-02T11:18:26-08:00" level=debug msg="retry count: 0"
func="gosnowflake.(*defaultLogger).Debugf" file="log.go:102"
time="2024-01-02T11:18:26-08:00" level=info msg="downloading OCSP Cache from
server
http://ocsp.my_privatelink.snowflakecomputing.com/ocsp_response_cache.json";
func="gosnowflake.(*defaultLogger).Infof" file="log.go:106"
time="2024-01-02T11:18:26-08:00" level=info msg="retryHTTP.totalTimeout: 5s"
func="gosnowflake.(*retryHTTP).execute" file="retry.go:218"
time="2024-01-02T11:18:26-08:00" level=debug msg="retry count: 0"
func="gosnowflake.(*defaultLogger).Debugf" file="log.go:102"
time="2024-01-02T11:18:26-08:00" level=debug msg="StatusCode from OCSP Cache
Server: 200\n" func="gosnowflake.(*defaultLogger).Debugf" file="log.go:102"
time="2024-01-02T11:18:26-08:00" level=debug msg="reading contents"
func="gosnowflake.(*defaultLogger).Debugf" file="log.go:102"
time="2024-01-02T11:18:27-08:00" level=info msg="Subject:
CN=*.east-us-2.privatelink.snowflakecomputing.com,O=Snowflake Inc.,L=San
Mateo,ST=California,C=US, Issuer: CN=DigiCert Global G2 TLS RSA SHA256 2020
CA1,O=DigiCert Inc,C=US\n" func="gosnowflake.(*defaultLogger).Infof"
file="log.go:106"
time="2024-01-02T11:18:27-08:00" level=info msg="Subject: CN=DigiCert Global
G2 TLS RSA SHA256 2020 CA1,O=DigiCert Inc,C=US, Issuer: CN=DigiCert Global Root
G2,OU=www.digicert.com,O=DigiCert Inc,C=US\n"
func="gosnowflake.(*defaultLogger).Infof" file="log.go:106"
time="2024-01-02T11:18:27-08:00" level=info msg="writing OCSP Response cache
file.
C:\\Users\\MY_LOGIN\\AppData\\Local\\Snowflake\\Caches\\ocsp_response_cache.json\n"
func="gosnowflake.(*defaultLogger).Infof" file="log.go:106"
time="2024-01-02T11:18:27-08:00" level=error msg="Authentication FAILED"
func="gosnowflake.(*defaultLogger).Errorln" file="log.go:242"
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"C:\Users\MY_LOGIN\Anaconda3\lib\site-packages\adbc_driver_snowflake\dbapi.py",
line 120, in connect
conn = adbc_driver_manager.AdbcConnection(db, **(conn_kwargs or {}))
File "adbc_driver_manager\\_lib.pyx", line 617, in
adbc_driver_manager._lib.AdbcConnection.__init__
File "adbc_driver_manager\\_lib.pyx", line 227, in
adbc_driver_manager._lib.check_error
adbc_driver_manager.OperationalError: IO: 390144 (08004): JWT token is
invalid. [0eb0d647-d2e3-40f9-b680-2f6181d9e699]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
