How to encrypt customer table data in Dynamics NAV or SQL Server?

samantharodg

I want to encrypt customer table data in Dynamics NAV or SQL Server? Personal info such as email, phone, SSN, credit card info is sensitive and can be stolen from DB, so looking for a solution to encrypt it. Strong encryption such as AES 256 will be better. I will appreciate if someone can make suggestions, we want to avoid or keep minimum code changes.

kriki

Sql server has "Transparent database encryption". No codechanges at all. Problem: it is an Enterprise Edition feature.

NAV 2017 has ENCRYPT and DECRYPT but codechanges are necessary.

jackclarke818

Here are the steps to Encrypt Sensitive Data using Always Encrypted & Dynamics NAV 2016

Step 1 - Running Always Encrypted Wizard:
SQL Server 2016 comes with an inbuilt tool of encryption that takes care of the full encryption mechanism within it. What you need to do is just go to the field that needs to be encrypted in SQL Server and run the wizard from there

Step 2 -Integrating Dynamics NAV 2016 with Always Encrypted:
Once the data is encrypted, it can be retrieved from a set of SQL queries if the Service user have the access to Column Master Key, which is an encrypted key generated by Always Encrypted Wizard and can be stored at Various Locations provided by Microsoft.

Step 3 - How to Ensure the Always Encrypted Data Security:
The Keys generated in Step 2 has an option to be stored at Widows Certificate Store, Azure Key Vaults and other Cryptographic Key Management Tools.

Step 4 - Always Encrypted Data Retrieval (Insert / Update operations):
To insert the data in always encrypted column, direct insert query will fail in this scenario, but this can be done using Dot Net assemblies by using a correct Connection String.

Step 5 -Deploying the Always Encrypted fields in Dynamics NAV or Customer Website:
Once the data is encrypted it can be seen in a Binary stored format at the SQL Server by using a simple Select Query, for publishing the data by decrypting, Dot Net assemblies needs to be employed both in Navision CAL code or Website Dot Net Code. Currently Always Encrypted is supported with Dot Net assemblies only.

You can also check this video on How to Encrypt or Decrypt data with Encrypted SQL 2016 feature