How to connect to SQL Server in Altair SLC on MacOS

Vinay_C

I am trying to connect to SQL Server on MacOS. I am getting this error:

ERROR: Could not load SQLSERVR engine or one of its dependent libraries. Please ensure that youhave installed the relevant client libraries for connecting to SQLSERVR and that they canbe located by Altair SLC before trying to connect.

I can access the database from VSCODE and from the terminal. ODBC is configured, I can locate the driver path through odbcinst -j command. I tried changing /etc/odbc.ini as recommended in one of the community blogs but it didn't help.

I checked Proc SETINIT and confirmed that SQLSERVER is an available option.

Product: Altair Analytics Workbench Version: 5.23.7.0.520-GA-release

Any help will be greatly appreciated.

PROC SQL;

CONNECT TO sqlservr(

    server='some-server.database.windows.net'

    port=1433

driver='ODBC Driver 18 for SQL Server'

database='mydb'

    user='myuser'

password='mypassword'.

encrypt='yes'

);

SELECT * FROM CONNECTION TO sqlservr(

  SELECT 1 AS TestConnection

);

DISCONNECT FROM sqlservr;

QUIT;

Nico Chart_21517

The error message…
ERROR: Could not load SQLSERVR engine or one of its dependent libraries
…essentially means that SLC didn't find the expected shared libraries on DYLD_LIBRARY_PATH.
There are many places you could set/modify DYLD_LIBRARY_PATH but a good recommendation for SLC would be: You can create a script called /Applications/altairslc.app/altairslcenv.shcontaining the necessary command for updating that environment variables. You might want to set ODBCINI and ODBCSYSINI as well as DYLD_LIBRARY_PATH in that script.

n.b. SLC needs to find the unixODBC libraries on DYLD_LIBRARY_PATH as well as the SQLServer ODBC libraries that ought to be found via the odbc .ini files.

Vinay_C

I created the script altairslcenv.sh in the folder /Applications/altairslc.app and sourced it in ~/.zshrc but the error remains the same. Please see the content of altairslcenv.sh, odbcinst.ini, and odbc.ini

Content of altairslcenv.sh -

#!/bin/bash

# Set the path to the odbc.ini file

export ODBCINI=/opt/homebrew/etc/odbc.ini

# Set the path to the directory containing odbcinst.ini

export ODBCSYSINI=/opt/homebrew/etc

# Set the path to find UnixODBC and SQL Server libraries

export DYLD_LIBRARY_PATH=/opt/homebrew/lib:$DYLD_LIBRARY_PATH

Content of odbcinst.ini -

[ODBC Driver 18 for SQL Server]

Description=Microsoft ODBC Driver 18 for SQL Server

Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib

UsageCount=1

Is there a specific content that must go in odbc.ini? The current content is

[SQL Server]

Driver=ODBC Driver 18 for SQL Server

Server=some-server.database.windows.net

Port=1433

Database=some-database

Encrypt=yes

TrustServerCertificate=no

Nico Chart_21517

When checking a unixODBC configuration you should use:

odbcinst -j # this command reports what .ini files you are actually using

isql -v dsn user passwd # this command demonstrates that you can connect to the database okay

And within the Altair Analytics Workbench you can check that your environment variable settings have been correctly set up by executing sas language code like this:

%put DYLD_LIBRARY_PATH is "%sysget(DYLD_LIBRARY_PATH)";

Nico

Vinay_C

isql -v dsn user passwd  # This works

%put DYLD_LIBRARY_PATH is "%sysget(DYLD_LIBRARY_PATH)";

%put DYLD_LIBRARY_PATH is "%sysget(DYLD_LIBRARY_PATH)";
WARNING: The argument DYLD_LIBRARY_PATH to macro function SYSGET does not name an existing environment variable
DYLD_LIBRARY_PATH is ""

It appears that AltairSLCenv.sh is not getting initialized or AltairSLC is not reading these environment variable. I have stored is file in /Applications/AltairSLC.app. Should it be in the Contents folder under /AltairSLC.app?

Nico Chart_21517

You are correct, the filename should be:

/Applications/altairslc.app/Contents/altairslcenv.sh

Vinay_C

Then I guess the issue is with the odbc.ini or odbcinst.ini files and the way altairslc reads it. Is there a specific structure or content to these files that altair understands. The environment variables are getting set properly.

213 %put DYLD_LIBRARY_PATH is "%sysget(DYLD_LIBRARY_PATH)";
DYLD_LIBRARY_PATH is "/opt/homebrew/lib"
214 %put ODBCSYSINI is "%sysget(ODBCSYSINI)";
ODBCSYSINI is "/opt/homebrew/etc"
215 %put ODBCINI is "%sysget(ODBCINI)";
ODBCINI is "/opt/homebrew/etc/odbc.ini"

Nico Chart_21517

If you are still getting the error message "Could not load SQLSERVR engine or one of its dependent libraries" this really does seem to mean that SLC didn't find the expected shared libraries on DYLD_LIBRARY_PATH. Maybe set the DYLD_LIBRARY_PATH so that both the unixODBC shared libraries and the SQLServer libraries are all present on that path?

Vinay_C

Nico,

both the drivers libodbc.2.dylib, libodbc.dylib and libmsodbcsql.18.dylib are in the opt/homebrew/lib folder. I have reached the ceiling of my technical knowhow :-).

Is there any other way I could get help on this topic?

Vinay_C

Any suggestions? or is there any other place we can set/save these dylib files?