Encrypting Data in NAV using SQL with ADO

A lot of Dynamics NAV databases carry sensitive information that by law in some countries need to be encrypted. For example credit card number or social security number. There are many solutions available. The following solution utilizes SQL Server 2005 and SQL Server 2008 provided encryption feature. There are two kinds of keys used in encryption: Symmetric Key, Asymmetric Key.
In my example I will be using Symmetric Key. SQL provides many algorithms to encrypt data. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256. I will be using TRIPLE_DES in my example.
In order to start using encryption in a database you need to first create a master key. In the statement below you can change the password NAVSQL to something else.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = N’##MS_DatabaseMasterKey##’)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘NAVSQL’
Then you need to use this master key to create a certificate.
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = N’EncryptTestCert’)
CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = ‘NAVSQL’

Once the certificate has been created, you use the certification to create the symmetric key .
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = N’TestTableKey’)
CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert

Ok so now that we have created the key, we can use it to encrypt data. In this case I’m using a dummy Credit card number.
OPEN SYMMETRIC KEY TestTableKey DECRYPTIONBY CERTIFICATE EncryptTestCert
SELECT ENCRYPTBYKEY(KEY_GUID(’TestTableKey’),N’1234-1234-1234-1234′)
CLOSE SYMMETRIC KEY TestTableKey

And to decrypt the encrypted data. The following statement is used
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT convert( NVARCHAR, DECRYPTBYKEY(0×00978394E816C741947BC95224DF249D010000003770A661AF5AD888731D4A07B6B5B2B8554B7A253089F7591FCF27B78CEABCA4FA81B4E10C34F1E07CD1465138000BF823FEA1965FBCFB20))
CLOSE SYMMETRIC KEY TestTableKey

Using Ado to run these staments brings up some challenges. For example EncryptbyKey returns a varbinary data type and NAV does not support this data type. To workaround this limitation you have to write some sql statement to convert it to hexstring.
Second issue is when you run the sql statement on using ADO you’ll get an error when openning recordset.
ADODB.Fields returned the following message:
Item cannot be found in the collection corresponding to the requested name or ordinal.
To work around this, You’ll need to call
Recordset.Open(’SET NOCOUNT ON’);

Here is Nav code that.

And here is a screenshot of Example Form used.

And here is the object. EncryptExample