Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.
  • Labs icon Lab
  • A Cloud Guru
Azure icon

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.

Azure icon

Path Info

Clock icon Intermediate
Clock icon 45m
Clock icon Nov 15, 2019

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Table of Contents

  1. Challenge

    Log In to the Azure Portal

    Log in to the Azure Portal using the provided credentials.

  2. Challenge

    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.
  3. Challenge

    Connect to the SQL Server VM

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

    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
  5. Challenge

    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
  6. Challenge

    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`.
     ``` SQL
     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`.
    ``` bash
     /opt/mssql-tools/bin/sqlcmd -S localhost -U ACAdmin -P 'AwesomePassword!'
    • Disable the SA login
  7. Challenge

    Change the SQL Server Port

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

    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.
  9. Challenge

    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
  10. Challenge

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

The Cloud Content team comprises subject matter experts hyper focused on services offered by the leading cloud vendors (AWS, GCP, and Azure), as well as cloud-related technologies such as Linux and DevOps. The team is thrilled to share their knowledge to help you build modern tech solutions from the ground up, secure and optimize your environments, and so much more!

What's a lab?

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.

Provided environment for hands-on practice

We will provide the credentials and environment necessary for you to practice right within your browser.

Guided walkthrough

Follow along with the author’s guided walkthrough and build something new in your provided environment!

Did you know?

On average, you retain 75% more of your learning if you get time for practice.

Start learning by doing today

View Plans