Can't connect MS SQL ODBC to MS SQL Sever 2016 Remotly

[ADIGA88]

Hi guys
I need your direction for this issue
I am working in way to connect access Database to MS Dynamic Nav 2018 (to overcome some limitation), after research I couldn't find a connector for MS access to Nav 2018 (Excel has one but not access), so i think why not to connect directly to the SQL using ODBC (for ready only).
so after little bit of research i could connect to the SQL natively (on the same device) using SQL 13 ODBC and SQL 17 ODBC, but when try it from remote device it's not working!!. knowing i check the connectivity it's OK, turned off the firewall on both systems and turn on "Enable remote connections" on my SQL Server throw SQL Studio with same result.

Environment parameters:
Device 1:
Notes: Vbox VM
OS: Windows 10 pro
Connectivity: Bridge connection to physical network baring IP 192.168.1.51
Applications:
Dynamic Nav 2018
MS SQL 2016 Express (installed with Nav 2018)
Microsoft SQL Visual Studio 18 (installed spreadly)

Device 2:
Notes: Host of the VM
OS: Windows 10 home
Connectivity: physical connection (192.168.1.50) and bridge to the vm
Applications:
Office 365
Virtual Box (Oracle)

Connectivity status:
Ping form Device 2 to 1 OK
Name resolve of Device 1 from Device 2 OK

thanks for taking the time to read this.

 

[Minty]

There is an ODBC driver you can get from here https://marketplace.visualstudio.com/items?itemName=CDATASOFTWARE.DynamicsNAVODBCDriver
Not sure how else you would do this.

 

[SQL_Hell]

Have you tried telnet from the remote machine to sql server on port 1433? That will tell you if there's a firewall issue or not, assuming the sql server is configured to use port 1433, which is the default.

 

[Gasman]

Test reply

 

[ADIGA88]

Thanks you for your response

There is an ODBC driver you can get from here https://marketplace.visualstudio.com/items?itemName=CDATASOFTWARE.DynamicsNAVODBCDriver
Not sure how else you would do this.

I downloaded CData Connector, it's odata connector so i enabled the odate within nav but run into odata authentication issue Nav issue to be resolved yet (under progress).

Have you tried telnet from the remote machine to sql server on port 1433? That will tell you if there's a firewall issue or not, assuming the sql server is configured to use port 1433, which is the default.

I tried Putty and Windows telnet with same result "connection refused" it's seem to be a connection issue, I think it's a problem with SQL server port opening to only localhost and 127.0.0.1 knowing

"Enable remote connections" on my SQL Server throw SQL Studio with same result.

Test reply

 

[SQL_Hell]

Have you turned on TCPIP on in SQL server configuration manager for the express instance? When you say you turned the firewalls off, was that windows local firewall? on both machines?

 

[ADIGA88]

Have you turned on TCPIP on in SQL server configuration manager for the express instance? When you say you turned the firewalls off, was that windows local firewall? on both machines?

Hi SQL_Hell
Yes I turned on TCP/IP on SQL server configuration (photo attached).
both machine have windows firewall and both turned off.
also i make nmap scan for port from another vm but there is no open port for SQL (photo attached)

 

[SQL_Hell]

Ok, so looking at your first image, that SQL server is running on a different port from default, it is running on 49170, can you try the telnet using port 49170? and also update your ODBC connection to use the same port and try that also?

Also I just noticed that the sql server instance is called navdemo in ODBC you need to reference this name like 192.168.1.50\navdemo

 

[ADIGA88]

Ok, so looking at your first image, that SQL server is running on a different port from default, it is running on 49170, can you try the telnet using port 49170? and also update your ODBC connection to use the same port and try that also?

Also I just noticed that the sql server instance is called navdemo in ODBC you need to reference this name like 192.168.1.50\navdemo

I did it as you says, the telnet seem to make a connection but it's closed when i start typing charts, regrading ODBC it's give me same result as before (photos attached)

 

[SQL_Hell]

Regarding telnet, that's just how it works, If you get a blank screen then it is working. Ok try either 192.168.1.50\navdemo or 192.168.1.50,49170 in your ODBC server box (it's a comma for sql server not a colon).

 

[ADIGA88]

Regarding telnet, that's just how it works, If you get a blank screen then it is working. Ok try either 192.168.1.50\navdemo or 192.168.1.50,49170 in your ODBC server box (it's a comma for sql server not a colon).

First thank you SQL_Hell for the effort you putting in this thread.
you are right, the error message changed after i used the comma now the issue changed to authentication issue -i think- (photo attached).
The SQL configured to use windows authentication and when connection the error message says "no credential are available in the security package" (the logical step for it to ask me to enter username and password).
so i activate sa user and set new password for it and changed the authentication method to use "SQL authentication and windows authentication" from SQL studio and set ODBC to use SQL authentication but with no luck but new error message changed (photo attached), knowing i changed ODBC connection on device 1 (same as SQL server) and it's work fine for both SQL auth and windows auth.

 

[SQL_Hell]

The new error is a Kerberos authentication error, did you click NTLM in the ODBC config? You might have to create a service principle name (SPN) to get round these issues and someone with domain admin permissions will need to do that.

Here is a link that will help you trouble shoot.

https://support.microsoft.com/en-gb/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message