I cannot get an ODBC connection to remote SQL Server 2014 established

Support / Troubleshooting
1

In a VPS, I have 2 VM’s connected via a local nic.
One VM is a Server 2012 R2 instance with MS SQL Server while the other is an Ubuntu 22.04 VM with a Twingate Connector installed.

The server is ABCD01.
The domain is abc.local.
I can interact with the server ABDC01 from a remote desktop (Twingate Client installed) without issue.
However, to access the SQL Server, I need to setup an ODBC connection on the remote Windows 10 PC and I cannot get this to work.

The ODBC settings are…

Microsoft SQL Server ODBC Driver Version 10.00.19041

Data Source Name: VPMsql
Data Source Description:
Server: ABCD01\MYOBACCT
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

… the result of running TEST CONNECTION are…

Microsoft SQL Server ODBC Driver Version 10.00.19041

Running connectivity tests…

Attempting connection
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.
Disconnecting from server

TESTS FAILED!
…

If, however, I change ABCD01\MYOBACCT to 200.100.55.88\MYOBACCT, the public IP of the server, ODBC connects perfectly, obviously bypassing any Twingate functionality.

I have tried \ABCD01\MYOBacct - no change.

Twingate has the following resources set up in the server network:
*abcd01
abcd01

Obviously I am missing something and any assistance to resolve this issue would be appreciated.
Regards

2

Can you add the SQL Server name explicitly and give it another shot?

There is a small chance you are using named pipes to connect ODBC is an older technology of course. Since you can connect through a public internet I think not.

Maybe adding TCP will help anyway?

3

@Jason,
I simply cannot get ODBC to work unless I take Twingate out of the loop. Go via a normal public IP ODBC works but it will not coexist with Twingate. The applications that do work with Twingate (everything else so far) won’t coexist with ODBC on a “different” network.
My remote configuration in the VPS is:

  • VM Ubuntu 22.04 running connector and linked via local nic to…
  • VM Server 2012 R2 with SQL 2014 (SQL Express).
    Windows firewall allows all ports etc. from the connector nic.

Server 2012 R2 is near EOL and does not handle WSL. Server 2022 does handle WSL and by the end of the year I would need to move the client to that platform.

I am beginning to think that MAYBE the answer to satisfy ODBC is to have the connector on the server rather than a “machine on the side”. Do you have an opinion regarding this hypothesis?

Does Twingate play well with WSL? Nested hyper-v’s are not an option.

Definetely using TCP/IP.
Regards

4

we do have a chocolatey package to install the connector on windows. Perhaps that is the solution here. There isn’t a technical reason why you shouldn’t be able to connect unless it is something between where the connector is running and the ODBC source.

Any chance you can try the chocolatey package?

5

The problem with using the Chocolately package is that the server is a VM and I would then require a nested VM setup which, for some reason that I have not persued, my VSP supplier doesn’t seem to be excited about. As I understand it, WSL removes this issue.

6

Yeah ok I see what you meant there.

What happens if you try and add the private IP of the SQL Server to Twingate as a resource and then connect that way. Does the VM have a private IP that the machine running the connector can see? Can you then connect to SQL Server from that machine where the connector is running?

If so you can add 200.100.55.88 as a resource in TW and it will use the connector for that IP. The weird thing would be the connector would “go out” and “come back” to get to the SQL Server which might cause delays. I doubt the routing would take the connection out to the public internet as it would be caught before that, but it would still be a bit.

7

Tried something like that already without ODBC success; everything else ok.
@Emrul setup a tailored connector which has worked wonderfully but not for ODBC.
The Connector vm has a private Nic as does the server vm and they see each other.

8

Is the ODBC Auth Windows Auth?

9

Tried with windows auth and user. Also dynamic port and port 1433. No active firewall on connector vm.

10

Can you connect via ODBC to that server from the connector machine? I know you can ping it, but can you ODBC to the server from the connector machine?

11

Than you for your suggestion. However, given the pending EOL of server 2012 R2, I have decided to setup a server 2016 copy of the 2012 system for testing purposes. This has, I feel, has a couple of advantages:

  1. We can “mess” with the server as it will be a testing platform;
  2. Using SSMS on server 2016 is much easier and we can manage listening ports etc. far easier;
  3. I am convinced this is not a Twingate issue, rather it is caused by port management, or lack of it, for read only db test connections in server 2012 R2 and SSMS. I may still try and dig into this one but I really need to “get this done” or move on and I suspect 2016 will resolve this issue.
1 Like