Exercise 2: Securing the Web Application and database

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. Switch to your Azure portal, select All Services then search for SQL Servers. Select SQL Servers:
  2. Select the Azure SQL database server you created using the Azure Manager template (Ex: AzureSecurity-INIT).
  3. Select SQL Databases, then select the SampleDB database.
  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. Enter the database server name from above.
  8. Enter the username and password used from the Azure Template deployment (wsadmin – p@ssword1rocks).Note: If you changed the username and password in the ARM template deployment, use those values instead.
  9. Select Connect, in the New Firewall Rule dialog, select Sign In.
  10. Sign in with your resource group owner credentials.
  11. In the dialog, select OK, notice how your IP address will be added for connection.
  12. Right-click Databases, and select Import Data-tier Application.
  13. In the Introduction dialog, select Next.
  14. Select Browse, navigate to the extracted Database directory, and select the Insurance.bacpac 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 SQL Management Studio try the import again. If that does not work, you may need to download the latest SQL Management Studio from here. In some instances, the latest version may not work, version 17.3 is known to deploy the package properly. You should also be aware that bacpac files exported from some SQL Server instances cannot be deployed to Azure SQL Servers. We have also included a .bak file of the Insurance database that you can use to restore from.

  1. Select Finish and the database will deploy to Azure.
  2. Once completed, select Close.
  3. In SQL Management Studio, select File->Open->File.
  4. Browse to the extracted GitHub folder, select the Hands-on labDatabase0_CreateLogin.ps1 file.
  5. Ensure that the master database is selected.
  6. Run the script to create a login called agent.
  7. Browse to the extracted folder, select the Hands-on labDatabase1_CreateUser.ps1 file.
  8. Ensure that the Insurance database is selected.
  9. Run the script to create a non-admin user called agent.

Task 2: Test the web application solution

  1. In the extracted directory, double-click the /WebApp/InsuranceAPI/InsuranceAPI.sln solution file, and Visual Studio will open.
  2. In the Solution Explorer, navigate to and double-click the web.config file to open it.
  3. Update the web.config (line 72) to point to the Insurance database created in Task 2. You should only need to update the server name to point to your Azure SQL Server.
  4. Run the InsuranceAPI solution and press F5.
  5. In the browser window that opens, browse to http://localhost:portno/api/Users/E91019DA-26C8-B201-1385-0011F6C365E9 you should see a json response that shows an unmasked SSN column.

Note: Depending on your browser, you may need to download to view the json response.

The json response is displayed in a browser window.

Task 3: Utilize data masking

  1. Switch to the Azure Portal.
  2. Select SQL databases.
  3. Select the Insurance database.
  4. In the menu, select Dynamic Data Masking, then select +Add Mask.
  5. Select the User table.
  6. Select the SSN column.
  7. Select Add.
  8. Select Save.
  9. Switch back to your InsuranceAPI solution, press F5 to refresh the page. You should see the SSN column is now masked with xxxx.
  10. 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 Insurance database, and execute the SQL statement.
  5. In the Object Explorer, expand the Insurance node.
  6. Expand the Tables node.
  7. Expand the User table node.
  8. Expand the Columns node.
  9. Right-click the SSN column, and select Encrypt Column.Notice that the State of the column is such that you cannot add encryption (data masking):
  10. Select Cancel.
  11. Switch back to the Azure Portal, and select the User.SSN data masking.
  12. Select Delete.
  13. Select Save.
  14. Switch back to SQL Management Studio.
  15. Right-click the SSN column, and select Encrypt Column.
  16. Check the checkbox next to the SSN column.
  17. For the Encryption Type, and select Deterministic.
  18. Select Next.
  19. For the encryption select Azure Key Vault in the dialog.
  20. Select SignIn.
  21. Sign in with your Azure Portal credentials.
  22. Select your Azure Key Vault.
  23. Select Next.
  24. On the Run Settings, select Next.
  25. Select Finish, and the configured will start.Note: You may receive a “wrapKey” error. If so, ensure that your account has been assigned those permissions in the Azure Key Vault.a. Select Key vault.b. Select your key vault.c. Select Access policies.d. Select Add New.e. For the principal, select your account.f. Select Key permissions, and select Select all.g. Select Secret permissions, and select Select all.h. Select Certificate permissions, and select Select all.i. Select OK.j. Select Save.k. Retry the operation.Note: If you are still getting errors (such as Access Denied), ensure that you have selected the correct subscription and Key Vault.
  1. Select Close.
  2. Right-click the User table, and select Select top 1000 rows.You will notice the SSN column is encrypted based on the new Azure Key Vault key.
  3. Switch to the Azure Portal.
  4. Select Key Vaults.
  5. Select your Azure Key Vault, and then select Keys. You should see the key created from the SQL Management Studio displayed: