Configuring User Access in SQL Server on Linux

30 minutes
  • 6 Learning Objectives

About this Hands-on Lab

With any RDBMS, it is important to appropriately configure both authentication and authorization. Users are provisioned the least privileges possible in order to accomplish their task. In SQL Server, this is accomplished via logins, roles, and users. In this hands-on lab, you are the SQL DBA for Awesome Company. You are working with a developer to set up access for a new web app. This will be accomplished by using the Azure Marketplace to quickly spin up a database back end, then configuring a database appropriately for the developer’s needs.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

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.
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.
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
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!'
  2. Create the logins:
Create the Database
  1. Create a database for the application:
    CREATE DATABASE AwesomeCompany;
Create Database Users and Assign Permissions
  1. Create the users:
    USE AwesomeCompany;
  2. Assign read and write roles to ACApp:
    EXEC sp_addrolemember 'db_datareader', 'ACApp'
    EXEC sp_addrolemember 'db_datawriter', 'ACApp';
  3. Assign the owner role to ACDev:
    EXEC sp_addrolemember 'db_owner', 'ACDev';

Additional Resources

In this hands-on lab, you are the SQL DBA for Awesome Company. You are working with a developer to set up access for a new web app. Utilizing the Azure Marketplace, you will quickly create a new SQL Server on a Linux development environment with a database for the application. You will then provision the following access for the database:

  • A service account that will let the application read and write to the database
  • An account for the developer that will let them build out the database structures
  • Neither account should have rights outside of their database

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

To get started, log in to the Azure Portal using the credentials provided.

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?