Michael.mast: Created page with "==Purpose== To install MSSQL 2017 on RHEL 7.7 (ends August 30, 2021; Final RHEL 7 EUS Release)https://access.redhat.com/support/policy/updates/errata hosted on AWS,..."

2019-08-20T18:46:40Z

Created page with "==Purpose== To install MSSQL 2017 on RHEL 7.7 (ends August 30, 2021; Final RHEL 7 EUS Release)<ref>https://access.redhat.com/support/policy/updates/errata</ref> hosted on AWS,..."

New page

==Purpose==
To install MSSQL 2017 on RHEL 7.7 (ends August 30, 2021; Final RHEL 7 EUS Release)<ref>https://access.redhat.com/support/policy/updates/errata</ref> hosted on AWS, with AD authentication.
<ref>https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017</ref>

==Config==
*m5a.xlarge
*100GB GP2 boot (SSD)
*500GB gp2 data (SSD)
*1024GB ti backup (Magentic)
==Notes==
<pre>
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
</pre>
I am unable to find yum-cron in the default repositories provided by Red Hat for RHEL 7.7 on AWS.
<pre>
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
</pre>
Configure storage
<pre>
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
</pre>
Update fstab to mount accordingly.<br>
Now update storage location<ref>https://www.sqlshack.com/configure-sql-server-2017-linux-mssql-conf-available-tools/</ref><ref>https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-2017</ref>
<pre>
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
</pre>
At this point I can use the database services with the SA account. No issues yet.<br>
Now setup AD Authentication.<ref>https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-join-domain?view=sql-server-2017</ref><ref>https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017</ref> In this case I want to authenticate against two domains.<br>
First enroll the linux host into AD
<pre>
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
</pre>
Then create a dedicated user account in AD, set the SPN for the account, and generate the keytab.
*On domain controller
<pre>
C:\Windows\system32>setspn.exe -A MSSQLSvc/mssql01.your.domain.tld:1433 dedicated_user
</pre>
*On the Linux host check for the kvno number of the account.
<pre>
[ec2-user@mssql01 ~]$ kvno MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMAIN.TLD
MSSQLSvc/mssql01.your.domain.tld:1433@YOUR.DOMNAIN.TLD: kvno = 2
</pre>
*Create keytab. I followed instructions to use the MSA instead of host SPN. Was only able to authenticate using MSA.
===Keytab with MSA===
<pre>
[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
</pre>
Tell MSSQL to use the domain account for authentication purposes.
<pre>
[ec2-user@mssql01 ~]$ sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount mssqllinux
</pre>
===Keytab with SPN===
<s>
<pre>
[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
</pre>
*Now add the UPN for the host. Make sure to remove all entries except the ones that represent the machine
<pre>
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
</pre>
</s>

*Secure keytab
<pre>
sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab
</pre>
*Configure MSSQL to use keytab
<pre>
sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
sudo systemctl restart mssql-server
</pre>

MSSQL RHEL 7.7 - Revision history

Michael.mast: Created page with "==Purpose== To install MSSQL 2017 on RHEL 7.7 (ends August 30, 2021; Final RHEL 7 EUS Release)https://access.redhat.com/support/policy/updates/errata hosted on AWS,..."