Connecting NAV 2016 to an external SQL Database

mark02tj

I'm trying to connect NAV 2016 to an external SQL Server database. I have it working on my laptop, but cannot get it working on the client system. As per this video, I've set up a codeunit to handle the connection. Here's what the codeunit has in it....

OnRun()
ConnectToSQL;

ConnectToSQL() : Boolean
DoConnectToSQL;

LOCAL DoConnectToSQL()
IF HASTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'EclipseNAVTransfer') THEN
UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'EclipseNAVTransfer');

DATABASE.REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'EclipseNAVTransfer',
'Data Source=lan-nav-sql\LAN-NAV-SQL;Initial Catalog=EclipseNAVTransfer;User ID=customerdomain\navisionservice;Password=p@ssword1');

SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'EclipseNAVTransfer');

The only difference between the code on my local copy and the code in the client database is "Data Source" path has different server names in it and the "User ID" and "Password" are different.

When I launch the page to display the data, here is the message I get:



Does anyone that's done this before have anything I should be looking at? As far as I can tell, I have the SQL security set up correctly. The main thing on the client site is that I'm going from the server that's hosting the NAV service over to the SQL Server whereas on my laptop, that's obviously the same box. Also, the database that I'm trying to connect to is on a different SQL Server than the SQL Server that hosting the actual NAV data. Also, the external SQL Server that I'm trying to connect to is SQL-2014 and the NAV SQL Server is SQL-2012.

Any help would be appreciated.

archer89

check firewall setting. disable, try again.
disable antivirus, if any.
check sql server settings: is the database reachable from outside ?
are the users valid sql users, are the passwords correct ?

mark02tj

Disabled the firewall and turned off the antivirus. No luck.

I opened the SQL Management Studio on the server that hosts the NAV database and logged into the server that I'm trying to connect to. That was successful. It's all on the same LAN and behind all of the same firewalls.

The users are valid SQL users and are set up as DBO. Passwords are also correct. I even tried giving the "guest" login DBO privileges to the this database and removed the user/password parameters from the login string. Still no luck.

I'm leaning towards it being something with the way the user ID is being processed though. On my laptop, I removed the user ID and password from the login string and was able to access the database table. I'm guessing that's because I'm logged into my local machine and SQL recognizes me as a DBO.

Any other ideas? Surely I can't be the first person to try and access an external SQL database from within NAV. But, maybe this is less common than I think because there's not much documentation (other than what's on MSDN) on anyone's blogs, etc. Usually when I'm trying to do something, I can easily find examples of other people doing roughly the same thing.

mark02tj

Also, I meant to say.... THANKS Jonathan for the suggestions!!

archer89

restart nav service, then try again.
check, if the user in the connectionstring is a valid sql user with according permissions/roles (db_owner) and has access to the target database.

mark02tj

Hi Jonathan,

Thanks again for following up. Yesterday I finally figured out what the problem was. I changed this command...

DATABASE.REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'EclipseNAVTransfer',
'Data Source=lan-nav-sql\LAN-NAV-SQL;Initial Catalog=EclipseNAVTransfer;User ID=customerdomain\navisionservice;Password=p@ssword1');


To....

DATABASE.REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'EclipseNAVTransfer',
'Data Source=lan-nav-sql\LAN-NAV-SQL;Initial Catalog=EclipseNAVTransfer');

What I discovered was that NAV is apparently passing the User ID for the NAV Service in the command and that passing the User ID in the string causes an error. I already had set the "navisionservice" account (which is what the NAV service tier uses) to DBO on my external database. To test my theory, I removed DBO from that account and received a permissions error when trying to connect. I added DBO back to the login and was able to access the table successfully. So, that confirms that NAV is passing the service account user ID and doesn't require one in the parameter.

Hopefully this saves someone else the many, many hours that I've spent trying to figure this out.

archer89

great info! thx.
in the end that means, the user in the connectionstring is ignored.
maybe helpful for you:
https://www.connectionstrings.com/sql-server-2012/

check out especially parameter "Integrated Security=SSPI"

madsmorre

Hi Mark
I also have difficulties to connect to an external SQL Server 2014 via dotnet. I get the error "network path not found". I can without problems connect via SQL Server Management Studio. I'm connecting with a SQL user (dbowner) and not a domain user on the external side.
I started making a solution using ADO and this works fine - my issue here is that i can't set this in a job queue to run automaticly (when using ADO).

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;

As I read this discussion, the connection string user is ignored - can this be true? and how can I then connect?
Thanks