TLS EncryptedSocket timeout error

[radyz]

I'm been trying to get TLS to work with SQL in Azure. I ran into this issue when I pass in:

cafile = '/etc/ssl/certs/ca-certicates.crt'
validate_host = False

Then the following error rises:

  File "/home/vagrant/.virtualenvs/project/lib/python2.7/site-packages/pytds/tds.py", line 1209, in commit
    prev_timeout = self._tds.sock.gettimeout()
AttributeError: 'EncryptedSocket' object has no attribute 'gettimeout'

I tracked it down and it looks like all references to gettimeout and settimeout are being done against the EncryptedSocket object rather than the transport property. I added pass through methods to avoid breaking further code but that seemed to correct the errors and could connect successfully to Azure.

[eydelrivero]

Hi @radyz , I'm trying to connect to an Azure SQL database too using pytds. Where can I get the certificate to pass on the cafile parameter? I've been using a certificate I downloaded using openssl s_client -showcerts -connect <host>:443 </dev/null 2>/dev/null|openssl x509 -outform PEM >dbcertfile.pem but no luck so far. It fails saying Error: [('SSL routines', 'tls_process_server_certificate', 'certificate verify failed')]

[radyz]

I managed to connect with

cafile = '/etc/ssl/certs/ca-certicates.crt'
validate_host = False

[eydelrivero]

Thanks @radyz for your response! I was able to find that the specific certificate needed is Baltimore CyberTrust Root. Don't know if it is specific for this case or can be applied in other situations.

[denisenkom]

By setting validate_host=False you make yourself vulnerable to MitM attack. Anyone can generate valid certificate using Let's Encrypt or similar service. Such certificate would pass validation if host validation is disabled. So why have you disabled host validation?

[eydelrivero]

Hi @denisenkom ! If I use validate_host=True, the same certificate no longer works. It throws Exception: Certificate does not match host name 'redacted_host_name.database.windows.net' when trying to connect. Any thoughts on how to make it work with validate_host=True?

[ahmedpopal456]

I can't get it to work with validate_host=False either... I get an error with an empty error stack in line 349 of init (using Baltimore with validate_host=False)

[] Traceback (most recent call last): File "", line 83, in main File "/mnt/resource/hadoop/yarn/local/usercache/livy/appcache/application_1517595982363_0023/spark-83135427-a696-4ceb-b5c8-7e0a9bd3380b/userFiles-93bfbaa9-1b52-4ed7-8a1d-bdea6a1f01db/pytds.zip/pytds/init.py", line 1196, in connect conn._open() File "/mnt/resource/hadoop/yarn/local/usercache/livy/appcache/application_1517595982363_0023/spark-83135427-a696-4ceb-b5c8-7e0a9bd3380b/userFiles-93bfbaa9-1b52-4ed7-8a1d-bdea6a1f01db/pytds.zip/pytds/init.py", line 349, in _open raise last_error Error: []

[eydelrivero]

Hi @ahmedpopal456 , have you tried installing pytds package from the master branch instead of using the latest release 1.9.0? In my case I had to do so because of the bug in this thread.

[ahmedpopal456]

Will try to do that now ! @eydelrivero will let you know what the results are ! Kudos

[denisenkom]

New version is uploaded to pypi - 1.9.1 with a fix, kudos to @radyz