如何在ZATO下正确配置与TLS的传出SQL连接？

Question

我将zato esb用于个人项目/实验，并在通过de > SQL连接(没有TLS加密.)创建和查询SQL数据库时取得了成功。

但是，我试图让托管在azure上的托管MSSQL正常工作，但无法正确地连接。在我看来，这是由于设置了一些关于TLS证书或与TLS相关的东西。

更正:我能够(在Windows下)使用pytds直接在python中使用以下方法打开连接：

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

但是，Docker内部的相同命令(我正在运行Zato)将显示与Zato中相同的错误。

我的Zato sql连接看起来像

​

我把这个问题追溯到pytds/tls.py中的双重异常，但是我不知道为什么会出现这种情况。

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...

任何帮助都是非常感谢的。

诚挚的问候

Answer 1

我终于让它起作用了。

我刚把最新的小矮人扔进了容器，就这样！

的码头形象需要更新..。

向亚历克斯问好

Answer 2

Azure SQL数据库需要TLS。为Server使用微软支持的连接库(如pyodbc+Microsoft ODBC驱动程序)，并在这里遵循TLS说明：在Linux上加密到Server的连接。