CODEWITHSUNDEEP
azure-sql-databasevirtual-machineintegrated-security
Arend Slomp
Arend Slomp

Reputation: 11

Authentication in Azure VM with AADLoginForWindows to Azure SQL using Azure AD Integrated Security

I want to connect to Azure SQL server from an Azure Virtual Desktop using Azure AD Integrated Security. I use an MS Access application that uses ODBC (v17/v18) to connect to the SQL Server.

The error I receive from ODBC (v17) is

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0xCAA9003B; state 10
ADAL received an empty response from the server during a WIA flow and could not continue.

TESTS FAILED!

For v18 it's the same:

Attempting connection [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated'). Error code 0xCAA9003B; state 10 ADAL received an empty response from the server during a WIA flow and could not continue.

How to solve this?

I configured the VM with extension AADLoginForWindows (I can succesfully login with my MS Entra ID credentials)

My klist session is showing: `

`PS C:\Users\my.username> klist

`Current LogonId is 0:0x1a05d6

`Cached Tickets: (0)
`PS klist sessions

`Current LogonId is 0:0x1a05d6
`[0] Session 2 0:0x1a06ed INTERNAL\my.username CloudAP:RemoteInteractive
`[1] Session 2 0:0x1a05d6 INTERNAL\my.username CloudAP:RemoteInteractive
`[2] Session 2 0:0x195d4f Window Manager\DWM-2 Negotiate:Interactive
`[3] Session 1 0:0xe960 Window Manager\DWM-1 Negotiate:Interactive
`[4] Session 0 0:0x84a9 \ NTLM:(0)
`[5] Session 2 0:0x195df0 Window Manager\DWM-2 Negotiate:Interactive
`[6] Session 0 0:0x3e7 WORKGROUP\vmrdpXXX-0$ NTLM:(0)
`[7] Session 0 0:0x3e4 WORKGROUP\vmrdpXXX-0$ Negotiate:Service
`[8] Session 1 0:0x8a61 Font Driver Host\UMFD-1 Negotiate:Interactive
`[9] Session 0 0:0x8a7d Font Driver Host\UMFD-0 Negotiate:Interactive
`[10] Session 2 0:0x195427 Font Driver Host\UMFD-2 Negotiate:Interactive
`[11] Session 0 0:0x3e5 NT AUTHORITY\LOCAL SERVICE Negotiate:Service
`[12] Session 1 0:0xe9b7 Window Manager\DWM-1 Negotiate:Interactive
`PS C:\Users\my.username>
`

I configured the ODBC with the correct database server, database name, and selected authentication method Authentication: ActiveDirectoryIntegrated

Upvotes: 1

Views: 420

Answers (1)

Bhavani
Bhavani

Reputation: 5317

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0xCAA9003B;

As per this MS document,

To use integrated Windows authentication, your domain's Active Directory must be federated with Microsoft Entra ID. Your client application (or a service) connecting to the database must be running on a domain-joined machine under a user's domain credentials.

That may be the reason for getting the above error. Instead of Integrated authentication, you can make use of SQL authentication or Active Directory MFA authentication.

Connection string for SQL authentication:

Driver={ODBC Driver 17 for SQL Server};Server=tcp:<serverName>.database.windows.net,1433;Database=<databaseName>;Uid=<userName>;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Connection string for Active Directory MFA authentication:

Driver={ODBC Driver 17 for SQL Server};Server=tcp:<serverName>.database.windows.net,1433;Database=<dbName>;Uid=<ADUserName>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryInteractive

Upvotes: 0

Related Questions