Table of Contents

1. Challenge

   Create a SQL Server on Linux VM from the Azure Marketplace

   1. On the home page, click Create a resource at the top of the left panel.
   2. Search for SQL Server 2017 and hit enter.
   3. Filter the search by the criteria:
      • Operating System: Redhat
      • Publisher: Microsoft
   4. Click on Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL).
   5. Click Create.
   6. On the Create a virtual machine page, set the following values:
      • Resource group: Select the one listed
      • Virtual machine name: Anything you'd like
      • Size: B2S
      • Authentication type: Password
      • Username: Anything you'd like
      • Password: Anything you'd like
      • Public inbound ports: Allow selected ports
      • Select inbound ports: SSH (22)
   7. Click Review + create.
   8. Verify that everything looks good, and click Create.
   9. Once the deployment is complete, click Go to resource.
2. Challenge

   Connect to the SQL Server VM

   1. On the resource page, click Connect at the top.
   2. Use the provided information to log in to the server via SSH.
3. Challenge

   Change the SA Password

   1. Stop the mssql-server service: sudo systemctl stop mssql-server
   2. Change the SA password: sudo /opt/mssql/bin/mssql-conf set-sa-password
   3. Start the mssql-server service: sudo systemctl start mssql-server
4. Challenge

   Create Instance Logins

   Create logins for both the developer and the application so they can connect to SQL Server.

   1. Connect to SQL Server locally:

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

   1. Create the logins:

   CREATE LOGIN ACApp WITH PASSWORD = 'AppPassword!'
   CREATE LOGIN ACDev WITH PASSWORD = 'DevPassword!'
   GO
   

5. Challenge

   Create the Database

   1. Create a database for the application:

   CREATE DATABASE AwesomeCompany;
   GO
   

6. Challenge

   Create Database Users and Assign Permissions

   1. Create the users:

   USE AwesomeCompany;
   GO
   CREATE USER ACApp FOR LOGIN ACApp
   CREATE USER ACDev FOR LOGIN ACDev;
   GO
   

   1. Assign read and write roles to ACApp:

   EXEC sp_addrolemember 'db_datareader', 'ACApp'
   EXEC sp_addrolemember 'db_datawriter', 'ACApp';
   GO
   

   1. Assign the owner role to ACDev:

   EXEC sp_addrolemember 'db_owner', 'ACDev';
   GO