Valid Private Key Buffer rejected with error "Please provide a valid unencrypted rsa private key in DER format as bytes object"

[jbruce1-ocrolus]

When initializing a snowflake SQLalchemy URL no combination of patterns to pass the key pair auth private key buffer to the URL method works.

I've validated that the private key buffer is valid via the connector pattern below.

snowflake.connector.connect(
            user=self.username,
            password=self.password,
            private_key=pkb,
            account=self.account,
            role=self.role,
            warehouse=self.warehouse,
            database=self.database,
            schema=self.schema,
        )

Part of the system utilizes the snowflake sql alchemy library so initializing with the same private key buffer and using the method described here

The connection library complains that there's no password, upon looking at the snowflake/connector/connection.py it appears that private_key is in fact a parameter that's supported in contradiction of the documentation. Not only that is the private_key parameter supported it's required in order for the library to process the private key buffer rather than attempt to use basic auth.

When using this pattern the following exception is raised.

  File "pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 1082, in _authenticate
    auth_instance.prepare(
  File "pip_snowflake_connector_python/site-packages/snowflake/connector/auth/keypair.py", line 110, in prepare
      raise ProgrammingError(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 251008: Failed to load private key: from_buffer() cannot return the address of a unicode object
Please provide a valid unencrypted rsa private key in DER format as bytes object

Leading me to believe there's some issue with the logic in the SQL alchemy auth connector preparation method.

I know the snowflake/sqlalchemy uses snowflake-connector under the hood so upon examining the snowflake/connector/auth/keypair.py when this line is observed with the same exact PKB input via snowflake.connector.connect and URL methods respectively different values are observed.

This leads me to believe that the library is augmenting the passed PKB

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   python 3.10

2. What operating system and processor architecture are you using?

macOS-13.3.1-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)?

   Replace with the output of python -m pip freeze

4. What did you do?

from snowflake.sqlalchemy import URL

from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization

def function():
        pk = serialization.load_pem_private_key(
            self.private_key.encode(), # string literal
            self.private_key_passphrase.encode(), # string literal
            backend=default_backend(),
        )
        pkb = pk.private_bytes(
            encoding=serialization.Encoding.DER,
            format=serialization.PrivateFormat.PKCS8,
            encryption_algorithm=serialization.NoEncryption(),
        )

        return URL(
            account=self.account,
            user=self.username,
            database=self.database,
            warehouse=self.warehouse,
            role=self.role,
            private_key=pkb,
        )

5. What did you expect to see?

   Functional connection URL.

6. Can you set logging to DEBUG and collect the logs?

2023-07-19 11:38:46,882 - MainThread connection.py:280 - __init__() - INFO - Snowflake Connector for Python Version: 2.9.0, Python Version: 3.10.9, Platform: macOS-13.3.1-x86_64-i386-64bit
2023-07-19 11:38:46,882 - MainThread connection.py:536 - connect() - DEBUG - connect
2023-07-19 11:38:46,882 - MainThread connection.py:832 - __config() - DEBUG - __config
2023-07-19 11:38:46,883 - MainThread connection.py:965 - __config() - INFO - 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.
2023-07-19 11:38:46,883 - MainThread connection.py:983 - __config() - INFO - Setting use_openssl_only mode to False
2023-07-19 11:38:46,883 - MainThread converter.py:145 - __init__() - DEBUG - use_numpy: False
2023-07-19 11:38:46,883 - MainThread connection.py:729 - __open_connection() - DEBUG - REST API object was created: fua62330.us-east-1.snowflakecomputing.com:443
Traceback (most recent call last):
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/auth/keypair.py", line 104, in prepare
    private_key = load_der_private_key(
  File "  /pip_cryptography/site-packages/cryptography/hazmat/primitives/serialization/base.py", line 48, in load_der_private_key
    return ossl.load_der_private_key(data, password)
  File "  /pip_cryptography/site-packages/cryptography/hazmat/backends/openssl/backend.py", line 989, in load_der_private_key
    bio_data = self._bytes_to_bio(data)
  File "  /pip_cryptography/site-packages/cryptography/hazmat/backends/openssl/backend.py", line 610, in _bytes_to_bio
    data_ptr = self._ffi.from_buffer(data)
TypeError: from_buffer() cannot return the address of a unicode object

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 690, in __connect
    with util.safe_reraise():
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/__init__.py", line 51, in Connect
    return SnowflakeConnection(**kwargs)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 302, in __init__
    self.connect(**kwargs)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 566, in connect
    self.__open_connection()
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 819, in __open_connection
    self.authenticate_with_retry(self.auth_class)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 1075, in authenticate_with_retry
    self._authenticate(auth_instance)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 1082, in _authenticate
    auth_instance.prepare(
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/auth/keypair.py", line 110, in prepare
    raise ProgrammingError(
snowflake.connector.errors.ProgrammingError: 251008: 251008: Failed to load private key: from_buffer() cannot return the address of a unicode object
Please provide a valid unencrypted rsa private key in DER format as bytes object

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "  /snapshot_service/repo/__sf.py", line 130, in <module>
    get_snowflake_clients()
  File "  /snapshot_service/repo/__sf.py", line 122, in get_snowflake_clients
    return sf_client.get_dataframe_from_query(text(query))
  File "  /snapshot_service/schema/sf_client.py", line 49, in get_dataframe_from_query
    return pd.DataFrame(sf_session.execute(query).fetchall())
  File "<string>", line 2, in execute
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/orm/session.py", line 1713, in execute
    conn = self._connection_for_bind(bind)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/orm/session.py", line 1552, in _connection_for_bind
    return self._transaction._connection_for_bind(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 3315, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 493, in checkout
    rec = pool._do_get()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
    return _ConnectionRecord(self)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 388, in __init__
    self.__connect()
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 690, in __connect
    with util.safe_reraise():
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
  File "  /pip_sqlalchemy/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/__init__.py", line 51, in Connect
    return SnowflakeConnection(**kwargs)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 302, in __init__
    self.connect(**kwargs)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 566, in connect
    self.__open_connection()
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 819, in __open_connection
    self.authenticate_with_retry(self.auth_class)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 1075, in authenticate_with_retry
    self._authenticate(auth_instance)
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/connection.py", line 1082, in _authenticate
    auth_instance.prepare(
  File "  /pip_snowflake_connector_python/site-packages/snowflake/connector/auth/keypair.py", line 110, in prepare
    raise ProgrammingError(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 251008: 251008: Failed to load private key: from_buffer() cannot return the address of a unicode object
Please provide a valid unencrypted rsa private key in DER format as bytes object
2023-07-19 11:38:46,981 - MainThread connection.py:581 - close() - INFO - closed
2023-07-19 11:38:46,981 - MainThread connection.py:597 - close() - DEBUG - Exception encountered in closing connection. ignoring...: 'SnowflakeConnection' object has no attribute '_telemetry'

[sfc-gh-dszmolka]

hi and thanks for raising this issue. We now seem to have this documented at https://docs.snowflake.com/en/developer-guide/python-connector/sqlalchemy#key-pair-authentication-support .
let us know please in a comment if any further help is needed.