Problems with ADO after upgrade to NAV 2017 / SQL 2014

lzrlzr Member Posts: 264
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.
Navision developer

Best Answer

Answers

  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • lzrlzr Member Posts: 264
    Thanks for your answers!
    I rewrote the code using ADO.NET and it worked.
    Navision developer
  • madsmorremadsmorre Member Posts: 40
    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
Sign In or Register to comment.