In this lesson, you will learn how to implement various security features for an Azure SQL Database. The exercise consists of four tasks: deploying an Azure SQL Database, configuring advanced data protection, configuring data classification, and configuring auditing. These tasks will help you enhance the security of your SQL Database and protect sensitive data.
- An Azure subscription with Owner or Contributor role access.
- Basic understanding of Azure portal navigation.
Lesson Steps:
- Sign in to the Azure portal using your Azure account.
- Open the Azure portal at
https://portal.azure.com/
. -In the search bar at the top of the portal, type "Deploy a custom template" and press Enter. - On the Custom deployment blade, select "Build your own template in the editor."
![build a template](https://private-user-images.githubusercontent.com/23303634/242440829-58399f1c-d8dc-4c6a-a4f6-31de7def8839.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NDA4MjktNTgzOTlmMWMtZDhkYy00YzZhLWE0ZjYtMzFkZTdkZWY4ODM5LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTNhNjcwMjI2NzM5MDZhM2UwNjI4YmEyYjg2ZTg3OTYyYjZlZTBlNDVkYTQwMmY0ZmYyNTA4N2FmNzY3MzY5OTMmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.Se1uXxn3kBb4G7SGteoToAg4Vj35bil_6BIi3LLyPgU)
- On the Edit template blade, click "Load file" and locate the file JSON file. Click Open.
![load json template](https://private-user-images.githubusercontent.com/23303634/242441121-c30a506d-449d-4dff-90c6-e7842fb9a837.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NDExMjEtYzMwYTUwNmQtNDQ5ZC00ZGZmLTkwYzYtZTc4NDJmYjlhODM3LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWFiMTA5ODdhNzE2ZTA5MzA1MjIxYzI5ZjMyMjQ5MWVhZTdlMDZmNTRlYWZjNmZlNTFkMTgyODM5NDk3NjNmZWQmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.7zrSLNC3kK7pcdhIWavv48IMEFQ089RJ0s7WaQ3WO1w)
- Review the template content that describes the deployment of an Azure SQL Database.
- Click Save on the Edit template blade.
- On the Custom deployment blade, configure the following settings:
- Subscription: Select the Azure subscription you will use for this lab.
- Resource group: Click "Create new" and type the name "AZ500LAB11".
![create resource group for custom deployement](https://private-user-images.githubusercontent.com/23303634/242440556-301c45aa-af6d-462e-b306-e06436a9a0e5.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NDA1NTYtMzAxYzQ1YWEtYWY2ZC00NjJlLWIzMDYtZTA2NDM2YTlhMGU1LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTVjYjkxMmZkODdmNTM2YzRjZTA1NjI5NWUxMjA4MDljMjUzZTVlOTE1ODRkOWQ1YmFhYzBmMmVhZjVhNzczZDkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.2o_-rjHNK_UBx8mdRkjY0pCVM4u7bw2fiNAYpf70xz8)
-
Location: Select "(US) South Central US" or your preferred location that is nearest to you. This has to match the location that is in the template.
-
Click "Review + Create" and then click Create.
-
Wait for the deployment to complete.
In the Azure portal, search for "Resource groups" and select it from the results.
-
On the Resource groups blade, locate and click on the "AZ500LAB11" entry.
-
On the AZ500LAB11 blade, click the entry representing the newly created SQL Server.
-
On the SQL server blade, go to the Security section and click "Microsoft Defender for Cloud".
-
Select "Enable Microsoft Defender for SQL" and wait for the notification indicating that Azure Defender for SQL has been successfully enabled.
-
On the SQL server blade, in the Security section, click on the "Microsoft Defender for Cloud" page.
-
In the "Microsoft Defender for SQL: Enabled at the subscription-level (Configure)" parameter, click "(configure)". Refresh the browser if it's not displaying.
-
On the Server Settings blade, review the information about pricing, trial period, vulnerability assessment settings, and advanced threat protection settings.
-
Go back to the Microsoft Defender for Cloud blade and review recommendations and security alerts.
Note: Recommendations may take some time to appear. You can proceed to the next task while waiting.
On the SQL server blade, in the Settings section, click "SQL Databases".
-
Select the "AZ500LabDb" entry from the list of databases.
-
On the AZ500LabDb SQL database blade, in the Security section, click "Data Discovery & Classification".
-
On the Data Discovery & Classification blade, click the "Classification" tab.
Note: The classification engine scans your database for columns containing potentially sensitive data and provides recommended column classifications.
-
Click the text message "We have found 15 columns with classification recommendations" displayed on the blue bar at the top of the blade.
![Data and Discovery Classification](https://private-user-images.githubusercontent.com/23303634/242453027-de0bf138-0f31-43e8-a22e-47635d757cea.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NTMwMjctZGUwYmYxMzgtMGYzMS00M2U4LWEyMmUtNDc2MzVkNzU3Y2VhLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTZhYjJjNGEyYWJiNTI4NDUyYjgwODQyMjBhOTE5YWQ5OTI2MWRkYzdiYzI1ZmVhNmUwYzllYzRkZjEyN2QwYmImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.leTrgaA9M1HRCmodr73UC7ZUSUtYAuu7HpR6KQ8sXNg)
- Review the listed columns and the recommended sensitivity labels.
- Enable the "Select all" checkbox and then click "Accept Selected Recommendations".
![Classification Select All](https://private-user-images.githubusercontent.com/23303634/242474622-4ffe51af-6f56-49b7-9a51-cefd168d2460.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NzQ2MjItNGZmZTUxYWYtNmY1Ni00OWI3LTlhNTEtY2VmZDE2OGQyNDYwLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWJiNDZiNGUzZjJlNmRkOTc4YzJjNzZjMTg5NzFjMzAzN2E4MjgwNTg2YjAzMDU1MDA0OTQxODZkNzMxZTQ0NTMmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.lHzzhNCEe9FKBOKdAy58xrWRO0ZL9PdvyyMcM_KGbfc)
Note: Alternatively, you can select specific columns and dismiss others.
Note: You can also change the information type and sensitivity label. Once you have completed your review, click "Save".
![Save Selected Data Classifications](https://private-user-images.githubusercontent.com/23303634/242475216-a78a99de-8a0b-41a9-a47d-8ef9f66de381.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NzUyMTYtYTc4YTk5ZGUtOGEwYi00MWE5LWE0N2QtOGVmOWY2NmRlMzgxLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTIyNmJlZDk3NzEyMjQyNDExNzhlYzliMGYyNmQ1NDUzYmNmMDBjNjlmMmM5NDkzMmQzNmI3MjJlNjZjNGE1NzcmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.afjLFNPFRBxtmUvAIGcy5tQIoIM3pRQaE39oVikybgI)
Note: This will complete the classification and persistently label the database columns with the new classification metadata.
- Back on the Data Discovery & Classification blade, go to the "Overview" tab and note the updated classification information.
In the Azure portal, navigate back to the SQL Server blade.
-
In the Security section, click "Auditing".
Note: This is server-level auditing, and the default settings include auditing queries, stored procedures, successful and failed logins.
-
Set the "Enable Azure SQL Auditing" switch to "ON" to enable auditing.
-
Select the Storage checkbox, and the entry boxes for Subscription and Storage Account will appear.
-
Choose your Subscription from the dropdown list.
-
Click "Storage account" and choose "Create new".
-
On the Create storage account blade, enter a globally unique name consisting of between 3 and 24 lowercase letters and digits. Click OK.
![Create Storage Account](https://private-user-images.githubusercontent.com/23303634/242476579-6fec4962-b6f2-44d7-b4ec-05040b06d919.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NzY1NzktNmZlYzQ5NjItYjZmMi00NGQ3LWI0ZWMtMDUwNDBiMDZkOTE5LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWQ0NDkzZWJhNGRkYjM3MTcyZDJjOWUyYjU3YzVlMWJlODQyM2RkNWQ2N2UwM2QzODkwZGJhYzFmY2ZhNDlkNTAmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.3o3ww5RkkvAd1cZJ4RY2JI4i1xruXT6ZNubiek24FEw)
Note: Refresh the browser if the storage account doesn't immediately become available.
- Back on the Auditing blade, under Advanced properties, set "Retention (days)" to 5.
- Click "Save" to save the auditing settings.
![Enable Azure SQL Auditing](https://private-user-images.githubusercontent.com/23303634/242479334-cdee1525-cdd5-483d-ab40-5d6008167f71.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0NzkzMzQtY2RlZTE1MjUtY2RkNS00ODNkLWFiNDAtNWQ2MDA4MTY3ZjcxLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTI5MWNlMmJhZTU2NjQ3ZmNkYzAwY2M2YmZmZGQwZDYwZDNiYjk4MGZlNzYzNDU4OWQ2MDkwYTcyZGEyZWQ0OTImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.9LFeOT3SDqsXrywR8Tt0Mq-2--tBz-AYgbwyx04zCZs)
Note: If you receive an error message regarding an invalid storage container path, wait a few minutes and click "Storage account". On the Choose storage account blade, select the newly created storage account, and then click "Save".
-
On the SQL Server blade, go to the Settings section and click "SQL Databases".
-
Select the "AZ500LabDb" entry from the list of databases.
-
On the AZ500LabDb SQL database blade, in the Security section, click "Auditing".
Note: This is database-level auditing, and server-level auditing is already enabled.
Note: Audits can be written to an Azure storage account, Log Analytics workspace, or Event Hub.
-
Click "View Audit Logs".
![View Audit Logs for SQL Database](https://private-user-images.githubusercontent.com/23303634/242481369-73dfd98c-be65-4623-ba61-1c6874232757.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0ODEzNjktNzNkZmQ5OGMtYmU2NS00NjIzLWJhNjEtMWM2ODc0MjMyNzU3LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTZhOTZkY2VjMzhkMjYzZTJlMjcwNTE1NzUyOThkZGY1YWMxZDZjNjY1NWNiMzExOWU4YjhmOWYyZTlkMjNkMTMmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.bfabrxfKrLbKIKriQW6PnwcNr0wrJLwKZ4OtHN4FASg)
- On the Audit records blade, you can switch between Server audit and Database audit.
![Audit Records](https://private-user-images.githubusercontent.com/23303634/242482540-41545846-17e7-4d9b-ad10-15165c376eeb.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjQ1OTQyMzEsIm5iZiI6MTcyNDU5MzkzMSwicGF0aCI6Ii8yMzMwMzYzNC8yNDI0ODI1NDAtNDE1NDU4NDYtMTdlNy00ZDliLWFkMTAtMTUxNjVjMzc2ZWViLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA4MjUlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwODI1VDEzNTIxMVomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWJkMjQ2MjIxMTdmYWJiMWIyZDZjYzk2Mzg4ZjI0YTFlNTU0ZDBiNGY4ZDE3NWRhZDdhMDQ5ZGQyNjY1ZjI2ZTgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.z385uDuNh9sNgHmb7v3rdb4lfNtcBrCR4hdwYDVcJdo)
Note: Since the SQL server and database were created recently, there may not be any events available at this point.
Congratulations! You have successfully completed the exercise on implementing SQL Database security features. In this lesson, you learned how to deploy an Azure SQL Database, configure advanced data protection, configure data classification, and configure auditing. These security measures will help you protect your SQL Database and sensitive data. Remember to clean up your resources to avoid unexpected costs.
Clean Up Resources:
Open the Azure Cloud Shell by clicking the first icon in the top right of the Azure portal. If prompted, select PowerShell and Create storage. In the PowerShell session within the Cloud Shell pane, run the following command to remove the resource group created in this lab:
Remove-AzResourceGroup -Name "AZ500LAB11" -Force -AsJob
Close the Cloud Shell pane. Now you can confidently implement SQL Database security features and ensure the protection of your valuable data.