Error establishing a connection to SQL Server in SAS Viya 3.4

[S_S]

Hi,

I was trying to establish a connection to MS SQL Server 2014 in SAS Studio Release 4.4 (for SAS Viya 3.4) but encountered the following errors:

 74         libname mslib odbc dsn="MyMSSQLServer" user="DataHub" password=XXXXXXXXXX;
 ERROR: CLI error trying to establish connection: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired :
        [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x102 : [unixODBC][Microsoft][ODBC Driver 13
        for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
        Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote
        connections. For more information see SQL Server Books Online.
 ERROR: Error in the LIBNAME statement.
 75        
 76         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

I crosscheck by creating a ODBC connection to the same SQL Server on my desktop and the connection is tested successfully.

I  would appreciate any pointer or advice on how to resolve this error.

FYI, the followings are the content of the .ini files:

[root@SASVIYA34 ~]# cat /opt/sas/spre/home/lib64/accessclients/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1

[root@SASVIYA34 ~]# cat /opt/sas/spre/home/lib64/accessclients/odbc.ini
[MyMSSQLServer]
Driver=ODBC Driver 13 for SQL Server
Description=My MS SQL Server
Trace=No
Server=192.168.1.111

Thanks in advance.

[SASKiwi]

The most likely explanation for the error is there is a firewall blocking the port the SAS app server is trying to communicate on. Talk to your SAS administrator regarding what firewall port rules are defined and what rule needs changing to enable communication. 

[S_S]

Hi  SAS Kiwi,

Thank you for your advice.

I have disabled the firewall and I am still getting the same error.

[SASKiwi]

Trying pinging the SQL server host name from your SAS app server. Does that work?

[S_S]

Hi SAS Kiwi,

Yes, I can ping my SQL Server from my SAS Viya 3.4 Server. I also don't think there is a latency issue (see ping output below).

[root@SASVIYA34 lib64]# ping 192.168.1.111
PING 192.168.1.111 (192.168.1.111) 56(84) bytes of data.
64 bytes from 192.168.1.111: icmp_seq=1 ttl=128 time=18.0 ms
64 bytes from 192.168.1.111: icmp_seq=2 ttl=128 time=38.6 ms
64 bytes from 192.168.1.111: icmp_seq=3 ttl=128 time=5.76 ms
64 bytes from 192.168.1.111: icmp_seq=4 ttl=128 time=38.3 ms
64 bytes from 192.168.1.111: icmp_seq=5 ttl=128 time=21.3 ms
64 bytes from 192.168.1.111: icmp_seq=6 ttl=128 time=15.8 ms
64 bytes from 192.168.1.111: icmp_seq=7 ttl=128 time=7.54 ms
64 bytes from 192.168.1.111: icmp_seq=8 ttl=128 time=22.9 ms
64 bytes from 192.168.1.111: icmp_seq=9 ttl=128 time=6.30 ms

--- 192.168.1.111 ping statistics ---
9 packets transmitted, 9 received, 0% packet loss, time 8013ms
rtt min/avg/max/mdev = 5.762/19.428/38.621/11.826 ms

Regards.

[SASKiwi]

@S_S - Did you manage to fix this? If so what was the solution?

[chuan · Accepted solution]

Hi,

I think this is a known compatibility issue of MS SQL Server and Unix ODBC.

Thank you very much for your help.