MSSQL RHEL 7.7

From Michael's Information Zone
Jump to navigation Jump to search

Purpose

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

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[3][4] 

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.[5][6] 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
  1. ↑ https://access.redhat.com/support/policy/updates/errata
  2. ↑ https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017
  3. ↑ https://www.sqlshack.com/configure-sql-server-2017-linux-mssql-conf-available-tools/
  4. ↑ https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-2017
  5. ↑ https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-join-domain?view=sql-server-2017
  6. ↑ https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017
Retrieved from "https://wiki.1701technology.com/index.php?title=MSSQL_RHEL_7.7&oldid=1172"