SQLCMD using older version of ODBC while connecting to Azure SQL database

Question

Hi,

I am trying to connect to my Azure sql database from an on-prem SQL server using sqlcmd.
Now I have both ODBC 13 and 17 installed on the source/on-prem server.
while I use sqlcmd, by default it uses ODBC 13 to connect to the azure sql database and it fails with below error.

C:\Users\XXXXXXX>SQLCMD -Q"SELECT GETDATE()" -d <db_name> -S <Azure_sql_DB_name> -G
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: The Microsoft Online Services Sign-In Assistant could not be found. Install it from http://go.microsoft.com/fwlink/?LinkId=234947. If it is already present, repair the installation. [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.

But when I specifically use the sqlcmd from the newer version folder , then it uses the ODBC 17 version and it connects successfully.

C:\Program Files\Microsoft SQL Server**Client SDK\ODBC\170\Tools\Binn**>SQLCMD -Q"SELECT GETDATE()" -d <db_name> -S <azure_SQL_DB_Name> -G

---

2021-09-07 09:43:54.410

(1 rows affected)

Problem is I have jobs configured in the on-prem SQL server to connect to this Azure database. So I want to know How can I configure my sqlcmd to use the newer version of ODBC i.e. ODBC version 17 by default?
Any leads are much appreciated.

Answer 1

Problem is I have jobs configured in the on-prem SQL server to connect to this Azure database. So I want to know How can I configure my sqlcmd to use the newer version of ODBC i.e. ODBC version 17 by default?

You need to use the new version of SQLCMD, which will use the proper ODBC version too. When the path to SQLCMD (or any exe) is not qualified, Windows searches the directories specified by the PATH environment variable in order until the exe is found.

The search path can be changed under Control Panel-->System and Security-->System-->Advanced system settings. Click the Environment Variables button, select the Path system variable and then edit. Select the C:\Program Files\Microsoft SQL Server*Client SDK\ODBC\170\Tools\Binn directory and press the "Move Up" button until it's before the other versions. I suggest you do the same for all of the *\170\* directories. You may need to restart the machine after the change.

Answer 2

Hi @Anonymous ,

If 2 or more ODBC installations on the same path, then will be loaded based on whichever version is on the path first, so you could try what Dan said.