SSMS Active Directory Authentication with Azure SQL DB

2
votes

I am attempting to log into an Azure SQL Database using SSMS. I need to be able to log into the database with Active Directory Integrated Authentication. When attempting to login:

enter image description here

I receive this error: Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0xCAA90002; state 10 WSTrust response does not have recognized SAML assertion. (.Net SqlClient Data Provider)

The following are true:

  1. I am the Active Directory admin in the Azure SQL Server.
  2. The Azure SQL Server Firewall accepts all IP addresses
  3. I am specifying a database under the connection properties tab
  4. I have .NET 4.6 and I'm using SSMS v17.1
  5. Authentication works if I use "Active Directory Universal Authentication".

Any ideas? Thanks.

3
azure-sql-databasessmsazure-active-directorywindows-authentication

3 Answers

1
votes

This issue is with the AD Syncing options. In my environment, AD is not syncing passwords into the tenant. This prevents AD Integration Authentication and AD Password Authentication. The only authentication that works in this instance is AD Universal Authentication.

1
votes

This may be due to the old API of ADALSQL.dll that is used by SSMS for Active Directory password. The fact that Active Directory Universal (which uses newer API from ADAL.net) works indicates the issue is in the AD library, not SQL.

1
votes

I had the same error message, and it turned out to be caused by a password expiry. As soon as I renewed the password, the error message disappeared.