CODEWITHSUNDEEP
pythonsql-serverpyodbc
Neil P
Neil P

Reputation: 3190

pyodbc connect to sql server with TrustServerCertificate

I'm trying to connect to a SQL server instance using python and pyodbc.

cnxn = pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=192.168.0.1;Database=Db;User Id=Too;Password=Easy")

When I run this, I get the below error:

('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722 (-2146893019)')

So we have a self signed certificate, this should be easy to fix, as we just add TrustServerCertificate

cnxn = pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=192.168.0.1;Database=Db;User Id=Too;Password=Easy;Encrypt=yes;TrustServerCertificate=yes")

Which now results in a new error:

('28000', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user ''. (18456); [28000] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0)")

Removing TrustServerCertificate, but leaving Encrypt results in the below error:

('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722 (-2146893019)')

Which suggests the problem, is something related to recognising TrustServerCertificate in the connection string.

So I try to set the value through attrs_before instead

SQL_COPT_SS_TRUST_SERVER_CERTIFICATE = 1228  
SQL_COPT_SS_ENCRYPT = 1223
cnxn = pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=192.168.0.1;Database=Db;User Id=Too;Password=Easy;", attrs_before={SQL_COPT_SS_ENCRYPT : 1, SQL_COPT_SS_TRUST_SERVER_CERTIFICATE : 1})

Which again results in the error

Invalid connection string attribute (0)

What am I missing in order to connect to a SQL Server that's using a self signed certificate?

Upvotes: 1

Views: 830

Answers (1)

Neil P
Neil P

Reputation: 3190

(as per Gord Thompson's comment) The connection string needs to use UID for user id and PWD for password

e.g.

cnxn = pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=192.168.0.1;Database=Db;UID=Too;PWD=Easy")

Upvotes: 1

Related Questions