CODEWITHSUNDEEP
sql-serverencryptionalways-encrypted
Ram
Ram

Reputation: 691

SQL Server Reporting Server data source connection fails when using Always Encrypted feature

enter image description hereWe're using "Always Encrypted" feature in SQL Server to encrypt a few columns. Encryption works fine on MVC/SQL side. Only issue we have is with the SQL reporting server. Able to run the reports when the data source connection string is 

Server=mssqlserver;Database=MYDATABASE;Integrated Security=true

But when I try to run the reports from the MVC application, it automatically adds the Column Encryption Setting=enabled to the connection string 

Server=mssqlserver;Database=MYDATABASE;Integrated Security=true;Column Encryption Setting=enabled

When I enter the credentials and click 'Test Connection', it throws an error

Keyword not supported: 'column encryption setting'

Because of the above error, it cannot make a connection to the data source 'MYDATABASE'. Tried giving permission to the reporting server service account and the account used to connect to the database to access the keys to encrypt/decrypt but no luck. How can I fix this issue? We're using SQL Server 2017.

Upvotes: 0

Views: 1058

Answers (1)

Rob Nicholson
Rob Nicholson

Reputation: 61

This error is to do with the version of SSRS you are running. Under the covers, SSRS uses .net framework to connect to data sources. This version does not understand the keyword provided.

From the screenshot it looks like SSRS 2008R2, this targets .net 3.5. Always Encrypted (AE) is not supported until 4.6 (SSRS 2019). As far as I am aware you cannot change SSRS's target framework.

To get around this issue I suggest either: 1. Install an alternate data source provider that supports AE (i.e. Microsoft ODBC Driver for SQL Server 17.4 or above), then update the data source's connection string to use this 2. Upgrade SSRS to 2019

Hope this helps

Upvotes: 1

Related Questions