Query SQL Server encrypted columns through python and show decrypted values

Question

I am trying to query a table (in an on premise database), which has some encrypted columns, in Python. I don't have any problem querying this data in Microsoft SQL Server Management Studio. Since I have a certificate, I just use Windows authentication to login, and in Additional Connection Parameters, I add "Column Encryption Setting = Enabled". This way I am able to see all the decrypted values.

In Python I am not able to do this. I have tried various parameters in the pyodbc library, but the values always show up encrypted.

What I have tried so far:

conn = pyodbc.connect('Driver={SQL 
Server};'
'Server=ServerName;'
'Database=DBName;'
'ColumnEncryption=Enabled;'
'Trusted_Connection=yes;'
'TrustedServerCertificate=yes;')

conn = pyodbc.connect('Driver={SQL 
Server};'
'Server=ServerName;'
'Database=DBName;'
'Trusted_Connection=yes;'
'ColumnEncryption=Enabled;')

The values in the Python pandas dataframe still show up encrypted, something like

'\xee\x91\xghk\x00n\xk9.....

The two lines are identical. The only relevant setting is ColumnEncryption but you don't specify any certificate anywhere. The other settings control the TLS connection and use Windows Authentication — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 20, 2022 at 15:34
Which ODBC driver are you using? According to the docs for the ODBC driver with Driver={ODBC Driver 17 for SQL Server} it should be enough to just use ColumnEncryption=Enabled;. The latest driver version is 18 — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 20, 2022 at 15:39
@PanagiotisKanavos It is version 17. That worked, thanks a lot! I changed to Driver={ODBC Driver 17 for SQL Server} instead of Driver={SQL Server} — Sasha Remedios
– Sasha Remedios, Commented May 20, 2022 at 15:56

Gord Thompson · Accepted Answer · 2022-05-20 17:05:12Z

3

Driver={SQL Server} (SQLSRV32.DLL) is the ancient SQL Server driver that ships with Windows. It dates back to the days of SQL Server 2000 and does not support many of the more modern SQL Server features.

Today's applications should use a more current driver like Driver=ODBC Driver 17 for SQL Server (MSODBCSQL17.DLL) or Driver=ODBC Driver 18 for SQL Server (MSODBCSQL18.DLL).

answered May 20, 2022 at 17:05

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query SQL Server encrypted columns through python and show decrypted values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related