Symptom: AD mode login fails - untrusted domain (Big Data Clusters)
Important
The Microsoft SQL Server 2019 Big Data Clusters add-on will be retired. Support for SQL Server 2019 Big Data Clusters will end on February 28, 2025. All existing users of SQL Server 2019 with Software Assurance will be fully supported on the platform and the software will continue to be maintained through SQL Server cumulative updates until that time. For more information, see the announcement blog post and Big data options on the Microsoft SQL Server platform.
On a SQL Server Big Data Cluster in Active Directory mode, a connection attempt may fail and the connection attempt returns the following error:
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
This can happens when you have configured DNS entries as CNAME pointing to an alias name of reverse proxy that distributes the traffic to Kubernetes nodes.
Root cause
When the endpoints are configured with DNS entries with CNAME pointing to an alias name of reverse proxy that distributes the traffic to Kubernetes nodes:
- Kerberos authentication process looks for a service principal name (SPN) that matches the entry for CNAME; not the true SPN registered by BDC in active directory
- Authentication fails
Confirm root cause
After authentication fails, check the cache of Kerberos tickets.
To check the cache of tickets, use klist command.
Look for a ticket with an SPN matching the endpoint you tried to connect to.
The expected ticket is not there.
In this example, a master endpoint, bdc-sql DNS record is CNAME set to reverse proxy named ServerReverseProxy
Resolve-DnsName bdc-sql
The following section shows the results from the previous command.
Name Type TTL Section NameHost
---- ---- --- ------- --------
bdc-sql.mydomain.com CNAME 3600 Answer ReverseProxyServer.mydomain.com
Name : ReverseProxyServer.mydomain.com
QueryType : A
TTL : 3600
Section : Answer
IP4Address : 193.168.5.10
Note
The following section references tshark. tshark is a command line utility installed as part of Wireshark network tracing utility).
To see the SPN requested from active directory, use tshark. The following command limits network tracing capture to Kerberos protocol communication and shows only krb-error (30) messages. These messages should contain failed SPN request messages.
tshark -Y "kerberos && kerberos.msg_type == 30" -T fields -e kerberos.error_code -e kerberos.SNameString
From a different command shell, try to connect to the master pod:
klist purge
sqlcmd -S bdc-sql.mydomain.com,31433 -E
See the following example output.
klist purge
Current LogonId is 0:0xf6b58
Deleting all tickets:
Ticket(s) purged!
sqlcmd -S bdc-sql.mydomain.com,31433 -E
sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
Check the tshark output.
Capturing on 'Ethernet 3'
25 krbtgt,RLAZURE.COM
7 MSSQLSvc,ReverseProxyServer.mydomain.com:31433
2 packets captured
Notice the client requests SPN MSSQLSvc,ReverseProxyServer.mydomain.com:31433 which doesn't exist. The connection attempt eventually fails with error 7. Error 7 means KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN Server not found in Kerberos database.
In the correct configuration, the client requests the SPN registered by BDC. In the example, the correct SPN would have been MSSQLSvc,bdc-sql.mydomain.com:31433.
Note
Error 25 means KDC_ERR_PREAUTH_REQUIRED - additional pre-authentication required. It can safely be ignored. KDC_ERR_PREAUTH_REQUIRED is returned on the initial Kerberos AD request. By default, the Windows Kerberos Client is not including pre-authentication information in this first request.
To see the list of SPN registered by BDC for master endpoint, run setspn -L mssql-master.
See the following example output:
Registered ServicePrincipalNames for CN=mssql-master,OU=bdc,DC=mydomain,DC=com:
MSSQLSvc/bdc-sqlread.mydomain.com:31436
MSSQLSvc/-sqlread:31436
MSSQLSvc/bdc-sqlread.mydomain.com
MSSQLSvc/bdc-sqlread
MSSQLSvc/bdc-sql.mydomain.com:31433
MSSQLSvc/bdc-sql:31433
MSSQLSvc/bdc-sql.mydomain.com
MSSQLSvc/bdc-sql
MSSQLSvc/master-p-svc.mydomain.com:1533
MSSQLSvc/master-p-svc:1533
MSSQLSvc/master-p-svc.mydomain.com:1433
MSSQLSvc/master-p-svc:1433
MSSQLSvc/master-p-svc.mydomain.com
MSSQLSvc/master-p-svc
MSSQLSvc/master-svc.mydomain.com:1533
MSSQLSvc/master-svc:1533
MSSQLSvc/master-svc.mydomain.com:1433
MSSQLSvc/master-svc:1433
MSSQLSvc/master-svc.mydomain.com
MSSQLSvc/master-svc
In the results above the reverse proxy address should not be registered.
Resolve
This section shows two ways to resolve the issue. After making the appropriate changes, run ipconfig -flushdns and klist purge in your client. Then attempt to connect again.
Option 1
Remove the CNAME record for each BDC endpoint in DNS and replace with multiple A records that points to each Kubernetes node or each Kubernetes master if you have more than one master.
Tip
The script described below uses PowerShell. See Installing PowerShell on Linux for more information.
You can use the following PowerShell Script to update DNS endpoints records. Run the script from any computer connected to the same domain:
#Specify the DNS server, example contoso.local
$Domain_DNS_name=mydomain.com'
#DNS records for bdc endpoints
$Controller_DNS_name = 'bdc-control'
$Managment_proxy_DNS_name= 'bdc-proxy'
$Master_Primary_DNS_name = 'bdc-sql'
$Master_Secondary_DNS_name = 'bdc-sqlread'
$Gateway_DNS_name = 'bdc-gateway'
$AppProxy_DNS_name = 'bdc-appproxy'
#Performing Endpoint DNS records Checks..
#Build array of endpoints
$BdcEndpointsDns = New-Object System.Collections.ArrayList
[void]$BdcEndpointsDns.Add($Controller_DNS_name)
[void]$BdcEndpointsDns.Add($Managment_proxy_DNS_name)
[void]$BdcEndpointsDns.Add($Master_Primary_DNS_name)
[void]$BdcEndpointsDns.Add($Master_Secondary_DNS_name)
[void]$BdcEndpointsDns.Add($Gateway_DNS_name)
[void]$BdcEndpointsDns.Add($AppProxy_DNS_name)
#Build arrary for results
$BdcEndpointsDns_Result = New-Object System.Collections.ArrayList
foreach ($DnsName in $BdcEndpointsDns) {
try {
$endpoint_DNS_record = Resolve-DnsName $DnsName -Type A -Server $Domain_DNS_IP_address -ErrorAction Stop
foreach ($ip in $endpoint_DNS_record.IPAddress) {
[void]$BdcEndpointsDns_Result.Add("OK - $DnsName is an A record with an IP $ip")
}
}
catch {
[void]$BdcEndpointsDns_Result.Add("MisConfiguration - $DnsName is not an A record or does not exists")
}
}
#show the results
$BdcEndpointsDns_Result
Option 2
Alternatively, it's possible to work around the issue by modifying the CNAME to point to the IP address of the reverse proxy rather than the name of the reverse proxy.
Confirm Resolution
After resoling the fix with one of the options above, confirm the fix by connecting to Big Data Cluster with active directory.
Next steps
Verify reverse DNS entry (PTR record) for domain controller.
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for