Connection problems - Error code 0x102

[keithslater]

Having problems getting connected to a SQL Server. Here is the command I'm using for testing -

sqlcmd -S 10.10.0.81,1434 -U username -P password -d database

I have assigned the static port 1434 to the named instance 10.10.0.81\SQLEXPRESS.

I have tested this connected via Navicat and it connects successfully through the IP and port. The port is wide open to all internal IPs.

unixODBC 2.3.1

Not sure what else to try at this point.

[yukiwongky]

@keithslater, to connect to a named instance on a static port, you need to use port number. Please see here. You can also check the related issues: #442, #340, #190, #175.

[keithslater]

I understand that. My example shows me using the port 1434.

[yukiwongky]

@keithslater, your problem may come from many places. It is difficult to pinpoint where the problem comes from. Here's a list of solution for you to try out:

1. Make sure SQL Server is not listening on a dynamic port (see here).

2. Make sure ODBC is installed properly. What is the output for:

1. odbcinst -j
2. odbcinst -q -d -n "ODBC Driver 13 for SQL Server"
3. sudo find /usr 2>/dev/null -name "libodbc*"
4. php -i | grep "Configure Command"

3. Disable any firewall, as mentioned in connection failed #190.

4. Make sure SQL Server is listening to the right port. Run the following to make sure the correct port is being listened to.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

[keithslater]

odbcinst -j
unixODBC 2.3.4
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/anvil/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

odbcinst -q -d
[PostgreSQL]
[MySQL]
[ODBC Driver 13 for SQL Server]

sudo find /usr 2>/dev/null -name "libodbc*"
/usr/lib64/libodbc.so
/usr/lib64/libodbc.so.2
/usr/lib64/libodbcdrvcfg2S.so.2
/usr/lib64/libodbcdrvcfg2S.so.2.0.0
/usr/lib64/libodbcinst.so
/usr/lib64/libodbcinst.so.2
/usr/lib64/libodbcinst.so.2.0.0
/usr/lib64/libodbcminiS.so.2
/usr/lib64/libodbcminiS.so.2.0.0
/usr/lib64/libodbcmyS.so
/usr/lib64/libodbcmyS.so.2
/usr/lib64/libodbcmyS.so.2.0.0
/usr/lib64/libodbcnnS.so
/usr/lib64/libodbcnnS.so.2
/usr/lib64/libodbcnnS.so.2.0.0
/usr/lib64/libodbc.so.2.0.0
/usr/lib64/libodbcpsqlS.so
/usr/lib64/libodbccr.so.2
/usr/lib64/libodbcpsqlS.so.2
/usr/lib64/libodbccr.so
/usr/lib64/libodbcdrvcfg1S.so
/usr/lib64/libodbccr.so.2.0.0
/usr/lib64/libodbcdrvcfg2S.so
/usr/lib64/libodbcdrvcfg1S.so.2
/usr/lib64/libodbctxtS.so
/usr/lib64/libodbcpsqlS.so.2.0.0
/usr/lib64/libodbctxtS.so.2
/usr/lib64/libodbcdrvcfg1S.so.2.0.0
/usr/lib64/libodbctxtS.so.2.0.0
/usr/lib64/libodbcminiS.so
/usr/local/lib/libodbcinst.so.2.0.0
/usr/local/lib/libodbcinst.so.2
/usr/local/lib/libodbcinst.so
/usr/local/lib/libodbcinst.la
/usr/local/lib/libodbc.so.2.0.0
/usr/local/lib/libodbc.so.2
/usr/local/lib/libodbc.so
/usr/local/lib/libodbc.la
/usr/local/lib/libodbccr.so.2.0.0
/usr/local/lib/libodbccr.so.2
/usr/local/lib/libodbccr.so
/usr/local/lib/libodbccr.la

php -i | grep "Configure Command"
returns nothing.

Double checking the port but I can connect to it on the port via Navicat.

[yukiwongky]

@keithslater, looks like you have 2 different unixODBC installed. I'm not sure if this is the source of the problem, but after you've checked the port number of your SQL Server, you can try to uninstall your current unixODBC, and then install the latest unixODBC version using apt-get. You can revisit issue 231 to see how to uninstall and install unixODBC properly.

[keithslater]

Looks like it is a port issue. I was able to run the command it gave me a different port than I was expecting. I set the port based on this article to 1434 - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

Is this not the correct port to set? I am in the process of getting access to Configuration Manager so I can double check the settings.

[yukiwongky]

Which IP (IP1 to IPAll) did you change? Make sure the IP you're configuring is the one you want to use. One thing you can try is to change the TCP Port in IPAll to 1434.

[keithslater]

So IP2 had the IP address listed I was trying to connect on 10.10.0.81 with the static port configured 1434. So from what I can tell, IP2 was configured correctly.

However, IPAll had the dynamic port listed that I was able to actually connect on.

We're going to change IP2 back to dynamic and move the static port to IPAll. Hopefully that should fix the issue. Not exactly sure why it didn't work.

[keithslater]

Switching the static port to IPAll fixed the issue.