BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
S_S
Obsidian | Level 7 S_S
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
chuan
Calcite | Level 5

Hi,

 

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

 

Thank you very much for your help.

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

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
Obsidian | Level 7 S_S
Obsidian | Level 7

Hi  SAS Kiwi,

 

Thank you for your advice.

 

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

 

 

SASKiwi
PROC Star

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

S_S
Obsidian | Level 7 S_S
Obsidian | Level 7

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
PROC Star

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

chuan
Calcite | Level 5

Hi,

 

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

 

Thank you very much for your help.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6435 views
  • 0 likes
  • 3 in conversation