search

elastic / connectors

Source code for all Elastic connectors, developed by the Search team at Elastic, and home of our Python connector development framework
https://www.elastic.co/guide/en/enterprise-search/master/index.html
Other
69 stars 121 forks source link

MS SQL Connector: Client does not have encryption enabled but it is required by server #2714

Open Gleb-Gadyatskiy opened 1 month ago

Gleb-Gadyatskiy commented 1 month ago

Bug Description

I tried to connect Elastic MS SQL Connector to a MS SQL DB and got this error:

Something went wrong while executing query. Exception: (pytds.tds_base.Error) Client does not have encryption enabled but it is required by server, enable encryption and try connecting again

Probably it is related to "encrypt=true" connection parameter, but I do not see any way how to configure it in Kibana.

To Reproduce

Steps to reproduce the behavior:

  1. Create MS SQL Connector in Kibana
  2. Start the Connector
  3. Configure the connector to point to a MS SQL DB which requires encrypted connection (JDBC connection string has "encrypt=true;")
  4. Run Sync

Expected behavior

The connector synchronize data between ES and DB

Environment

Additional context

2024-07-16 23:47:28 [FMWK][03:47:28][ERROR] [Connector id: 1om3vpABkHyk0MRPbI-v, index name: mssql-test-01, Sync job id: 34nMvpABkHyk0MRP4I-d] Can't connect to Microsoft SQL on ######.database.windows.net 2024-07-16 23:47:28 Traceback (most recent call last): 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in init 2024-07-16 23:47:28 self._dbapi_connection = engine.raw_connection() 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection 2024-07-16 23:47:28 return self.pool.connect() 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect 2024-07-16 23:47:28 return _ConnectionFairy._checkout(self) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout 2024-07-16 23:47:28 fairy = _ConnectionRecord.checkout(pool) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout 2024-07-16 23:47:28 rec = pool._do_get() 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get 2024-07-16 23:47:28 with util.safe_reraise(): 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in exit 2024-07-16 23:47:28 raise exc_value.with_traceback(exc_tb) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get 2024-07-16 23:47:28 return self._create_connection() 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection 2024-07-16 23:47:28 return _ConnectionRecord(self) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in init 2024-07-16 23:47:28 self.connect() 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 902, in connect 2024-07-16 23:47:28 with util.safe_reraise(): 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in exit 2024-07-16 23:47:28 raise exc_value.with_traceback(exc_tb) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 898, in connect 2024-07-16 23:47:28 self.dbapi_connection = connection = pool._invoke_creator(self) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 640, in connect 2024-07-16 23:47:28 return dialect.connect(*cargs, *cparams) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 580, in connect 2024-07-16 23:47:28 return self.loaded_dbapi.connect(cargs, **cparams) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/init.py", line 1352, in connect 2024-07-16 23:47:28 conn._open(sock=sock) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/init.py", line 379, in _open 2024-07-16 23:47:28 self._try_open(timeout=retry_time, sock=sock) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/init.py", line 361, in _try_open 2024-07-16 23:47:28 self._connect(host=host, port=port, instance=instance, timeout=timeout, sock=sock) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/init__.py", line 304, in _connect 2024-07-16 23:47:28 route = conn.login(login, sock, self._tzinfo_factory) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/tds.py", line 1703, in login 2024-07-16 23:47:28 self._main_session.process_prelogin(login) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/tds.py", line 1294, in process_prelogin 2024-07-16 23:47:28 self.parse_prelogin(octets=p, login=login) 2024-07-16 23:47:28 File "/app/lib/python3.10/site-packages/pytds/tds.py", line 1343, in parse_prelogin 2024-07-16 23:47:28 raise tds_base.Error('Client does not have encryption enabled but it is required by server, ' 2024-07-16 23:47:28 pytds.tds_base.Error: Client does not have encryption enabled but it is required by server, enable encryption and try connecting again

parthpuri-elastic commented 1 month ago

Hi @Gleb-Gadyatskiy, Can you please try it with SSL enabled?

Gleb-Gadyatskiy commented 1 month ago

"SSL enabled" required SSL certificate, which I do not have and which is not required when I connect to the same DB via plain JDBC

image
Gleb-Gadyatskiy commented 1 month ago

the issue is probably related to https://stackoverflow.com/questions/59307103/connect-sqlalchemy-to-encrypted-sql-server

Neither SQLAlchemy nor pymssql mention how to do this, only that SSL support is available

also https://github.com/denisenkom/pytds/issues/162

TLS: support "trust server certificate" similar to mssql connection strings

parthpuri-elastic commented 1 month ago

@Gleb-Gadyatskiy, Can you please try with Root CA for your instance. If your mssql instance is hosted on azure you can try using these CAs: https://learn.microsoft.com/en-us/azure/security/fundamentals/azure-ca-details?tabs=root-and-subordinate-cas-list.

Gleb-Gadyatskiy commented 1 month ago

Cannot make it working. I downloaded DigiCert Global Root G2 and added it to Elastic Connector image as "/etc/logstash/conf.d/DigiCertGlobalRootCA.crt". Set "/etc/logstash/conf.d/DigiCertGlobalRootCA.crt" as SSL certificate in Elastic MS SQL Connector and run Full Sync. Got error:

Something went wrong while executing query. Exception: [('x509 certificate routines', '', 'no certificate or crl found')]

Also tried /etc/logstash/conf.d/ - same error.

Full error trace:

2024-07-29 14:08:31 Traceback (most recent call last): 2024-07-29 14:08:31 File "/app/connectors/sources/mssql.py", line 481, in ping 2024-07-29 14:08:31 await self.mssql_client.ping() 2024-07-29 14:08:31 File "/app/connectors/sources/mssql.py", line 248, in ping 2024-07-29 14:08:31 return await anext( 2024-07-29 14:08:31 File "/app/connectors/sources/generic_database.py", line 110, in fetch 2024-07-29 14:08:31 async for result in _execute(): 2024-07-29 14:08:31 File "/app/connectors/utils.py", line 556, in wrapped 2024-07-29 14:08:31 raise e 2024-07-29 14:08:31 File "/app/connectors/utils.py", line 551, in wrapped 2024-07-29 14:08:31 async for item in func(*args, kwargs): 2024-07-29 14:08:31 File "/app/connectors/sources/generic_database.py", line 90, in _execute 2024-07-29 14:08:31 cursor = await cursor_func() 2024-07-29 14:08:31 File "/app/connectors/sources/mssql.py", line 233, in get_cursor 2024-07-29 14:08:31 self.connection = await loop.run_in_executor( 2024-07-29 14:08:31 File "/usr/local/lib/python3.10/concurrent/futures/thread.py", line 58, in run 2024-07-29 14:08:31 result = self.fn(*self.args, *self.kwargs) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3245, in connect 2024-07-29 14:08:31 return self._connection_cls(self) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in init 2024-07-29 14:08:31 self._dbapi_connection = engine.raw_connection() 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection 2024-07-29 14:08:31 return self.pool.connect() 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect 2024-07-29 14:08:31 return _ConnectionFairy._checkout(self) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout 2024-07-29 14:08:31 fairy = _ConnectionRecord.checkout(pool) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout 2024-07-29 14:08:31 rec = pool._do_get() 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get 2024-07-29 14:08:31 with util.safe_reraise(): 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in exit 2024-07-29 14:08:31 raise exc_value.with_traceback(exc_tb) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get 2024-07-29 14:08:31 return self._create_connection() 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection 2024-07-29 14:08:31 return _ConnectionRecord(self) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in init 2024-07-29 14:08:31 self.connect() 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 902, in connect 2024-07-29 14:08:31 with util.safe_reraise(): 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in exit 2024-07-29 14:08:31 raise exc_value.with_traceback(exc_tb) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 898, in __connect 2024-07-29 14:08:31 self.dbapi_connection = connection = pool._invoke_creator(self) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 640, in connect 2024-07-29 14:08:31 return dialect.connect(cargs, cparams) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 580, in connect 2024-07-29 14:08:31 return self.loaded_dbapi.connect(*cargs, **cparams) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/pytds/init.py", line 1248, in connect 2024-07-29 14:08:31 login.tls_ctx = tls.create_context(cafile) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/pytds/tls.py", line 124, in create_context 2024-07-29 14:08:31 ctx.load_verify_locations(cafile=cafile) 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/OpenSSL/SSL.py", line 920, in load_verify_locations 2024-07-29 14:08:31 _raise_current_error() 2024-07-29 14:08:31 File "/app/lib/python3.10/site-packages/OpenSSL/_util.py", line 57, in exception_from_error_queue 2024-07-29 14:08:31 raise exception_type(errors) 2024-07-29 14:08:31 OpenSSL.SSL.Error: [('x509 certificate routines', '', 'no certificate or crl found')] 2024-07-29 14:08:31 2024-07-29 14:08:31 The above exception was the direct cause of the following exception:

parthpuri-elastic commented 1 month ago

You need to paste whole CA data as SSL certificate rather than file path e.g: -----BEGIN CERTIFICATE----- MIID+jCCAuKgAwIBAgIGAJJMzlxLMA0GCSqGSIb3DQEBCwUAMHoxCzAJBgNVBAYT ... 7RhLQyWn2u00L7/9Omw= -----END CERTIFICATE----- https://www.elastic.co/guide/en/enterprise-search/current/connectors-ms-sql.html#connectors-ms-sql-client-configuration.

moxarth-elastic commented 2 weeks ago

You need to paste whole CA data as SSL certificate rather than file path e.g: -----BEGIN CERTIFICATE----- MIID+jCCAuKgAwIBAgIGAJJMzlxLMA0GCSqGSIb3DQEBCwUAMHoxCzAJBgNVBAYT ... 7RhLQyWn2u00L7/9Omw= -----END CERTIFICATE----- https://www.elastic.co/guide/en/enterprise-search/current/connectors-ms-sql.html#connectors-ms-sql-client-configuration.

@Gleb-Gadyatskiy did you get a chance to try this out?