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.
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
Description=ODBC for PostgreSQL
Description=ODBC for MySQL
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
[root@SASVIYA34 ~]# cat /opt/sas/spre/home/lib64/accessclients/odbc.ini
Driver=ODBC Driver 13 for SQL Server
Description=My MS SQL Server
Thanks in advance.
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.
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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.