Database Security

Task 1 - Azure SQL: Advanced Data Security and Auditing
In this task, we will explore vulnerability assessments, data dicscovery and classification, and audting.

Install the AdventureWorks sample database

1. In the Portal, search for and a select SQL databases.
2. On the Basics tab, give you database a name, and create a new server.
3. On the Additional settings tab, select Sample for Use existing data. Also, Enable advanced data security and Start free trial.
4. Review & create, and then Create.
5. Wait for the database to deploy.

Review Vulnerability Assessments

1. Navigate to your SQL database.
2. Under Security select Advanced Data Security.
3. Select Vulnerability Assessment.
4. Review vulnerability assessments and the risk levels.
5. Click Scan.
6. The scan does not need to fully complete for results to show.
7. Review the Findings.
8. Click any Security Check to get more details.
9. Review the Passed checks.
10. Notice Export Scan Results and Scan History.

Review Data Discovery and Classification

1. Return to the Advanced data security blade.
2. Select Data Discovery & Classification.
3. On the Classification tab, select Add classification. Schema name: SalesLT
4. Table name: Customer
5. Column name: Phone
6. Information type: Contact Info
7. Sensitivity label: Confidential
8. When finished click Add classification.
9. Click the blue bar columns with classification recommendations.
10. Notice the data that has been recommended for classification.
11. Select the data of interest and then click Accept selected recommendations.
12. Save your changes.

Review Auditing

1. Return to your SQL database.
2. Under Security select Auditing.
3. Select On for auditing.
4. Click Storage for the destination.
5. Select on the Storage account for logs.
6. Set Retention day to 45 days.
7. Set storage access key to Primary.
8. Save your changes.
9. Discuss Server level auditing and when how it could be used

Task 2 - Azure SQL: Diagnostics
Note: This demonstration requires an Azure SQL database.
In this task, we will review and configure SQL database diagnostics.

1. In the Portal, search for and launch SQL databases.
2. From the Overview blade, review the Compute utilization data graphic. Data is available for different time frames (1 hour, 24 hours, 7 days).
3. Under Monitoring select Diagnostic settings.
4. Click Add diagnostic setting.
5. Give your setting a name.
6. Under Destination details select Send to Log Analytics. Make a note of the Log Analytics workspace that will be used.
7. Under Destination details select Archive to Storage Account.
8. Select the Errors log.
9. Select the Automatic tuning log.
10. Select the Basic metric.
11. Give each item a retention time of 45 days. Retention only applies to storage account.
12. Save your diagnostic setting.
13. In the Portal, search for and launch the Log Analytics workspace.
14. Select the workspace that is being using for your database diagnostics.
15. Under General select Usage and estimated costs.
16. Click Data retention. Use the slider to show how to increse the data retention time. Discuss how additional charges can incur, depending on the pricing plan.
17. Under General select Workspace summary.
18. Click Add and then search the Marketplace for Azure SQL. This feature may be in Preview. Explain the benefits of using this product.
19. Select and then create Azure SQL Analytics.
20. It will take few minutes for the product to deploy.
21. Click Go to resource once the deployment is completed.
22. Click Azure SQL databases.
23. Review the additional metrics that are provided by this product.
24. You can drill into any graphic for additional details.

Task 3 - Azure SQL: AAD Authentication
Note: This task requires an Azure SQL database that has not had AAD configured. This task also requires SQL
Server Management Studio.
In this task, we will configure Azure AD authentication.

1. In the Portal.
2. Navigate to your SQL database.
3. On the Overview page, there is an Active Directory admin box that shows the current status, configured or not configured.
4. Under Settings select Active Directory admin.
5. Click Set admin.
6. Search for and Select the new Active Directory admin. Remember this user you will need in following steps.
7. Be sure to Save your changes.
8. In SQL Server Management Studio connect to the database server using your credentials.
9. Select the SQL database you configured with a new Active Directory admin.
10. Construct a query to create a new user. Insert the admin user and domain. For example, user@contoso.com
11. Create user [user@contoso.com] from external provider;
12. Run the query and ensure it completes successfully.
13. In the Object Explorer navigate your database and Security and Users folder.
14. Verify that the new admin user is shown.
15. Connect to the new database with the new admin credentials.
16. Verify that you can successfully access the database.