Can't connect MS SQL ODBC to MS SQL Sever 2016 Remotly (1 Viewer)

ADIGA88

Member
Local time
Tomorrow, 01:24
Joined
Apr 5, 2020
Messages
94
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.
 

Attachments

  • Device 2 Faild Connection .jpg
    Device 2 Faild Connection .jpg
    128.2 KB · Views: 399
  • Device 1  successful connection.jpg
    Device 1 successful connection.jpg
    98 KB · Views: 413
Last edited:

SQL_Hell

SQL Server DBA
Local time
Today, 22:24
Joined
Dec 4, 2003
Messages
1,360
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.
 
Last edited:

ADIGA88

Member
Local time
Tomorrow, 01:24
Joined
Apr 5, 2020
Messages
94
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
 

Attachments

  • Device 1 CData connection problem.jpg
    Device 1 CData connection problem.jpg
    123.6 KB · Views: 384
  • Device 2 Telnet to Device 1 SQL.jpg
    Device 2 Telnet to Device 1 SQL.jpg
    18.6 KB · Views: 394

SQL_Hell

SQL Server DBA
Local time
Today, 22:24
Joined
Dec 4, 2003
Messages
1,360
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

Member
Local time
Tomorrow, 01:24
Joined
Apr 5, 2020
Messages
94
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)
 

Attachments

  • Enable TCPIP  2.jpg
    Enable TCPIP 2.jpg
    43.6 KB · Views: 393
  • Enable TCPIP 1.jpg
    Enable TCPIP 1.jpg
    36.5 KB · Views: 250
  • Nmap Scan.JPG
    Nmap Scan.JPG
    49 KB · Views: 340

SQL_Hell

SQL Server DBA
Local time
Today, 22:24
Joined
Dec 4, 2003
Messages
1,360
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
 
Last edited:

ADIGA88

Member
Local time
Tomorrow, 01:24
Joined
Apr 5, 2020
Messages
94
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)
 

Attachments

  • ODBC Connector Test.jpg
    ODBC Connector Test.jpg
    209.2 KB · Views: 412
  • Telnet Test .jpg
    Telnet Test .jpg
    32.6 KB · Views: 267

SQL_Hell

SQL Server DBA
Local time
Today, 22:24
Joined
Dec 4, 2003
Messages
1,360
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

Member
Local time
Tomorrow, 01:24
Joined
Apr 5, 2020
Messages
94
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.
 

Attachments

  • ODBC SQL Auth.jpg
    ODBC SQL Auth.jpg
    200.4 KB · Views: 416
  • ODBC Windows Auth.jpg
    ODBC Windows Auth.jpg
    190.3 KB · Views: 389

SQL_Hell

SQL Server DBA
Local time
Today, 22:24
Joined
Dec 4, 2003
Messages
1,360

Users who are viewing this thread

Top Bottom