Configuring User Access in SQL Server on Linux

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.

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

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

1. Create a database for the application:

CREATE DATABASE AwesomeCompany;
GO

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