question

DeySuman-7017 avatar image
0 Votes"
DeySuman-7017 asked SaurabhSharma-msft commented

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

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.


sql-server-generalazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered SaurabhSharma-msft commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition to Dan's post, somewhat unpractically when you install a new version of SQL Server on a machine where you have an older, the folders for the new version are appended to the end of PATH, so you may be keeping running the old version if you don't watch out.

0 Votes 0 ·

Hi @DeySuman-7017,

We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh

0 Votes 0 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered ErlandSommarskog commented

Hi @DeySuman-7017,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If 2 or more ODBC installations on the same path, then will be loaded based on whichever version is on the path first

Not really. An application will use the ODBC driver that is given in the connection string. ODBC SQL Server 13 and ODBC SQL Server 17 have different names,

What we were talking about was SQLCMD. If you just type SQLCMD, Windows will pick the one that comes first in the path. And different versions of SQLCMD have different connection strings with different drivers.

0 Votes 0 ·