How to correctly configure an outgoing SQL connection WITH TLS under ZATO?-CodePudding

I'm using zato esb for a personal project/experiment and had success when creating and querying SQL databases via de outgoing->SQL connection (with no TLS encryption....)

However I'm trying to get a managed MSSQL hosted on azure to work and can't get the connection right. In my belief this due to some setup regarding the TLS certificate or something related to TLS.

Correction: I was able (under Windows) to open a connection directly in python using pytds using:

 pytds.connect(dsn='url_of_instance.database.windows.net',database='khronos', user='khronos', password='*******', cafile='/opt/zato/ca-cert.pem',validate_host=False)

However the same command inside Docker (where I'm running Zato) will exhibit the same error as in Zato...

My Zato sql connection looks like enter image description here

I traced the problem down to a double exception in pytds/tls.py, however I can't know why that happens.

2022-08-26 18:00:39,572 - WARNING - 10940:Dummy-23 - zato.outgoing.sql.ping:0 - SQL connection `khronos` could not be pinged, e:`Traceback (most recent call last):
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/tls.py", line 119, in establish_channel
    conn.do_handshake()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/OpenSSL/SSL.py", line 1894, in do_handshake
    self._raise_ssl_error(self._ssl, result)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/OpenSSL/SSL.py", line 1607, in _raise_ssl_error
    raise WantReadError()
OpenSSL.SSL.WantReadError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/tls.py", line 125, in establish_channel
    req = conn.bio_read(BUFSIZE)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/OpenSSL/SSL.py", line 1848, in bio_read
    self._handle_bio_errors(self._from_ssl, result)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/OpenSSL/SSL.py", line 1813, in _handle_bio_errors
    raise WantReadError()
OpenSSL.SSL.WantReadError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/zato/3.2.0/code/zato-server/src/zato/server/service/internal/outgoing/sql.py", line 240, in handle
    response_time = ping(self.server.fs_sql_config)
  File "/opt/zato/3.2.0/code/zato-common/src/zato/common/odb/api.py", line 361, in ping
    func(*args)
  File "/opt/zato/3.2.0/code/zato-common/src/zato/common/mssql_direct.py", line 123, in ping
    return self.execute(self.ping_query)
  File "/opt/zato/3.2.0/code/zato-common/src/zato/common/mssql_direct.py", line 112, in execute
    conn = self.connect()
  File "/opt/zato/3.2.0/code/zato-common/src/zato/common/mssql_direct.py", line 100, in connect
    return self._pool.connect(**self._connect_kwargs)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/dbapi_proxy.py", line 135, in connect
    return self.get_pool(*args, **kw).connect()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 264, in <lambda>
    return lambda crec: creator()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/sqlalchemy/pool/dbapi_proxy.py", line 112, in <lambda>
    lambda: self.module.connect(*args, **kw), **self.kw
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/__init__.py", line 1196, in connect
    conn._open()
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/__init__.py", line 349, in _open
    raise last_error
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/__init__.py", line 302, in _open
    conn.login(login, sock, self._tzinfo_factory)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/tds.py", line 1712, in login
    self._main_session.process_prelogin(login)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/tds.py", line 1360, in process_prelogin
    tls.establish_channel(self)
  File "/opt/zato/3.2.0/code/lib/python3.8/site-packages/pytds/tls.py", line 147, in establish_channel
    raise Exception("##ALEX## double exception...")
Exception: ##ALEX## double exception...

Any help is greatly appreciated

best regards

CodePudding user response：

I finally got it working.

I just smashed the latest pytds lib into the container and that's it!

Zato's Docker image need a refresh...

Best regards Alex

CodePudding user response：

Azure SQL Database requires TLS. Use a Microsoft-supported connection library like pyodbc Microsoft ODBC Driver for SQL Server, and follow the TLS directions here: Encrypting Connections to SQL Server on Linux