Configure database authentication and authorization

Estimated Time: 20 minutes

The students will take the information gained in the lessons to configure and subsequently implement security in the Azure Portal and within the AdventureWorks database.

You’ve been hired as a Senior Database Administrator to help ensure the security of the database environment.

Note: These exercises ask you to copy and paste T-SQL code and makes use of existing SQL resources. Please verify that the code has been copied correctly, before executing the code.

Authorize access to Azure SQL Database with Azure Active Directory

From the lab virtual machine, start a browser session and navigate to https://portal.azure.com. Connect to the Portal using the Azure Username and Password provided on the Resources tab for this lab virtual machine.
On the Azure portal home page select All resources.
Select the Azure SQL Database server dp300-lab-xxxxxx, where xxxxxx is a random string, and select Not Configured next to Active Directory Admin.
On the next screen, select Set admin.
In the Azure Active Directory sidebar, search for the Azure username you logged into the Azure portal with, then click on Select.
Select Save to complete the process. This will make your username the Azure Active Directory admin for the server as shown below.
On the left select Overview, then copy the Server name.
Open SQL Server Management Studio and select Connect > Database Engine. In the Server name paste the name of your server. Change the authentication type to Azure Active Directory Universal with MFA.

For the User name field, select the Azure Username from the Resources tab.
Select Connect.

[!NOTE] When you first try to sign in to an Azure SQL database your client IP address needs to be added to the firewall. SQL Server Management Studio can do this for you. Use the Azure Portal password from the Resources tab, then select Sign in, choose your Azure credentials and then select OK.

Manage access to database objects

In this task you will manage access to the database and its objects. The first thing you will do is create two users in the AdventureWorksLT database.

Use the Object Explorer and expand Databases.
Right-click on AdventureWorksLT, and select New Query.
In the new query window, copy and paste the below T-SQL into it. Execute the query to create the two users.
```
 CREATE USER [DP300User1] WITH PASSWORD = 'Azur3Pa$$';
 GO

 CREATE USER [DP300User2] WITH PASSWORD = 'Azur3Pa$$';
 GO
```
Note: These users are created in the scope of the AdventureWorksLT database. Next you will create a custom role and add the users to it.

Execute the following T-SQL in the same query window.

 CREATE ROLE [SalesReader];
 GO

 ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
 GO

 ALTER ROLE [SalesReader] ADD MEMBER [DP300User2];
 GO

Next create a new stored procedure in the SalesLT schema.

Execute the below T-SQL in your query window.

 CREATE OR ALTER PROCEDURE SalesLT.DemoProc
 AS
 SELECT P.Name, Sum(SOD.LineTotal) as TotalSales ,SOH.OrderDate
 FROM SalesLT.Product P
 INNER JOIN SalesLT.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
 INNER JOIN SalesLT.SalesOrderHeader SOH on SOH.SalesOrderID = SOD.SalesOrderID
 GROUP BY P.Name, SOH.OrderDate
 ORDER BY TotalSales DESC
 GO

Next use the EXECUTE AS USER syntax to test out the security. This allows the database engine to execute a query in the context of your user.

Execute the following T-SQL.

 EXECUTE AS USER = 'DP300User1'
 EXECUTE SalesLT.DemoProc

This will fail with the message:

Screenshot of the error message, The EXECUTE permission was denied on the object DemoProc

Next grant permissions to the role to allow it to execute the store procedure. Execute the below T-SQL.
```
 REVERT;
 GRANT EXECUTE ON SCHEMA::SalesLT TO [SalesReader];
 GO
```
The first command reverts the execution context back to the database owner.

Rerun the previous T-SQL.

 EXECUTE AS USER = 'DP300User1'
 EXECUTE SalesLT.DemoProc

Screenshot showing the returned rows of data from the stored procedure

In this exercise, you’ve seen how you can use Azure Active Directory to grant Azure credentials access to a SQL Server hosted in Azure. You’ve also used T-SQL statement to create new database users and granted them permissions to run stored procedures.