PYODBC + MS SQL SERVER connection with Encrypt=yes not connecting

user_9216998 0
Viewed 6346

We have a python flask app running on an aws centos ECS instance. We are trying to establish an encrypted connection to our database via PYODBC with odbc 17 on Linux. When running locally we just use the SQL server driver. Currently we have the code:

params = urllib.parse.quote_plus(driver;server;user;pwd;...;Encrypt=yes)

SQLALCHEMY_DATABASE_URI="mssql+PYODBC:///?odbc_connect=%s" %params

We have tls enabled on the server. The connection works locally on windows but not deployed in Linux. Currently doing a deployment with 'yes' instead of 'true'. We are also about to try with 'trustedserverconnection=yes'. Any insight on this process would be greatly appreciated!

Update: latest error, invalid connection string attribute 'trustservercertificate'

pyodbc odbc sqlalchemy sql-server python
2 Answers

We ended up implementing a second connection param:

TrustServerCertificate=YES

Which is not ideal, obviously, because we want to have good security implementation practices. In future state we will need to set this to false and put our ssl pem file in the Linux ssl store.

Hope this helps someone. Had some issues finding documentation for pyodbc with MS SQL Server.

user_92169980

According to this documentation, pyodbc passes the connection string through to the underlying ODBC driver. Microsoft's article Using Connection String Keywords with SQL Server Native Client documents both the Encrypt and TrustServerCertificate attributes. The TrustServerCertificate setting should generally be avoided in production databases; however, it is very useful when testing encrypted connections to a development database that is using a self-signed certificate. For example, the default installation of SQL Server uses a self-signed certificate and will require this setting.

In my mssql+pyodbc connection strings I just append ?Encrypt=yes&TrustServerCertificate=yes as appropriate. Please note, if you already have another setting after a question mark ? then use & instead of ?, for example: ?Trusted_Connection=yes&Encrypt=yes&TrustServerCertificate=yes

sfuqua_303840
Related