How to access the SQL Server encrypted columns in an SQL connector in Bold BI

What are ‘Always Encrypted’ Columns?

“Always Encrypted” is a feature in SQL Server that helps protect sensitive data, such as credit card numbers or national identification numbers, stored in SQL Server databases. Here’s a brief overview:

Definition:

Always Encrypted is a security feature that ensures sensitive data is encrypted and decrypted transparently to the application, not to the database itself. This means that data is encrypted in the database and only decrypted by the application that retrieves it.

Key Features:

End-to-End Encryption:

Data is encrypted at the client side and remains encrypted during transmission and storage. Only authorized users and applications with the necessary decryption keys can view the data in plaintext.

Encryption Types:

Deterministic Encryption: Produces the same encrypted value for any given plaintext value, enabling operations like equality joins, group by, and distinct operations.
Randomized Encryption: Uses a method that encrypts data in a less predictable way, providing a higher level of security but not supporting equality joins, group by, and distinct operations.

How to create SQL Encrypted columns

1. To encrypt the column in SSMS, we need to open it in admin mode.
   

   

2. Choose “Local Machine” as the Master Key source in the Master Key configuration because the key needs to be present on the local machine for IIS.
   

   

3. When SSMS is opened without administrator privileges and an attempt is made to save encrypted data, an error occurs in our Bold BI application. Please open the request in admin mode, select the Encryption key from the drop-down menu, and try again.
   

   

How to connect SQL Encrypted columns in Bold BI

1. Establish a SQL connection using the SQL credentials and additional parameters, include the property Column Encryption Setting=Enabled and proceed to connect to the data source.
   

   

2. Drag and drop the encrypted table, and you will see the decrypted data.
   

   

If encountering an issue in Bold BI where the user saves the encrypted column without admin mode, it is necessary to follow the steps given in the previous section to resolve this issue.

Limitations in SQL encryption in Bold BI

1. Both SQL and Bold BI applications should be installed on the same Windows machine in order to ensure compatibility with IIS, and Bold BI should be configured to use the same user account.
2. Encryption column is supported in SQL Server Management studio 2018 or latest version
   
   
3. Query-level encryption is not supported because it requires a master key in IIS. Please consider encrypting the columns using SSMS 2018 or above version.