Workshop on MS SQL Server On Linux Day 2
Environment:
Domain Name: [Link]
Domain server FQDN:[Link]
Domain Ip: [Link]
Today Agenda:
Installing sql server on cent os
Configuring windows authentication.
Connecting from ssms and also using windows authentication
Step By step:
1. Let's check if mssql-server repository already exists?
ls /etc/[Link].d | grep [Link]
OR
yum repolist
2. If it does, we need to check contents of this repository and remove
that, otherwise we will face issues during the install
cat /etc/[Link].d/[Link]
rm -rf /etc/[Link].d/[Link]
[Link] and Download SQL Server Repository
sudo curl -o /etc/[Link].d/[Link]
[Link]
[Link] SQL Server using below commands
yum install -y mssql-server
5. Configure SQL Server and Setup SA password
sudo /opt/mssql/bin/mssql-conf setup
6. Restart SQL Server Services
systemctl restart mssql-server
7. Configure repository and install Sqlcmd and other Tools to internally
connect to newly installed SQL Server
sudo curl -o /etc/[Link].d/[Link]
[Link]
sudo yum install -y mssql-tools unixODBC-devel
[Link] Environment Tools to your Path Environment
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>
~/.bash_profile;echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>
~/.bashrc;source ~/.bashrc
9. Connect to SQL Server using sqlcmd tool
sqlcmd -S localhost -U SA -P Zoom@@123
10. Enable Firewall port 1433
sudo firewall-cmd --zone=public --add-port=1433/tcp --
permanent;firewall-cmd --reload;sudo firewall-cmd --zone=public --
add-port=5022/tcp --permanent;firewall-cmd --reload
11. Enable and start SQL Server Agent services
sudo /opt/mssql/bin/mssql-conf set [Link] true
[Link] the ms sql service
systemctl restart [Link]
=====================================
[Link] SQL Server Service Account in Your Active Directory Domain
UserName= mssql
PasswordExpire= never
[Link] ServicePrincipalName (SPN) for SQL Server Service account
on Your Domain Conroller
setspn -A MSSQLSvc/[Link] mssql
setspn -A MSSQLSvc/[Link] mssql
[Link] KVNO (Key Version Number) number for AD Account-
usually its 2
kinit mssql@[Link]
kvno MSSQLSvc/[Link]
kvno MSSQLSvc/[Link]
16. Creating SQL Keytab File (AD Account with password must set this
up) using ktutil
ktutil
for node21
ktutil: addent -password -p
MSSQLSvc/[Link]@[Link] -k 2 -e aes256-cts-hmac-
sha1-96
ktutil: addent -password -p
MSSQLSvc/[Link]@[Link] -k 2 -e rc4-hmac
ktutil: wkt /var/opt/mssql/secrets/[Link]
quit
for node22
ktutil
ktutil: addent -password -p
MSSQLSvc/[Link]@[Link] -k 2 -e aes256-cts-hmac-
sha1-96
ktutil: addent -password -p
MSSQLSvc/[Link]@[Link] -k 2 -e rc4-hmac
ktutil: wkt /var/opt/mssql/secrets/[Link]
quit
17. Setting up Proper permission (ownership)
chown mssql:mssql /var/opt/mssql/secrets/[Link];chmod 400
/var/opt/mssql/secrets/[Link]
18. Configuring SQL Server to Point to KeyTab File
sudo /opt/mssql/bin/mssql-conf set [Link]
/var/opt/mssql/secrets/[Link]
19. Restarting SQL Server Services
systemctl restart [Link]
[Link] to connect with ssms
