We have SQL Server 2017 installed on CentOS and connected to domain according to this tutorial - https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017 .
Every day every 12-24 hours SQL rejecting AD domain logins:
sssd[18880]: ; TSIG error with server: tsig verify failure
# Error: 17806, Severity: 20, State: 14.
# SSPI handshake failed with error code 0x80090308, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The token supplied to the function is invalid
# Error: 18452, Severity: 14, State: 1
# Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
To get AD domain login working again I have to run on Domain controller two commands:
setspn -D MSSQLSvc/**fqdn**:1433 mssql
setspn -A MSSQLSvc/**fqdn**:1433 mssql
I checked and change mssql user (which retrieved kerberos ticket) to be administrator, if it's permission issue, but still SQL Server is not updating SPN.
Maybe someone can give me a hint what am I missing?
Configuring steps:
$ yum install sssd ntp authconfig krb5-workstation openldap-clients sssd-tools
$ realm join domain.com -U "mssql@DOMAIN.COM"
$ kinit mssql@DOMAIN.COM
On Windows
setspn -A MSSQLSvc/mssql.domain.com:1433 mssql
And back on Centos:
$ kinit mssql@DOMAIN.COM
$ kvno MSSQLSvc/mssql.domain.com:1433
$ ktutil
$ ktutil: addent -password -p MSSQLSvc/mssql.domain.com:1433@DOMAIN.COM -k **<kvno from above>** -e aes256-cts-hmac-sha1-96
$ ktutil: addent -password -p MSSQLSvc/mssql.domain.com:1433@DOMAIN.COM -k **<kvno from above>** -e rc4-hmac
$ ktutil: wkt /var/opt/mssql/secrets/mssql.keytab
$ ktutil: quit
$ ktutil: rkt /etc/krb5.keytab
$ ktutil: list
$ ktutil: delent <slot num> # delete all hosts which are not UPN
$ ktutil: wkt /var/opt/mssql/secrets/mssql.keytab
$ ktutil: quit
$ chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
$ chmod 400 /var/opt/mssql/secrets/mssql.keytab
$ /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
$ systemctl restart mssql-server
User contributions licensed under CC BY-SA 3.0