Problems with ADO after upgrade to NAV 2017 / SQL 2014

lzr

Hi all,

Recently did a test upgrade to SQL 2014 and NAV 2017 and now our encryption function doesn't work anymore. The same function runs perfectly in NAV 2009 classic on SQL 2008. I used Rasheds code example a long time ago ( https://community.dynamics.com/nav/b/navrashedamini/archive/2009/05/20/encrypting-data-in-nav-using-sql-with-ado)
I think the error message is this "Item cannot be found in the collection corresponding to the requested name or ordinal", it's in Swedish so I am not sure.

If I run the code in SQL Management studio 2014 using the same user it executes properly. It looks like the ADO is not returning any data

The SQL code which works:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'blabla' 
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert
declare @hexbin varbinary(max);
set @hexbin = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),N'1111');

CLOSE SYMMETRIC KEY TestTableKey
select '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(1024)') as Column1;

The code in NAV 2017 which doesn't work:

ConnectionString:='Driver={SQL Server};'
    + 'Server=bella;'
    + 'Database=nav;'
    + 'User ID=decrypter;'
    + 'Password=1234;';

IF ISCLEAR(lADOConnection) THEN 
  CREATE(lADOConnection, FALSE, TRUE);
lADOConnection.ConnectionString(ConnectionString);

lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN
  CREATE(lADOCommand, FALSE, TRUE);

lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;

IF ISCLEAR(lADORecordset) THEN
   CREATE(lADORecordset, FALSE, TRUE);

lADORecordset.ActiveConnection := lvarActiveConnection;

SqlCommand := 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''blabla'' ' +
              'ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ' +
              'CLOSE MASTER KEY ';

lADOCommand.CommandText :=  SqlCommand;
lADOCommand.CommandTimeout := 0;
lADOCommand.Execute;

SqlCommand := 'OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert ' +
              'declare @hexbin varbinary(max); ' +
              'set @hexbin = ENCRYPTBYKEY(KEY_GUID(''TestTableKey''),N''1111''); '+             
             'select ''0x'' + cast('''' as xml).value(''xs:hexBinary(sql:variable("@hexbin") )'', ''varchar(1024)'') as Column1;' +
             'CLOSE SYMMETRIC KEY TestTableKey ';
              
              
lADOCommand.CommandText :=  SqlCommand;
lADOCommand.CommandTimeout := 0;
lADOCommand.Execute;

lADORecordset.Open('SET NOCOUNT ON');
lADORecordset.Open(lADOCommand);

Value := lADORecordset.Fields.Item('Column1').Value;

lADORecordset.Close;
lADOConnection.Close;
CLEAR(lADOConnection);

Would really appreciate if someone can point me in the right direction. If I can't get this to work I guess I have to export all old data and encrypt it with some other function.

Remco_Reinking

you could try and rewrite this code to use ado .net, or maybe even better, start using the encryption functions NAV has now.

Remco_Reinking

you could try and rewrite this code to use ado .net, or maybe even better, start using the encryption functions NAV has now.

ara3n

I agree. Either use dotnet type. There are many examples on using ADO with dotnet. Or use the new encryption mechanism NAV has built. There are several limitation to nav encryption. Such as length of the field.

lzr

Thanks for your answers!
I rewrote the code using ADO.NET and it worked.

madsmorre

Hi
I'm doing some of the same code, but I have trouble connecting to sql - on the SQLConnection.OPEN (it says "...network path not found").
I run NAV 2016

Thois is my connection sstring with dotnet

My connection string looks like this:
ConnectionString :='Data Source=mssql.xxxxx.dk;Initial Catalog=xxxxxxx;User Id=xxxx;Password=xxxxxxx';
The error appears in the
SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;

Hope to hear from you