topic Re: Error establishing a connection to SQL Server in SAS Viya 3.4 in Developers

Error establishing a connection to SQL Server in SAS Viya 3.4

S_S — Wed, 24 Oct 2018 07:41:01 GMT

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.

Re: Error establishing a connection to SQL Server in SAS Viya 3.4

SASKiwi — Wed, 24 Oct 2018 08:08:19 GMT

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.

Re: Error establishing a connection to SQL Server in SAS Viya 3.4

S_S — Wed, 24 Oct 2018 08:15:57 GMT

Hi SAS Kiwi,

Thank you for your advice.

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

Re: Error establishing a connection to SQL Server in SAS Viya 3.4

SASKiwi — Wed, 24 Oct 2018 09:05:04 GMT

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

Re: Error establishing a connection to SQL Server in SAS Viya 3.4

S_S — Wed, 24 Oct 2018 09:13:49 GMT

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.

Re: Error establishing a connection to SQL Server in SAS Viya 3.4

SASKiwi — Thu, 22 Nov 2018 02:04:07 GMT

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

Re: Error establishing a connection to SQL Server in SAS Viya 3.4

chuan — Thu, 22 Nov 2018 04:57:02 GMT

Hi,

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

Thank you very much for your help.