Error connection to SQL Server 2014 via dotnet

I'm new into using Automations and dotnet, so i'm trying to learn (if I don't explain correct ). I'm trying to connect from NAV 2016 to an external SQL Server 2014 to read and post data to this.

The story is that it works fine when i'm using ADO to connect, but this doesn't allows me to run the batches as scheduled (on a NAS server) - it needs to be run from a client.

The code is:
ConnectionString :='Data Source=mssql.xxxxx.xx;Initial Catalog=XXXX_stage;User Id=xxxx;Password=xxxxxxxx;
SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;

I have read in the https://connectionsstrings.com and tried some of the different ways to connect.

It is correct that when I used ADO (Automation) I used

ConnectionString :='PROVIDER=SQLOLEDB;Server=mssql.xxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';
But when I changed to using dotnet I can't do this as I get the error "...keyword not supported: 'provider'"

I have then tried the following
ConnectionString :='Server=mssql.xxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';
and also
ConnectionString :='Data Source=mssql.xxxxx.dk;Initial Catalog=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';

I'm able to connect to the SQL server in Microsft SQL Server Managment Studio without any problems with the credentials in the Connectionstring

The reason why i'm changing from Automation (ADO) to DotNet is that I can't put the function in a Job queue with the Automation version. It says that
"Microsoft Dynamics NAV Server attempted to issue a client callback to create an Automation object: 00000514-0000-0010-8000-00aa006d2ea4 (CodeUnit xxxx). Client callbacks are not supported on Microsoft Dynamics NAV Server."

I hope that by changing to DotNet would solve that (I was advised to try this)

Please advice what i'm doing wrong if possible

1 answer