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

ADIGA88

New member
Local time
Today, 17:18
Joined
Apr 5, 2020
Messages
19
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

Last edited:

SQL_Hell

SQL Server DBA
Local time
Today, 15:18
Joined
Dec 4, 2003
Messages
1,347
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

New member
Local time
Today, 17:18
Joined
Apr 5, 2020
Messages
19
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

SQL_Hell

SQL Server DBA
Local time
Today, 15:18
Joined
Dec 4, 2003
Messages
1,347
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

New member
Local time
Today, 17:18
Joined
Apr 5, 2020
Messages
19
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

SQL_Hell

SQL Server DBA
Local time
Today, 15:18
Joined
Dec 4, 2003
Messages
1,347
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

New member
Local time
Today, 17:18
Joined
Apr 5, 2020
Messages
19
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

SQL_Hell

SQL Server DBA
Local time
Today, 15:18
Joined
Dec 4, 2003
Messages
1,347
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

New member
Local time
Today, 17:18
Joined
Apr 5, 2020
Messages
19
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

SQL_Hell

SQL Server DBA
Local time
Today, 15:18
Joined
Dec 4, 2003
Messages
1,347
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.

 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom