Issue description:
While connect to a Azure SQL database via AAD Integrated/Password authentication, fail with below error:
Client: Azure VM (SSMS). SQL authentication works normally.
Error message:
Cannot connect to xxx.database.windows.net.
===================================
One or more errors occurred. (mscorlib)
------------------------------
Program Location:
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Threading.Tasks.Task.Wait()
at System.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
at System.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
...
The attempt to connect to the server failed. (System.Data)
Analysis:
Azure SQL database side shows error 33155 state 1 which means client failed to get an AAD token.
Network trace shows:
After client VM performed TCP handshakes with AAD endpoint(20.190.154.19 in the below diagram), client VM didn't send a TLS hello packet, but sent a Fin packet to terminate the connection:
Test with sqlcmd, udl -> Failed to authenticate the user 'xxx' "Error code 0xCAA82EFD; state 10"
System event log shows: a fatal error occurred while creating a TLS credential
Schannel registry:
KeyExchangeAlgorithms(PKCS) is disabled
SSL 3.0 and TLS 1.2 is enabled
TLS 1.0, 1.1 is disabled
==================
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\PKCS
Enabled REG_DWORD 0x0
==================
Cause:
On Windows Server version 1709 Windows Server version 1803 Windows Server version 1809 Windows Server version 1903 Windows Server version 1909 Windows Server 2019, when TLS 1.2 is enabled and TLS 1.1 and lower is disabled, Key Exchange Algorithms PKCS is required.
Solution:
enable PKCS or enable TLS 1.0, 1.1 on client machine (Azure VM in this example)