Support SSL

[jordanorc]

When I try to connect to Windows Azure SQL, it raises the following exception:

AttributeError: '_TdsSession' object has no attribute '_sock'

Changing line 3199 on file tds.py from:

self._sock = ssl.wrap_socket(self._sock, ssl_version=ssl.PROTOCOL_SSLv3)

to:

self._sock = ssl.wrap_socket(self._transport._sock, ssl_version=ssl.PROTOCOL_SSLv3)

Solves the issue. But then I get the following error:

ssl.SSLError: [Errno 8] _ssl.c:507: EOF occurred in violation of protocol

Has anyone had success when connecting to Azure using pytds?

[denisenkom]

The ssl is not supported by pytds currently. So if you can disable ssl on the mssql server it might work. Is Windows Azure SQL always require encryption? Or do you require encryption?

[jordanorc]

@denisenkom, according to this link http://msdn.microsoft.com/en-us/library/ff394108.aspx, isn't possible disable encryption on Windows Azure SQL Database:

"All communications between Windows Azure SQL Database and your application require encryption (SSL) at all times. If your client application does not validate certificates upon connection, your connection to Windows Azure SQL Database is susceptible to "man in the middle" attacks. "

How can I help with this (update pytds to support SSL)? Do you have any plans to work on it?

Would be awesome to see a pure python library supporting Windows Azure SQL.

[denisenkom]

Nobody yet asked for this, you are the first one :)

I explored ways to implement ssl on python but the way TDS does it isn't standard, and at least python 2.7 doesn't support this kind of ssl negotiation.

In tds protocol you first open plain text connection and negotiate encryption with server over unsecured channel and if client and server require/support encryption you start ssl handshake and basically upgrade your connection into an encrypted connection. The problem with Python's 2.7 ssl library is that you cannot establish a connection and later run ssl handshake on it, it does connection and handshake in one function call, and it doesn't allow you to break this process into separate steps.

There might be some improvements in ssl library in python 3, I didn't look into it yet.

I will take a look at the state of the things as of today. If you can help with also looking into it that would be great too!

[jordanorc]

Thanks for the information @denisenkom

I'll research a bit more about it and see if I can help in any way.

Be able to replace a library that depends on the OS (in this case, pyodbc) for another in pure python seems to be very advantageous.

I'm working to adapt django-pydbc (https://github.com/lionheart/django-pyodbc/) library for use with pytds, since the library suggested by you doesn't support newer versions of Django. Everything seems to be working ok with Django 1.7, except Windows Azure SQL.

If you find something new, let me know.

[denisenkom]

There is a django backend for pytds already: https://bitbucket.org/cramm/django-sqlserver

[jordanorc]

Front page says it only works with Django 1.5, despite commits also references 1.6.

I will test it with 1.6 and 1.7.

[denisenkom]

1.6 should be fully supported, I run tests agains it, but 1.7 isn't yet, migrations are still work in progress.

[denisenkom]

I refreshed my memory on the subject. The actual problem is that TDS protocol requires SSL handshake to be wrapped inside PRELOGIN packet http://msdn.microsoft.com/en-us/library/dd357559.aspx To be able to do this one should be able to generate SSL handshake request as raw bytes and then wrap it inside PRELOGIN packet. Python 3.4 still doesn't allow this, it sends handshake packet directly to the socket.

[jordanorc]

This can help? http://stackoverflow.com/questions/3235486/recv-send-on-raw-socket-before-ssl-wrap-python

[denisenkom]

Unfortunately not, what is needed is a way to intercept handshake packet before it is sent, wrap it into TDS PRELOGIN packet and only after that send it (and by wrapping I basically mean that a TDS packet header should be sticked in front of the packet), same is probably needed for incoming handshake packets, they probably need to be unwrapped from PRELOGIN header and passed to ssl afterwards.

To illustrate this, here is how Python does handshake:

request packet: <ssl handshake payload>

Here is how TDS should do: <tds packet header with type PRELOGIN> <ssl handshake payload>

[jordanorc]

Do you know how FreeTDS does it, @denisenkom?

I don't know much about how python calls Openssl, but it seems to delegate handshake to Openssl:

def do_handshake(self):
    """Perform a TLS/SSL handshake."""
    self._sslobj.do_handshake()

Maybe understand how Freetds does that can help to find a workaround in python.

[denisenkom]

FreeTDS does it by using C API to OpenSSL/CryptoApi. Theoretically it is possible to use C api by using ctypes python library. On which platform do you run your code?

[rschneiderman0123]

denisenkon. I like your pytds lib, it's pretty good. To get the handshake packet, it's this import binascii import socket try: import OpenSSL from OpenSSL import SSL, crypto except: print "pyOpenSSL is not installed, can't continue" sys.exit(1)

ctx = SSL.Context(SSL.TLSv1_METHOD) ctx.set_cipher_list('RC4') tls = SSL.Connection(ctx,None) tls.set_connect_state() try: tls.do_handshake() except SSL.WantReadError: data = tls.bio_read(4096) print binascii.hexlify(data)

I'm not sure if this works but basically you put the packet into another prelogin, and use an offline tls object to decrypt. https://code.google.com/p/impacket/source/browse/trunk/impacket/tds.py. If you're on linux and you need encryption, then the Microsoft Linux SQLSever driver is fine and there are some good instructions on how to put it on a 64 bit linux box.

[denisenkom]

Additional options to consider: https://gitlab.com/m2crypto/m2crypto https://github.com/trevp/tlslite

[denisenkom]

and that: https://pypi.python.org/pypi/pyOpenSSL

[gfranxman]

bump. I'm not a windows user, but I'd really like to use this to help migrate a node app to python which uses a pure javascript implementation that is capable of even active directory authentication. Their code looks like var sql = require('mssql'); var cfg = { domain: process.env.ADDOMAIN, user: process.env.ADUSER, password: process.env.ADPASSWD, server: dbhost, database: dbname, encrypt: true }; sql.connect( cfg ).then( ...

Wouldn't that be nice? Maybe I'll get time to attempt to reverse how they did it.

[cristianocoelho]

Any ideas on how to work with this? Azure forces you to use SSL and doing code changes to use SSL doesn't seem to work in any way.

[lnattrass]

It looks like you might be able to do this using Memory BIO in python 3.5+

I needed to print out the certificate from an SQL server, so ended up writing this digusting piece of code: https://gist.github.com/lnattrass/a4a91dbf439fc1719d69f7865c1b1791

Works for us though..

[denisenkom]

Initial implementation is available in tls-support branch. Please take a look. Suggestions welcome!

[denisenkom]

Released version 1.9.0 with TLS support

[ahmedpopal456]

Hello :); I had a quick question; was trying to connect to a Microsoft SQL Server on Azure using the newest version of pytds, which supports TLS; I was wondering if there was a way to enable encryption on the client side (Error: Client does not have encryption enabled but it is required by server, enable encryption and try connecting again)

[denisenkom]

you need to specify cafile parameter for connect function: https://python-tds.readthedocs.io/en/latest/pytds.html#pytds.connect

[eydelrivero]

Documentation says cafile is the

Name of the file containing trusted CAs in PEM format, if provided will enable TLS

, but where can I get that file name from? Do I need to download a certificate? Can you elaborate with an example? I'm trying to use pytds to connect to an Azure SQL database.

[denisenkom]

I think there should be a download link somewhere at Azure control panel. First place I would check is connection string suggested by Azure, it might have a link to the root certificate.

[ahmedpopal456]

Yes, I did find the link to the certificate ! Now, I guess copying the file to the VM and pointing the path to the cafile attribute should be enough?

[AnilChandore]

@ahmedpopal456 did it work? If yes then, can you please share how did you download the pem file and what it contained?

[dmlundeby]

I see this thread is old, but I also had troubles specifying the cafile. However, I found the following solution to work for me:

I'm trying to access a MSSQL DB in Azure. It turns out its certificate is trusted by Mozilla. Thus, I could use the certifi package, and use certifi.where() as cafile. I also needed to set validate_host to False.

[sfc-gh-mrojas]

Thanks @dmlundeby this really unblocked me I am able to connect with:

import certifi
with pytds.connect('xxxx.database.windows.net', 'database', 'user', 'password',cafile=certifi.where(),validate_host=False) as conn:
        with conn.cursor() as cur:
            cur.execute("select 1")
            cur.fetchall()
            cur.execute("insert into mytest_table(col1,col2) values('hola', 3200)")
            cur.execute("commit")
            return "done"

[denisenkom]

You should not set validate_host to false since that option makes you vulnerable to MitM attack. You should set it to true.

[sfc-gh-mrojas]

mm the reason I used it as false is because if you go to the Azure pane for connection strings, it says to use something like:

Driver={ODBC Driver 18 for SQL Server};Server=tcp:xxxx-sqlserver-tests.database.windows.net,1433;Database=xxxxx;Uid=admin;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

So I thought that using TrustServerCertificate=no will be similar to validate_host=False.