Problem to login from linux using ActiveDirectoryPassword option

Moshe Lin 11

Hi,

I'm trying to login to Azure SQL DB using Authentication=ActiveDirectoryPassword option (-G option in sqlcmd) and I'm getting a timeout error (I defined the timeout to be 120 second and it didn't help):

./sqlcmd -S tcp:erz-azure-si.database.windows.net -G -U USER@keyman .COM -P PASSWORD -l 120
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Failed to authenticate the user 'USER@keyman .COM' in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0xA190; state 41360
Could not find any endpoints.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Timeout error [258]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in login response.

Trying to work with SQL credentials works well, also trying to login via ActiveDirectory on Windows works well.
I'm working with RedHat 7.6 and using ODBC version 17.6 (libmsodbcsql-17.6.so.1.1), please find the library the odbc is working with:

ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
linux-vdso.so.1 => (0x00007ffc21517000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f0581ff6000)
librt.so.1 => /lib64/librt.so.1 (0x00007f0581dee000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f0581bd3000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f05818ea000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f058169d000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f0581395000)
libm.so.6 => /lib64/libm.so.6 (0x00007f0581093000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f0580e7d000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0580c60000)
libc.so.6 => /lib64/libc.so.6 (0x00007f0580893000)
/lib64/ld-linux-x86-64.so.2 (0x0000559f7ad1a000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f0580678000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f0580473000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f0580264000)
libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f057fe01000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f057fbfc000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f057f9e3000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f057f7bb000)
libz.so.1 => /lib64/libz.so.1 (0x00007f057f5a5000)
libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f057f343000)

I don't here libcurl, maybe this is the root cause of my problem?

Any idea how to solve the timeout problem?

Thanks,
Moshe

Anurag Sharma 17,606 Reputation points

2020-10-30T11:45:50.683+00:00

Hi @Moshe Lin , welcome to Microsoft Q&A forum. Apologies for the delay in response.

We are checking this and will get back to you at the earliest.
Anurag Sharma 17,606 Reputation points

2020-11-03T12:05:04.92+00:00

Hi @Moshe Lin , would it be possible for you to try connecting with SQL authentication just to understand if this is connectivity issue, firewall issue or latency?

Also I noticed an article that says as below, however as we are using Active Directory password, it could work.
Moshe Lin 11 Reputation points

2020-11-04T13:02:52.83+00:00

Hi Anurag,

Thanks for your response.
Trying to login without -G (activedirectorypassword) option seems to work well, I don't even need to supply a timeout value:
./sqlcmd -S tcp:erz-azure-si.database.windows.net -U ADMINUSER -P PWD

I managed to run queries against the DB, while the same login using ActiveDirectoryPassword authentication failed.

I saw the the note you attached, from what I read it seems that ODBC on linux do support ActiveDirectoryPassword authentication starting from 17.6 version.

Regards,
Moshe
Rene Carrier 1 Reputation point

2020-11-09T19:21:58.607+00:00

Yes I confirmed that it's working on Linux but may be you are only missing the database name.
Normally your command should include it as most of the time users are DB-contained users in Azure.
here an example that I've use on Centos 7 with ODBC driver 17.6.1.1.

sqlcmd -I -S "MySQLserver.database.windows.net" -d "mydbname" -G -U "myuser@keyman .com" -P "******"
Moshe Lin 11 Reputation points

2020-11-12T14:11:03.097+00:00

Hi Rene,

Thanks for your response.
I tried your suggestion and it fails with the same timeout error also when I specify the database.
I want to mention that I tried to run the same command on my macbook and got the error, which made me think that maybe there is a problem in Azure side (although I managed to connect on Windows).
According to below link, the message came from Azure Active Directory:

https://social.msdn.microsoft.com/Forums/en-US/6fd3f1b7-d37a-46f9-9b72-4fb15927b0a3/ms-sqlserver-odbc-linux-driver-131-failed-connection-to-azure-sql-db?forum=sqltools

My question is if you encountered such a problem before? do you think we should check the Active directory settings in Azure?

Thanks,
Moshe
Rene Carrier 1 Reputation point

2020-11-12T14:32:13.933+00:00

When we troubleshoot our issues, We've tried several things on purpose to see what's the resulting behavior and the majority of what we try end up with the same kind of generic error from the linux driver with the timeout.

I don't know how your Azure AD is configured and the type of users in it but here what I can say, If you have on-premise Ad users synched to Azure and use ADFS for their authentication or any other kind of federation, this is not currently supported from what I know with the Linux driver. In our tenant we are using PTA which works really great but if you synch password hashes, this should be fine as well.

So to work, my understanding is that this is Azure AD that need to complete the authentication, so if you are federated then you can also try using an account define only in your Azure AD instead and grant that user in your Azure SQL DB.

Share via

Problem to login from linux using ActiveDirectoryPassword option

Your answer