UID and PWD not working in odbc.ini file

Question

I'm the admin for the SAS application running on Linux servers. I'm supporting a project that migrates the data source from Cloudera to an Azure platform. ODBC is used to connect SAS application to the data sources via odbc.ini and odbcinst.ini files. The connections from SAS to Cloudera data source uses a simple "dsn=cloudera" SAS statement where the connection details are listed in the odbc.ini file that includes the UID and PWD values. For the new Azure data source, I've installed the MS SQL Server ODBC driver and added a new DSN in the odbc.ini file. I was expecting with the SQL Server driver that a similar dsn=azure SAS statement would work. However, an error occurs requiring the addition of the UIS and PWD in the SAS statement, even though the UID and PWD are set in the odbc.ini. It appears that the SLQ server driver or manager does not recognize the UID and PWD values in the odbc.ini file. Below are the details for the ODBC environment and associated odbc.ini settings.

#-> ./odbcinst -j

unixODBC 2.3.4

DRIVERS............: /usr/local/unixODBC-2.3.4/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/unixODBC-2.3.4/etc/odbc.ini

FILE DATA SOURCES..: /usr/local/unixODBC-2.3.4/etc/ODBCDataSources

USER DATA SOURCES..: /usr/local/unixODBC-2.3.4/etc/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

ODBC.ini DSN
[azure]

Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.3.1

Server=tcp:vrai-synapse-dev.com,1433

UID=############

PWD=########

DATABASE=vrai_dev

Authentication=ActiveDirectoryPassword

TrustServerCertificate=yes

SAS Log of two connections. One successful since UID and PWD were included in the SAS statement and the other failed.

 73         libname azure1 odbc dsn=azure user='XXXXXXXXXXXX' pwd=XXXXXXXXXXXXXX; NOTE: Libref AZURE1 was successfully assigned as follows:        Engine:        ODBC        Physical Name: azure 74         

libname azure2 odbc dsn=azure; ERROR: CLI error trying to establish connection: [unixODBC][Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Both User and         Password options must be specified, if Authentication option is 'ActiveDirectoryPassword'. ERROR: Error in the LIBNAME statement.

Please provide guidance to setup odbc connection that does not require the addition of the UID and PWD values in the SAS statement.

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

For ActiveDirectoryPassword authentication, the ODBC driver expects both UID and PWD to be provided explicitly when making a connection.

While the odbc.ini file allows you to define the connection details, certain authentication methods may override the default behavior and require credentials to be passed at runtime.

The most straightforward solution is to include the UID and PWD in your SAS statement. While this may not be ideal for security reasons, it ensures that the SAS application can authenticate correctly.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful