Configuring SQL Server on Linux in Azure

In order to achieve the best possible security and performance, it is always recommended to configure installed software to be in compliance with best practices and our organization’s policies. This is especially true with databases since they contain valuable information.

In this hands-on lab, we harness the power of the Azure Marketplace to quickly provision a SQL Server on a Linux VM. We then configure that instance and bring it into compliance with company policy.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log In to the Azure Portal

Create a SQL Server on Linux VM from the Azure Marketplace

On the Home page, click Create a resource.
Search for "SQL Server 2017" and hit enter.
Filter the search by the criteria Operating system -> Redhat and Publisher -> Microsoft.
Click on Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL).
Click Create.
Select the Resource Group created by the lab.
Provide a Virtual machine name.
Click Change size under Size.
Select B2s and click Select.
Choose Password for the Authentication type, then provide a Username and Password.
Click Allow selected ports.
Select SSH (22).
Click Disks.
Click Create and Attach a New Disk.
Change the name to mssql_data.
Click Change Size.
Change the disk size to a custom value of "10" and click OK.
Click OK.
Repeat these steps twice more to create mssql_log and mssql_backups disks.
Click Review + create.
Verify that everything looks good and click Create.
Once the deployment is complete, click Go to resource.

To allow incoming connections, follow these steps.

On your resource page, click Networking in the left pane.
Click Add inbound port rule.
Leave all of the defaults, and change Destination port ranges to 50000.
Change Protocol to TCP.
Change Name to Port_50000.
Click Add.

Connect to the SQL Server VM

On the resource page, click Connect at the top.
Use the provided information to SSH to the server.

Change the SA Password

Stop the mssql-server service.
sudo systemctl stop mssql-server
Change the SA password.
sudo /opt/mssql/bin/mssql-conf set-sa-password
Start the mssql-server service.
sudo systemctl start mssql-server

Configure the VM Firewall

Use the following commands to open the firewall port on the VM. Choose a port that won’t conflict with others on the system.

sudo firewall-cmd --zone=public --add-port=50000/tcp --permanent
sudo firewall-cmd --reload

Create a New Admin Account and Disable SA

Connect to the instance with SQLCMD.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!'

Create an admin account called ACAdmin.

CREATE LOGIN ACAdmin WITH PASSWORD = 'AwesomePassword!'
exec SP_ADDSRVROLEMEMBER 'ACAdmin','SYSADMIN';
GO

Log out of SA by typing quit and hitting enter.

Connect to SQL Server again, this time as ACAdmin.

/opt/mssql-tools/bin/sqlcmd -S localhost -U ACAdmin -P 'AwesomePassword!'

Disable the SA login
```
ALTER LOGIN SA DISABLE;
GO
```

Change the SQL Server Port

Use mssql-conf to set a new TCP port.
sudo /opt/mssql/bin/mssql-conf set network.tcpport 50000

Configure the Data, Log, and Backup Disks

Find the disks via dmesg (probably sdc, sdd, and sde).
dmesg | grep SCSI
Partition the disk with fdisk.
sudo fdisk /dev/sdc
At the Command line, enter n and press enter.
For Partition type, choose p and press enter.
Press enter to accept the default. Do this twice more until the system prompts for another command.
Print the partition table with p, then write it with w.
Write a file system to the disk.
sudo mkfs -t ext4 /dev/sdc1
Create a directory for the mount point.
```
sudo mkdir /var/opt/mssql/data/UserData
```

Change the directory ownership to mssql.

sudo chown mssql:mssql /var/opt/mssql/data/UserData

Mount the disk.
sudo mount /dev/sdc1 /var/opt/mssql/data/UserData
Add the drive to /etc/fstab.
Get the UUID with blkid.
```
sudo -i blkid
```
Edit /etc/fstab.
```
sudo vi /etc/fstab
```

Add an entry similar to the one below, using your UUID.

UUID=1c8e3964-b444-4243-ac76-88cb6ea0cf2b   /var/opt/mssql/data/UserData   ext4   defaults,nofail   0   0

Save and exit.
Repeat these steps for the log and backup drives.

Change the Default Paths

Change the default data path.

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data/UserData

Change the default log path.

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /var/opt/mssql/data/UserLog

Change the default backup path.

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/data/Backups

Restart the mssql-server service.
```
sudo systemctl restart mssql-server
```

Verify Configuration

To see the current mssql-conf configuration, run the following command.
```
sudo cat /var/opt/mssql/mssql.conf
```
Connect from an external client (Azure Data Studio, VS Code or SQLCMD) using the new port number and user.

Install SQLCMD.

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel

Connect with SQLCMD.

/opt/mssql-tools/bin/sqlcmd -S <IPAddress>,50000 -U ACAdmin -P 'AwesomePassword!'

Verify the SA account is disabled.

SELECT Name, is_disabled
FROM sys.server_principals;
GO

Create a new database, then list the contents of our new directories to verify the .mdf and .ldf files are placed there.
CREATE DATABASE AwesomeCompany;
GO
```
sudo ls /var/opt/mssql/data/UserData
sudo ls /var/opt/mssql/data/UserLog
```
Back up the database and verify the backup file is placed in the new location.
BACKUP DATABASE AwesomeCompany TO DISK = ‘/var/opt/mssql/data/Backups/AwesomeCompany.bkp’;
GO
```
sudo ls /var/opt/mssql/data/Backups
```

Configuring SQL Server on Linux in Azure

About this Hands-on Lab

Learning Objectives

Additional Resources

What are Hands-on Labs

Newsletter