Question Re: SQL Anywhere Remote server connection to SQL Server in Technology Q&A

SQL Anywhere Remote server connection to SQL Server

ovehalseth-1 — Mon, 20 Apr 2020 06:11:13 GMT

I'm trying to link tables from MS SQL Server(on Windows) to SQL Anywhere(on Linux)

Have installed SQL Server driver: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17

Have tested connection to SQL Server with sqlcmd and isql and both connects fine.

In SQL Anywhere I have made a Remote Server with this query:

<code>CREATE SERVER "lnkIS" CLASS 'MSSODBC'USING'host=xxx.xxx.xxx.xxx;port=nnnn;driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1;uid=wis;pwd=xxx;eng=WisData';

But when I test the connection I get:

<code>Connection failed.
Unable toconnectto server 'lnkIS':[Sybase][ODBC Driver Manager] Unable to load driver /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'lnkIS':[Sybase][ODBC Driver Manager] Unable to load driver /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
SQLCODE:-656
SQLSTATE: HY000
SQL Statement:SELECT FIRST table_name FROM dbo.sp_remote_tables('lnkIS',NULL,NULL,NULL,1)ORDERBY1

I have not found any examples on how to use a driver other than Sybase. Can anyone give an example on how to connect to MS SQL Server?

Re: SQL Anywhere Remote server connection to SQL Server

chris_keating — Tue, 21 Apr 2020 18:09:26 GMT

SQL Anywhere has no specific experience with or testing of SQL Server ODBC on non Windows platforms. While we have no specific experience, we had worked with an environment that was able to successfully resolve connectivity as described below and using a DSN that was defined:

1. Configure unixODBC (which is part of recommended setup discussed in the MSS documentation):

export LD_PRELOAD=/lib64/libcrypto.so.1.0.0
export ODBCINI=/etc/unixODBC/odbc.ini
export ODBCINST=/etc/unixODBC/odbcinst.ini

2. Create the following symbolic links (as per SAP note 1644499) - you may need to confirm the specific symbolic link references for your environment:

ln -s /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbc.so.1
ln -s /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.1

3. Add the driver and create a DSN by modifying the odbc.ini and odbcinst.ini files as in this example:

/etc/unixODBC/odbc.ini
[<your_dsn_name>]
Driver = ODBC Driver 17 for SQL Server
Description = <DSN Description>
Server = <server_ip>
Port = <port>
Database = <database>
/etc/unixODBC/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1