CODEWITHSUNDEEP
pythonsql-serversqlalchemy
PGHE
PGHE

Reputation: 1962

UPDATE encrypted column with SQLAlchemy

I'm trying to update an encrypted column in a SQL Server 2016 database using SQLAlchemy. The query will update the column as expected when run in SSMS, but results in NULL when run with the following python code. It's only an issue with the encrypted column as I can update other columns in the same table via SQLAlchemy.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

engine = create_engine('mssql+pyodbc://{}:{}@{}:{}/{}?driver=ODBC+Driver+13+for+SQL+Server'.format('User', 'UserPassword', 'server', port, 'db_name'))
Session = scoped_session(sessionmaker(bind=engine))
s = Session()
s.execute("USE [db_name] OPEN Symmetric KEY S_Key DECRYPTION BY CERTIFICATE S_Cert WITH PASSWORD = 'secret' UPDATE [db_name].[dbo].[Table1] SET [Encryption] = ENCRYPTBYKEY(KEY_GUID('S_Key'), 'newpassword') WHERE [ID] = 10")
s.commit()

Note: I'm using raw SQL becuase I'm unaware of another method to insert data into an encrypted column via SQLAlchemy. Happy to be enlighted on other methods.

Upvotes: 1

Views: 827

Answers (1)

PGHE
PGHE

Reputation: 1962

Sorted. Changed the execute function to call the query as a stored procedure instead, and the error produced let me know the user didn't have the correct access to the key and certificate.

Upvotes: 1

Related Questions