MSSQL RHEL 7.7

Purpose

To install MSSQL 2017 on RHEL 7.7 (ends August 30, 2021; Final RHEL 7 EUS Release) hosted on AWS, with AD authentication.

Config

• m5a.xlarge
• 100GB GP2 boot (SSD)
• 500GB gp2 data (SSD)
• 1024GB ti backup (Magentic)

Notes

sudo hostnamectl set-hostname mssql01
sudo yum upgrade -y
sudo yum install firewalld yum-cron setroubleshoot-server -y
sudo enable --now firewalld
sudo firewall-cmd --permanent --add-port=1433/tcp
sudo firewall-cmd --reload

I am unable to find yum-cron in the default repositories provided by Red Hat for RHEL 7.7 on AWS.

sudo reboot
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum install -y mssql-tools unixODBC-devel mssql-server
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo /opt/mssql/bin/mssql-conf setup

Configure storage

sudo mkfs.btrfs /dev/nvme1n1
sudo mkfs.btrfs /dev/nvme2n1
sudo mkdir /mnt/data
sudo mkdir /mnt/backup
sudo chown mssql:mssql /mnt/data
sudo chown mssql:mssql /mnt/backup

Update fstab to mount accordingly.
Now update storage location

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mnt/data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /mnt/data
sudo systemctl restart mssql-server

At this point I can use the database services with the SA account. No issues yet.
Now setup AD Authentication. In this case I want to authenticate against two domains.
First enroll the linux host into AD

sudo yum -y install realmd krb5-workstation oddjob oddjob-mkhomedir sssd samba-common-tools
sudo realm join your.domain.tld -U 'michael.mast@YOUR.DOMAIN.TLD' -v

Then create a dedicated user account in AD, set the SPN for the account, and generate the keytab.

• On domain controller

C:\Windows\system32>setspn.exe -A MSSQLSvc/mssql01.your.domain.tld:1433 dedicated_user

• On the Linux host check for the kvno number of the account.

[ec2-user@mssql01 ~]$ kvno MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD
MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMNAIN.TLD: kvno = 2

• Create keytab. I followed instructions to use the MSA instead of host SPN. Was only able to authenticate using MSA.

Keytab with MSA

[ec2-user@mssql01 ~]$ sudo ktutil
ktutil:  addent -password -p MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD -k 2 -e aes256-cts-hmac-sha1-96
Password for MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD -k 2 -e rc4-hmac
Password for MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD -k 2 -e aes256-cts-hmac-sha1-96
Password for MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD -k 2 -e rc4-hmac
Password for MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD:
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2 MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD
   2    2 MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD
   3    2     MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD
   4    2     MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD
ktutil:  wkt /var/opt/mssql/secrets/mssql.keytab
ktutil:  addent -password -p mssqlinux@YOUR.DOMAIN.TLD -k 2 -e aes256-cts-hmac-sha1-96
Password for mssqlinux@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p mssqlinux@YOUR.DOMAIN.TLD -k 2 -e rc4-hmac
Password for mssqlinux@YOUR.DOMAIN.TLD:
ktutil:  wkt /var/opt/mssql/secrets/mssql.keytab
ktutil:  quit
[ec2-user@mssql01 ~]$ sudo ktutil
ktutil:  rkt /var/opt/mssql/secrets/mssql.keytab1
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2 MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD
   2    2 MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD
   3    2     MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD
   4    2     MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD
   5    2                 mssqllinux@YOUR.DOMAIN.TLD
   6    2                 mssqllinux@YOUR.DOMAIN.TLD
ktutil:  quit

Tell MSSQL to use the domain account for authentication purposes.

[ec2-user@mssql01 ~]$ sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount mssqllinux

Keytab with SPN

[ec2-user@mssql01 ~]$ sudo ktutil
ktutil:  addent -password -p MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD -k 2 -e aes256-cts-hmac-sha1-96
Password for MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD -k 2 -e rc4-hmac
Password for MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD -k 2 -e aes256-cts-hmac-sha1-96
Password for MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD:
ktutil:  addent -password -p MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD -k 2 -e rc4-hmac
Password for MSSQLSvc/mssql01:1433@YOUR.DOMAIN.TLD:
ktutil: wkt /var/opt/mssql/secrets/mssql.keytab
ktutil:  quit

• Now add the UPN for the host. Make sure to remove all entries except the ones that represent the machine

ktutil:  rkt /etc/krb5.keytab
ktutil:  delent 1
....
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2                  MSSQL01$@YOUR.DOMAIN.TLD
   2    2                  MSSQL01$@YOUR.DOMAIN.TLD
   3    2                  MSSQL01$@YOUR.DOMAIN.TLD
   4    2                  MSSQL01$@YOUR.DOMAIN.TLD
   5    2                  MSSQL01$@YOUR.DOMAIN.TLD
ktutil:  wkt /var/opt/mssql/secrets/mssql.keytab
ktutil:  quit

• Secure keytab

sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

• Configure MSSQL to use keytab

sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
sudo systemctl restart mssql-server