MSSQL Server 2017 on Centos - AD auth - SSPI handshake failed

2

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
centos
active-directory
sql-server
asked on Server Fault Jan 9, 2019 by pszafer

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0