Securing PaaS – Securing the web application and database

Synopsis: In this exercise, attendees will utilize Azure SQL features to data mask database data and utilize Azure Key Vault to encrypt sensitive columns for users and applications that query the database.

Task 1: Setup the database

1. Return to the Azure portal window where you are logged in with your user account, not the Key Vault account.
2. Navigate to your resource group by selecting Resource groups, entering your resource group name in the Filter box, and selecting it from the list.
   
3. From the list of resources in your resource group, select the sampledb SQL database which was created by the ARM template you ran in the Before the hands-on lab exercise.
   
4. In the summary section, select the Show database connection strings
5. Take note of the connection string for later in this lab, specifically the Server parameter:
6. Open SQL Server Management Studio.
7. In the Connect to Server dialog:a. Server name: Enter the database server name from above

   b. Authentication: Select SQL Server Authentication

   c. Login: Enter wsadmin

   d. Password: Enter p@ssword1rocks
   

   e. Select Connect

8. If presented with the New Firewall Rule dialog, select Sign In.
9. Sign in as your Azure tenant admin.
10. In the dialog, select OK, notice how your IP address will be added for connection.
11. Right-select Databases, select Import Data-tier Application.
12. In the Introduction dialog, select Next.
13. Select Browse.
14. Navigate to the extracted /Database directory, select the FourthCoffee.dacpac file.
15. Select Open.
16. On the Import Settings dialog, select Next.
17. On the Database Settings dialog, select Next.
    

    NOTE: If you get an error, close and re-open SSMS and try the import again.

    

18. Select Finish, the database will deploy to Azure.
19. Once completed, select Close.
20. Ensure that the master database is selected.
21. In SSMS, select File->Open->File.
22. Browse to the extracted GitHub folder, select the \Database\00_CreateLogin.ps1 file.
23. Press F5 to run the script to create a login called store.
24. Ensure that the FourthCoffee database is selected.
25. Browse to the extracted folder, select the \Database\01_CreateUser.ps1 file.
26. Press F5 to run the script to create a non-admin user called store.

Task 2: Test the web application solution

1. In the extracted directory, double-click the /WebApp/FourthCoffeeAPI/FourthCoffeeAPI.sln solution file to open the solution in Visual Studio 2017 Community edition.a. If prompted in the Visual Studio Version Selector, select Visual Studio 2017 as the program with which to open the solution.

   b. Login to Visual Studio when prompted.

2. In the Solution Explorer, navigate to and double select the web.config file to open it.
3. In the web.config, locate the database connection string (line 72), and update the “data source” property to point to the FourthCoffee database created in Task 2. You should only need to update the server name to point to your Azure SQL Server.
   
4. Save the Web.config file.
5. Run the FourthCoffeeAPI solution, press F5.
6. In the browser window that opens, browse to http://localhost:[PORT-NUMBER]/api/CustomerAccounts, and you should get a JSON response that shows an unmasked credit card column:
   

   NOTE: depending on your browser, you may need to download to view the JSON response.

Task 3: Utilize data masking

1. Switch to the Azure Portal.
2. Select SQL databases.
3. Select the FourthCoffee database.
4. In the menu, select Dynamic Data Masking, then select +Add Mask.
   
5. In the Add masking rule blade, enter the following:a. Schema: Leave dbo selected

   b. Table: Select CustomAccount

   c. Column: Select CreditCardNumber

   d. Masking field format: Select Credit card value (xxxx-xxxx-xxxx-1234)
   

   e. Select Add.

6. Select Save.
7. Switch back to your FourthCoffeeAPI solution, refresh the page, you should see the CreditCardNumber column is now masked with xxxx-xxxx-xxxx-1234.
   

   NOTE: If you do not see this, then you are logged in as a user with dbo privileges
   

8. Close Visual Studio.

Task 4: Utilize column encryption with Azure Key Vault

1. Switch to SQL Management Studio.
2. In the extracted directory, navigate to the Database directory.
3. Open the 02_PermissionSetup.sql file, copy and paste the TSQL to the Query Window.
4. Switch to the FourthCoffee database, execute the SQL statement.
5. In the Object Explorer, expand the FourthCoffee node.
6. Expand the Tables node.
7. Expand the CustomerAccount table node.
8. Expand the Columns node.
9. Right-click the CreditCardNumber column, select Encrypt Column.
10. Select Next on the intro screen.
11. Notice that the State of the column is such that you cannot add encryption (data masking):
12. Select Cancel, then Yes to confirm.
13. Switch back to the Azure Portal, select the CustomerAccount.CreditCardNumber data masking.
14. Select Delete.
15. Select Save.
16. Switch back to SQL Management Studio.
17. Right-click the CreditCardNumber column, select Encrypt Column.
18. Check the checkbox next to the CreditCardNumber column.
19. For the Encryption Type, select Deterministic.
20. Select Next.
21. For the encryption, select Azure Key Vault, in the dialog.
22. Select Sign In.
23. Sign in with your Azure Portal credentials.
24. Select your Azure Key Vault.
    
25. Select Next.
26. On the Run Settings, leave Proceed to finish now selected, and select Next.
27. Select Finish, the configured will start. If prompted, login using your Azure Portal credentials.
    

    NOTE: You may receive a “wrapKey” error, if so, ensure that your account has been assigned that permissions in the Azure Key Vault.

    

    • Select Key vault.
    • Select your key vault.
    • Select Access policies.
    • Select your account.
    • Select Key permissions, select Select all.
    • Select Secret permissions, select Select all.
    • Select Certificate permissions, select Select all.
    • Select OK.
    • Select Save.
    • Retry the operation.
28. Select Close
29. Right-click the CustomerAccount table, select Select top 1000 rows.
30. You will notice the CreditCardNumber column is encrypted based on the new Azure Key Vault key.
31. Switch to the Azure Portal.
32. Select Key Vaults.
33. Select your Azure Key Vault, then select Keys. You should see the key created from the SQL Management Studio displayed:
    

Task 5: Enable Azure SQL Auditing & Threat Detection

1. In the Azure portal, select SQL Databases, and select the FourthCoffee database.
   
2. Select Auditing & Threat Detection.
3. For Auditing, toggle to ON.
4. Select Storage details.
5. Select Storage account, select your storage account.
6. Select OK.
7. For Threat Detection, toggle to ON.
8. Enter your email address.
9. Select Save.

Task 6: Ensure SQL Azure Transparent Data Encryption (TDE) is enabled

1. Select Transparent data encryption.
2. For data encryption, ensure that the toggle is set to ON.

• NOTE: For newly created databases, this is automatically enabled.

3. Select Save.