Python connection to EXASOL which has encryption turned on

Question

I am trying to connect to encrypted exasol database through python. It connects without issues when it is unencrypted.

EXASOL.connect seems to be based on pyodbc, does anyone had success in using pyodbc with encryption turned on?

I tried to pass the ENCRYPTION=Y as string parameter, based on the manual (page 343) with no success.

ENCRYPTION Switches on the automatic encryption. Valid values are "Y" and "N" (default is "N").

Seems like it ignores it. Keeps blasting - OperationalError: database error [08004] Connection exception - Client connection must be encrypted.

database_table = "SCHEMA_NAME.MY_TABLE"
conn = EXASOL.connect('ws://192.168.56.101:8563', 
                      'UID=sys', 
                      'PWD=pwd', 
                      'ENCRYPTION=Y', 
                      )

sql = "SELECT * FROM %s LIMIT 1000;" % database_table
df_sql = pd.read_sql(sql, conn)

/home/ec2-user/anaconda3/envs/mxnet_p27/lib/python2.7/site-packages/IPython/core/interactiveshell.pyc in run_cell_magic(self, magic_name, line, cell)
   2115             magic_arg_s = self.var_expand(line, stack_depth)
   2116             with self.builtin_trap:
-> 2117                 result = fn(magic_arg_s, cell)
   2118             return result
   2119 

<decorator-gen-60> in time(self, line, cell, local_ns)

/home/ec2-user/anaconda3/envs/mxnet_p27/lib/python2.7/site-packages/IPython/core/magic.pyc in <lambda>(f, *a, **k)
    186     # but it's overkill for just that one bit of state.
    187     def magic_deco(arg):
--> 188         call = lambda f, *a, **k: f(*a, **k)
    189 
    190         if callable(arg):

/home/ec2-user/anaconda3/envs/mxnet_p27/lib/python2.7/site-packages/IPython/core/magics/execution.pyc in time(self, line, cell, local_ns)
   1183         else:
   1184             st = clock2()
-> 1185             exec(code, glob, local_ns)
   1186             end = clock2()
   1187             out = None

<timed exec> in <module>()

/home/ec2-user/anaconda3/envs/mxnet_p27/lib/python2.7/site-packages/EXASOL/__init__.pyc in __init__(self, url, username, password, autocommit, queryTimeout, useCompression, typeMapper)
    324         self._inconnect = False
    325         self._type_mapper = typeMapper
--> 326         self._connect()
    327         self._login()
    328 

/home/ec2-user/anaconda3/envs/mxnet_p27/lib/python2.7/site-packages/EXASOL/__init__.pyc in _connect(self)
    417                 self._ws_send = self.__ws.send
    418                 self._ws_recv = self.__ws.recv
--> 419                 ret = self._req(command = 'login', protocolVersion = 1)
    420                 if CRYPTO_LIB == 'rsa':
    421                     if sys.version_info.major >= 3:

/home/ec2-user/anaconda3/envs/mxnet_p27/lib/python2.7/site-packages/EXASOL/__init__.pyc in _req(self, **req)
    407             raise OperationalError('%s [%s] %s' % ('database error',
    408                                                    rep['exception']['sqlCode'],
--> 409                                                    rep['exception']['text']))
    410         raise OperationalError('internal error: %s' % repr(rep))
    411 

OperationalError: database error [08004] Connection exception - Client connection must be encrypted.

wildraid · Answer 1 · 2018-06-21T16:37:18.627

0

PyEXASOL driver supports encryption both for WebSocket protocol and HTTP transport.

All you need to do is to pass encryption=True to connection options.

edited Jun 21 '18 at 16:37

answered Jun 19 '18 at 17:05

wildraid

126
4

Avoid just posting links to other sites, that may disappear in the future. Would you mind quoting some of the specifics that answer the question in your answer? – David Hempy Jun 19 '18 at 17:29

Python connection to EXASOL which has encryption turned on

1 Answers1