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’
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.
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
andTrustServerCertificate
attributes. TheTrustServerCertificate
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