I need to enable Active Directory authentication on SQL Server 2019 running on CentOS 7.
The server centos-1 alresdy has sssd pre-configured to us AD authentication. However, I followed the official Microsoft guide on how to enable Windows authentication on SQL Server on Linux:
- Added Linux server to the domain with
user-1@centos-1:~$ sudo realm join SD.DOMAIN.COM -U '[email protected]' -v
The server can query the domain
user-1@centos-1:~$ id sql_server_account
uid=10608(sql_server_account) gid=13502(service_accounts)
- Created an SPN for service account used to run MSSQL in Windows
setspn -A MSSQLSvc/centos-1.sd.domain.com:1433 sql_server_account
setspn -A MSSQLSvc/CENTOS-1:1433 sql_server_account
- Created keytab file and copied it to centos-1 machine with SQL Server
ktpass /princ MSSQLSvc/centos-1.sd.domain.com:[email protected] /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SD\sql_server_account /out mssql.keytab -setpass -setupn /kvno 2 /pass <passw>
ktpass /princ MSSQLSvc/centos-1.sd.domain.com:[email protected] /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser SD\sql_server_account /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <passw>
ktpass /princ [email protected] /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser SD\svc_sql_server_dev /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <passw>
ktpass /princ [email protected] /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser SD\svc_sql_server_dev /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <passw>
- Configured MSSQL to use the keytab
user-1@centos-1:~$ sudo mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
user-1@centos-1:~$ sudo service mssql-server restart
While AD can authenticate users connecting to the server, SQL Server cannot create Windows logins. It says
Windows NT user or group 'SD\db_users' not found. Check the name again.
sssd.log says that
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [dp_get_account_info_handler] (0x0200): Got request for [0x12][BE_REQ_USER_AND_GROUP][name=sd\[email protected]]
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sss_domain_get_state] (0x1000): Domain SD.DOMAIN.COM is Active
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sdap_get_groups_next_base] (0x0400): Searching for groups with base [dc=sd,dc=domain,dc=com]
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sdap_get_generic_ext_step] (0x0400): calling ldap_search_ext with [(&(cn=sd\5cdb_users)(objectClass=group)(cn=*)(&(gidNumber=*)(!(gidNumber=0))))][dc=sd,dc=domain,dc=com].
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sdap_get_generic_ext_step] (0x1000): Requesting attrs: [objectClass]
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sdap_get_groups_process] (0x0400): Search for groups, returned 0 results.
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sysdb_search_object_attr] (0x0400): No such entry.
(Mon May 24 19:45:11 2020) [sssd[be[SD.DOMAIN.COM]]] [sysdb_delete_by_sid] (0x0400): search by sid did not return any results.
I believe the problem is that mssql-server is passing sd\db_users entire account name without omitting the domain prefix sd.
Is there a setting or way to let MSSQL to use AD in my setup?
Fixed it by added this line to /etc/sssd/sssd.conf under
[domain/SD.DOMAIN.COM]The
re_expressionisDefault regular expression that describes how to parse the string containing user name and domain into these components. Each domain can have an individual regular expression configured. For some ID providers there are also default regular expressions.