Skip to main content

Configuring SQL Server on Linux in Azure

Hands-On Lab

 

Photo of Landon Fowler

Landon Fowler

Database Training Architect II

Length

00:45:00

Difficulty

Intermediate

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Configuring SQL Server on Linux in Azure

Introduction

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.

Connecting to the Lab

  1. Log in to the Azure Portal using the credentials provided on the lab instructions page.

Create a SQL Server on a Linux VM from the Azure Marketplace

  1. Click + Create a resource.

  2. Enter "SQL Server 2017" in the search box and run a search.

  3. Click Operating System and click Red Hat to select only that option.

  4. Click Publisher and select Microsoft.

  5. Click the option for Free SQL Server License: SQL Server 2017 Developer.

  6. Click Create.

  7. Under Resource Group, click the combo box and select the provided resource group.

  8. Enter a name "configlab" for Virtual machine name.

  9. Click Change size and select B2s.

  1. Click Select.

  2. For Authentication type, select the Password option.

  3. Provide a Username and Password in the associated boxes. For the rest of this lab, these will be referred to as VM_USERNAME and VM_PASSWORD.

  4. For Public inbound ports, select Allow selected portS.

  5. Click the combo box for Select inbound ports and select SSH.

  6. Scroll to the top of the window and select the Disks tab.

  7. Click Create and attach a new disk.

  8. Enter "mssql_data" in the Name box.

  9. Click Change size.

  10. Enter a "10" in the Custom disk size (Gib) box.

  11. Click OK, then click OK again to create the disk.

  12. Go through steps 16 - 20 twice more, using Name values of "mssql_log" and "mssql_backup".

  13. Click Review + create.

  14. Click Create.

Connect to the SQL Server VM

  1. Once the deployment is complete, click Go to resource.

  2. Under Settings, click Networking.

  3. Click Add inbound security rule.

  4. Change Destination port ranges to "50000".

  5. Under Protocol, select TCP.

  6. Change the Name value to "Port_50000".

  7. Click Add.

  8. Click Overview in the configlab navigation pane.

  9. Click Connect.

  1. Copy the Login using VM local account (preview) box to the clipboard.

  2. Open a terminal and use the copied string to connect to the server.

Configure the SQL Server VM

  1. Stop the SQL Server service.

    sudo systemctl stop mssql-server
  2. Change the password to "AwesomePassword!".

    sudo /opt/mssql-conf set-sa-password
  3. Restart the SQL service.

    sudo systemctl start mssql-server
  4. Configure the local firewall.

    sudo firewall-cmd --zone=public --add-port=50000/tcp --permanent
  5. Reload the firewall.

    sudo firewall-cmd --reload
  6. Connect to the SQL Server instance.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!'
  7. Create a login and add it to the sysadmin role.

    CREATE LOGIN ACAdmin WITH PASSWORD = 'AwesomePassword!'
    exec SP_ADDSRVROLEMEMBER 'ACAdmin','SYSADMIN';
    go
  8. Exit the SQL Server.

    quit
  9. Reconnect to the SQL Server instance with the ACadmin login.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U ACAdmin -P 'AwesomePassword!'
  1. Disable the default SA account.

    ALTER LOGIN SA DISABLE;
    go
  2. Exit the SQL Server.

    quit
  3. Change the SQL port.

    sudo /opt/mssql/bin/mssql-conf set network.tcpport 50000

Configure the Data, Log, and Backup Disks

  1. Find the disks.

    dmesg | grep SCSI
  2. Partition the disk.

    sudo fdisk /dev/sdc
  3. At the prompt, type "n" and then enter.

  4. At the Select prompt, type "p" and then enter.

  5. Press enter for the next three options to use the default values.

  6. At the prompt, type "p" and then enter.

  7. At the prompt, type "w" and then enter.

  8. Write a file system to the partition.

    sudo mkfs -t ext4 /dev/sdc1
  9. Create a directory for the disk to mount to.

    sudo mkdir /var/opt/mssql/data/UserData
  1. Change the ownership of the folder.

    sudo chown mssql:mssql /var/opt/mssql/data/UserData
  2. Mount the disk.

    sudo mount /dev/sdc1 /var/opt/mssql/data/UserData
  3. Get the UUID for sdc1 and copy it to the clipboard.

    sudo -i blkid
  4. Edit the configuration file.

    sudo vi /etc/fstab
  5. At the bottom of the file, add the following lines.

    UUID=COPIED_UUID    /var/opt/mssql/data/UserData    ext4    defaults,nofail 0   0
  6. Save the changes and exit the editor.

  7. Repeat steps 1 - 16 twice more, for the log and backups disks.

Change the Default Paths

  1. Set the default paths.

    sudo /opt/mssql/bin/mssql-conf set filelocation/defaultdatadir /var/opt/mssql/data/UserData
    sudo /opt/mssql/bin/mssql-conf set filelocation/defaultlogdir /var/opt/mssql/data/UserLog
    sudo /opt/mssql/bin/mssql-conf set filelocation/defaultbackupdir /var/opt/mssql/data/Backups
  2. Restart the service.

    sudo systemctl retart mssql-server

Verify the Configuration

  1. See a list of configurations and verify the changes.

    sudo cat /var/opt/mssql/mssql.conf
  2. Open Azure Data Studio and provide the Server IP including port 50000, the User name ACAdmin, and the Password AwesomePassword!.

  3. Click Connect.

  4. Right-click the server and select New Query from the context menu.

  5. Run a query to verify the SA account is disabled by entering the following query.

    SELECT Name, is_disabled
    from sys.server_principals
  6. Hit F5 to run the query and verify sa is listed as disabled.

  7. Create a database by entering the following query.

    CREATE DATABASE AwesomeCompany
  8. Hit F5 to run.

  9. Go back to the terminal server and verify the existence of the files.

    sudo ls /var/opt/mssql/data/UserData
    sudo ls /var/opt/mssql/data/UserLog
  1. Back in Azure Data Studio, run a backup.

    BACKUP DATABASE AwesomeCompany
    TO DISK = '/var/opt/mssql/data/Backups/AwesomeCompany.bkp'
  2. Hit F5 to run.

  3. Once the backup is completed, go back to the terminal sever and verify the backup.

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

Conclusion

Congratulations, you've completed this hands-on lab!