I'm trying to connect to SQL Server 2016 with python-tds driver using NTLM authentication. The sql server is forcing encryption (SQL Configuration manager) as per this screenshot. There is no certificate attached to it on the next tab.
The server is on AWS EC2 machine but I don't think this bring any difference? This code (which is confirmed to work for a different server with no encryption):
import pytds
from pytds.login import NtlmAuth
ntlm = NtlmAuth(user_name='domain\username', password='password')
pytds_conn = pytds.connect(dsn='11.22.33.44', database='master',
port=1433, auth=ntlm, autocommit=True)
gives this error:
pytds.tds_base.Error: Client does not have encryption enabled but it is required by server, enable encryption and try connecting again
I then tried to use cafile parameter within pytds.connect function. So I created .cer file and corresponding .pem file following this link : http://www.richardnichols.net/2014/03/creating-and-importing-an-external-certificate-to-sql-server/
After uploading the .cer certificate using the query:
CREATE CERTIFICATE testCer
FROM FILE = 'c:\Temp\test.cer'
and adding parameter cafile=test.pem to pytds.connect I got this error:
OpenSSL.SSL.Error: [('SSL routines', 'tls_process_server_certificate', 'certificate verify failed')]
Do you know how (or whether at all) can I make the python-tds NTLM connection working when the encryption is forced and there is no certificate attached to it in SQL Server Network Configuration?