Total Pageviews

Tuesday, March 20, 2018

SQL Server on Linux - Active Directory authentication with Kerberos

Before using the Windows Authentication mechanism in SQL Server on Linux, the following steps need to be configured:

1.      An Active Directory domain account for the SQL Server on Linux.
2.      SPN needs to be set for the Linux host and for that AD account.
3.      Installation of Kerberos and Samba on Linux.
4.      Joining the Linux Server to the Windows domain.
5.      Verifying that the AD account is able to acquire the Kerberos Ticket.
6.      Attaching the AD account with the SQL Server process on Linux.
7.      Configuring and securing SQL Server.

Configuration steps:

1.      Create an Windows domain account on the AD Server with "User never expires" and "User cannot change password" options.

2.      On the AD Server, create a SPN for this account. The general syntax is the following:
setspn -A MSSQLSvc/[FQDN of Linux Host]:[tcp port]  [AD-Account Name]

3.      On the Linux Server, join the domain with the following command with the AD account, which has sufficient privileges in AD to join a new machine to the domain:

sudo realm join -U '[AD Admin Account]@[UPPERCASE DOMAIN NAME]' -v
sudo realm join --user=[AD Admin Account]  [lowercase domain name]

4.      Verify and take note of kvno:
id [AD Account for Linux]@[lowercase domain name]
kinit [AD Account for Linux]@[UPPERCASE DOMAIN NAME]
kvno MSSQLSvc/[lowercase fqdn of linux server]:1433 

5.      Create a keytab file with ktutil.

sudo ktutil

ktutil> addent -password -p MSSQLSvc/[lowercase fqdn of linux server]:[tcp port]@[UPPERCASE DOMAIN NAME] -k [kvno] -e aes256-cts-hmac-sha1-96

ktutil> addent -password -p MSSQLSvc/[fully qualified domain name of host machine]:[tcp port]@[UPPERCASE DOMAIN NAME] -k [kvno] -e rc4-hmac

ktutil> wkt /var/opt/mssql/secrets/mssql.keytab

ktutil> quit

6.      Restrict access to the keytab file and ensure only the mssql user can read the file.

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

7.      Configure the SQL Server to use this keytab file for the Kerberos authentication:

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

8.      Restart the SQL Server service on Linux server.
sudo systemctl restart mssql-server

Step by step joining to a domain:

We will be adding the linux03 (CentOS 7.4) to use Kerberos authentication to join the domain for the AD user linuxsqlsvc:

For this task, we have the following environment information:

Linux Host/IP: linux03/
FQDN:    (lowercase fqdn of linux server)
Domain: (
Realm:            MYHOME.ORG (uppercase domain name)
AD Admin:     sqladmin (is an Admin account in Windows AD)

1.      Create the new AD account linuxsqlsvc with "User never expires" and "User cannot change password" options:

AD account:     linuxsqlsvc
Password:        Very$ecret

2.      Create SPN for Linux03 and linuxsqlsvc on the admin command prompt of the AD Server:
setspn -A MSSQLSvc/ linuxsqlsvc

3.      Install both the realmd and Kerberos client packages on the SQL Server host:
sudo yum -y install realmd krb5-workstation

4.      Join to the domain; choose a command from the following. Here the sqladmin has sufficient privileges in AD to join a new machine to the domain:

sudo realm join -U 'sqladmin@MYHOME.ORG' -v
sudo realm join --user=sqladmin

5.      Verify and take note of kvno:

kinit linuxsqlsvc@MYHOME.ORG (domain name in upper case)
kvno MSSQLSvc/ 

6.      Create a keytab file with ktutil:

sudo ktutil
> addent -password -p MSSQLSvc/linux03.MYHOME.ORG:1433@MYHOME.ORG -k 2 -e aes256-cts-hmac-sha1-96
> addent -password -p MSSQLSvc/linux03.MYHOME.ORG:1433@MYHOME.ORG -k 2 -e rc4-hmac
> wkt /var/opt/mssql/secrets/mssql.keytab
> quit

7.      Restrict access to the keytab file and ensure only the mssql user can read the file.

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

8.      Configure the SQL Server to use this keytab file for the Kerberos authentication:

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

9.      Restart the SQL Server service on the Linux server:
sudo systemctl restart mssql-server

Configuring Windows Authentication for SQL Server on Linux:

Thursday, March 15, 2018

SQL Server on Linux - Database backup directly to a Windows Network shared folder using samba

We may want to use a Windows Network shared location to redirect SQL Server database backup files, either permanently or temporarily, from the SQL Server Linux instance. Whenever a database happens, the backup files will go directly to the Network shared folder which is \\\linuxshare, where the Network shared username and password are the following:


Step-by-step guide:

1.      If samba and cifs packages have not been installed, then install them:
sudo yum -y install samba-client samba-common cifs-utils

2.      Create a directory on the linux Server:
sudo mkdir /var/opt/mssql/sqlbackup

3.      Assign permission to mssql user:
sudo chown mssql:mssql /var/opt/mssql/sqlbackup

4.      Review the permission:
sudo ls -la /var/opt/mssql/sqlbackup

5.      Retrieve the mssql user id (uid):
id mssql

If we need to use the Windows network backup location permanently, then follow the following steps:

1.      Create a hidden file such as .sharedcrd. The file name can be anything:
sudo vi /etc/samba/.sharedcrd

2.      Add the following to the .sharedcrd:

3.      Assign permission to the root user only and make it secured:
sudo chown roor:root /etc/samba/.sharedcrd
sudo chmod 600 /etc/samba/.sharedcrd

4.      Edit the fstab file:
vi /etc/fstab

5.      Add the following lines to the newly created file:
// /var/opt/mssql/sqlbackup cifs defaults,credentials=/etc/samba/.sharedcrd,uid=991 0 0

6.      Reload the fastab file:
sudo mount -a

7.      Reconfigure the default location of the backup files for SQL Server.
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/sqlbackup

8.      Restart the SQL Server Service:
sudo service mssql-server restart

If we would like to use Windows Network shared temporarily, then execute the following line on the bash shell:
sudo mount.cifs // /var/opt/mssql/sqlbackup -o user=megauser userid=991

Once the database backup is done, it can be dismounted with the umount command:
umount /var/opt/mssql/sqlbackup

Tuesday, March 13, 2018

SQL Server on Linux - Adding dedicated backup disk for SQL Server

The default backup location of SQL Server during installation on Linux is /var/opt/mssql/data. This is also the default location of all data and log files. We should add a new disk and reconfigure the SQL Server default backup location. Additionally, we also need three separate directories for Full, Differential and Transaction Log backups for edge of administration.

The SQL Server configuration file is mssql.conf and is located in the following directory /var/opt/mssql/mssql.conf which needs to be adjusted after adding the disk.

In this task, we will perform the following:
1.      Add a 50GB dedicated backup disk.
2.      Prepare (partition, format, mount) the disk.
3.      Create multiple directories.
4.      Assign read/write permission to the SQL Server process.
5.      Reconfigure SQL Server configuration (mssql.conf file).
6.      Test the database backup using SSMS.

Environment: VMWare Esxi 6.5
Linux Edition: CentOS 7.4
SQL Server: linux05 (
Linux Terminal Emulator: Putty

Step-by-step guide:

Part one: Adding a 50GB new disk to SQL Server Box.

1.      From the Windows desktop, logon to the Linux server as a root user.
2.      Check the current disk status with Linux lsblk command.
3.      Add a 50GB disk to Linux Server and check the status again. The newly added disk name is sdb.

4.      Create partition on the disk sdb as follows:
sudo parted -s -a optimal /dev/sdb mklabel gpt
sudo parted -s -a optimal /dev/sdb mkpart primary 1M 100%

5.      Create the physical volume sdb1:
sudo pvcreate /dev/sdb1

6.      Create a volume group as vg_sqlbackup:
sudo vgcreate vg_sqlbackup /dev/sdb1

7.      Create a logical volume as lv_sqlbackup:
sudo lvcreate -n lv_sqlbackup --extents 100%FREE vg_sqlbackup

8.      Format the drive:
sudo mkfs.xfs /dev/vg_sqlbackup/lv_sqlbackup

9.      Create the mount directory in /var/opt/mssql:
sudo mkdir /var/opt/mssql/sqlbackup

10. Change the user and group ownership:
sudo chown mssql:mssql /var/opt/mssql/sqlbackup

11. Edit the fstab file, create a permanent mount point and add the mount point entry:
vi /etc/fstab
/dev/vg_sqlbackup/lv_sqlbackup  /var/opt/mssql/sqlbackup xfs   defaults 0 0  

12. Reload the fstab:
sudo mount -a

13. Check the disk status:
sudo df -h

14. Edit or reconfigure the SQL Server configuration file:
Method one – directly edit the conf file:
sudo vi /var/opt/mssql/mssql.conf

Add the following parameter:
defaultbackupdir = /var/opt/mssql/sqlbackup

Alternatively, method two:
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/sqlbackup

15. Restart the SQL Server Service:
sudo service mssql-server restart

Part two: Creating directory for Full, Differential and Transaction Log backup:

1.      Check the newly created default backup location:
ll /var/opt/mssql/sqlbackup/

2.      Create three directories: full, diff and tlog respectively:
sudo mkdir /var/opt/mssql/sqlbackup/full
sudo mkdir /var/opt/mssql/sqlbackup/diff
sudo mkdir /var/opt/mssql/sqlbackup/tlog

3.      Assign permission to the mssql user and review the change:
sudo chown mssql:mssql /var/opt/mssql/sqlbackup/full
sudo chown mssql:mssql /var/opt/mssql/sqlbackup/diff
sudo chown mssql:mssql /var/opt/mssql/sqlbackup/tlog

ll /var/opt/mssql/sqlbackup/

Testing the backup in SSMS:

1.      Connect to the SQL Server on Linux using SSMS and execute the following commands:

BACKUP DATABASE TestDB TO DISK='/var/opt/mssql/sqlbackup/full/testdb_full_backup.bak'

BACKUP DATABASE TestDB TO DISK='/var/opt/mssql/sqlbackup/diff/testdb_diff_backup.bak'

BACKUP LOG TestDB TO DISK='/var/opt/mssql/sqlbackup/tlog/testdb_transaction_01.trn'

2.      Review the backup locations in Linux server:

ll -RL /var/opt/mssql/sqlbackup/

Some Screenshots:

Default database backup location:

Single disk on SQL Server:

A new 50 GB disk has been added:

Steps performed on Linux to add the new disk:

Added mount point entry to fstab:

New database backup location:

Three directories for different types of backup:

Verifying existence of database backup files:

Using SQL Monitoring Tool:

SQL Server on Linux - Offline SQL Server installation on Redhat/CentOS

The following step-by-step method can be used to install SQL Server 2017 on the latest Redhat or CentOS. Please take a moment to review the Microsoft documentation for more detailed information. Please also note that the Redhat (paid) and CentOS (non-paid) distributions are identical in terms of execution and package deployment.

Convenience tools:
Download and use the following open source tools while working within the Linux/Unix environment.

Linux Terminal Emulator: Putty/Kitty (
SSH File transfer: FileZila (

We will be installing SQL Server 2017 on the CentOS Server ( as a root user. Where

CentOS 7.4 Server: linux01 (

Step-by-step method to install SQL Server 2017 on Linux:

1. Download all the packages for the Redhat Linux distribution from the following link:

Following are the packages for Redhat/CentOS Linux:


2. On the Linux server, create a folder such as "sqlpkg" in /home directory
mkdir /home/sqlpkg

3. Move all the packages from Windows to the /home/sqlpkg folder in the Linux Server:
use FileZila to transfer the files.

4. Use Putty or Kitty as the Linux terminal emulator from the Windows desktop:
log on to the Linux server - linux01 (

5. Go to the package folder:
cd /home/sqlpkg

6. Install the database engine package:
sudo yum localinstall mssql-server-14.0.3022.28-2.x86_64.rpm

7. Run the setup to complete the installation and respond to the on screen prompt:
sudo /opt/mssql/bin/mssql-conf setup

8. Choose the SQL Server Edition option that needs to be configured.

9. Enter the SQL Server sa password – the password should be complex enough.

10. Restart the SQL Server Service and check the status:
systemctl restart mssql-server.service

11. Check the SQL Server Service status:
systemctl status mssql-server

12. Enable SQL Server Agent:
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

13. Restart and check the SQL Server Service status:
sudo systemctl restart mssql-server
systemctl status mssql-server

14. Firewall port configuration:
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

15. Environment: PATH environment variable needs to be configured:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

16. Install the sql-tools:  execute the following commands in a sequence.
sudo yum localinstall unixODBC-2.3.1-11.el7.x86_64.rpm
sudo yum localinstall msodbcsql-
sudo yum localinstall mssql-tools-

17. Test the SQL Server installation:
sqlcmd -S linux01 -U SA -P '<SAPassword>'
sqlcmd -S localhost -U SA -P '<SAPassword>'

Some Screenshots:

Using FileZila to transfer SQL Server 2017 packages to the Linux Server:

SQL Server packages on the Linux Server (/home/sqlpkg):

SQL Server Database Engine Installation:

Choosing SQL Server Engine Edition:

Connecting SQL Server on Linux using SSMS: