Configuring SQL Server on Linux in Azure

45 minutes
  • 10 Learning Objectives

About this Hands-on Lab

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

Log in to the Azure Portal using the provided credentials.

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!'
  • 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
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
    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;
  • 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;

    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’;

    sudo ls /var/opt/mssql/data/Backups

Additional Resources

In this hands-on lab scenario, we are the SQL Server DBA for Awesome Company. Safeguarding their clients' data is a top priority for the company, so a set of policies has been created that every new SQL instance must adhere to. They are a Linux shop, and now that SQL Server is available on Linux they are moving all new instances to it.

Note: For the lab, use the "Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL)."

We need to provision a new SQL instance and make sure it meets the following policy requirements.

  • The port must be changed from the default 1433.
  • We must create a new admin account called ACAdmin, and disable the SA one.
  • Change the default data, log, and backup directories to be on separate disks from the operating system.

Step-by-step instructions are included in the task list. Feel free to follow along there or jump in on your own!

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?