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
- On the home page, click Create a resource at the top of the left panel.
- Search for SQL Server 2017 and hit enter.
- Filter the search by the criteria:
- Operating System: Redhat
- Publisher: Microsoft
- Click on Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL).
- Click Create.
- 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)
- Click Review + create.
- Verify that everything looks good, and click Create.
- Once the deployment is complete, click Go to resource.
- Connect to the SQL Server VM
- On the resource page, click Connect at the top.
- Use the provided information to log in to the server via SSH.
- Change the SA Password
- Stop the
mssql-serverservice:
sudo systemctl stop mssql-server - Change the SA password:
sudo /opt/mssql/bin/mssql-conf set-sa-password - Start the
mssql-serverservice:
sudo systemctl start mssql-server
- Stop the
- Create Instance Logins
Create logins for both the developer and the application so they can connect to SQL Server.
- Connect to SQL Server locally:
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!' - Create the logins:
CREATE LOGIN ACApp WITH PASSWORD = 'AppPassword!' CREATE LOGIN ACDev WITH PASSWORD = 'DevPassword!' GO
- Connect to SQL Server locally:
- Create the Database
- Create a database for the application:
CREATE DATABASE AwesomeCompany; GO
- Create a database for the application:
- Create Database Users and Assign Permissions
- Create the users:
USE AwesomeCompany; GO CREATE USER ACApp FOR LOGIN ACApp CREATE USER ACDev FOR LOGIN ACDev; GO - Assign read and write roles to ACApp:
EXEC sp_addrolemember 'db_datareader', 'ACApp' EXEC sp_addrolemember 'db_datawriter', 'ACApp'; GO - Assign the owner role to ACDev:
EXEC sp_addrolemember 'db_owner', 'ACDev'; GO
- Create the users:
