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.
    Resource groups is selected in the left-hand navigation pane of the Azure portal, and the resouce group name is entered into the Filter box. The resource group is highlighted and selected in the results.
  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.
    From the list of resources in the selected resource group, the paassecurity-db-sampledb resource is selected.
  4. In the summary section, select the Show database connection stringsIn the Summary section, under Connection strings, the Show database connection strings link is selected.
  5. Take note of the connection string for later in this lab, specifically the Server parameter:Under ADO.NET (SQL authentication), the Server parameter is called out.
  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
    The Connect to Server window displays.

    e. Select Connect

  8. If presented with the New Firewall Rule dialog, select Sign In.In the New Firewall Rule dialog box, the Sign In button is selected.
  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.The right-click menu for Databases displays with Import Data-tier Application selected.
  12. In the Introduction dialog, select Next.
  13. Select Browse.Under Specify the BACPAC to import, Import from local disk is selected, as is the Browse button.
  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.

    In the Verify Specified Settings section, both Source and Target are expanded.

  18. Select Finish, the database will deploy to Azure.The Importing database window displays a progress bar and a list of tasks and their status below.
  19. Once completed, select Close.
  20. Ensure that the master database is selected.In SQL Management Studio, master displays in the top toolbar drop-down field.
  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.In SQL Management Studio, FourthCoffee now displays in the top toolbar drop-down field.

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.
    Screenshot of the FourthCoffeeEntities connection string in the Web.config file, with the data source component highlights.
  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:In the JSON response code, a credit card number is called out to show that all of the digits are visible.

    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.
    In the SQL Database blade, under Settings, Dynamic Data Marketing is selected. On the top toolbar, Add mask is selected.
  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)
    Fields in the Add masking rule blade are set to the previously defined settings.

    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
    Screenshot of the JSON results returned by calling the CustomerAccounts API, with the CreditCardNumber highlighted, and the masked value "xxxx-xxxx-xxxx-1111" highlighted.

  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.In SQL Management Studio, the previously mentioned path is expanded, CreditCardNumber is selected, and from its right-click menu, Encrypt Column is selected.
  10. Select Next on the intro screen.
  11. Notice that the State of the column is such that you cannot add encryption (data masking):The CreditCard state column has a circle with a red slash through it, and the message that columns with dynamic data masking are not supported.
  12. Select Cancel, then Yes to confirm.
  13. Switch back to the Azure Portal, select the CustomerAccount.CreditCardNumber data masking.
  14. Select Delete.The Delete button is selected in the Edit Masking Rule blade.
  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.The Checkbox next to CreditCard column is selected, and under Encryption Type, Deterministic is selected.
  20. Select Next.
  21. For the encryption, select Azure Key Vault, in the dialog.In the Generate new encryption key section, under Select the key store provider, Azure Key Vault is selected.
  22. Select Sign In.
  23. Sign in with your Azure Portal credentials.
  24. Select your Azure Key Vault.
    Screenshot of the Encrypt Column screen in SQL Server, with Azure Key Vault selected and highlighted, the user signed in to the appropriate subscription, and the proper Azure Key Vault selected from the drop down list.
  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.

    In the Summary section, under Task, the Generate new column master key task has passed.

    • Select Key vault.
    • Select your key vault.
    • Select Access policies.
    • Select your account.
    • Select Key permissions, select Select all.In the Key permissions section, the Select all check box is selected. All options under Cryptographic Operations are selected and called out.
    • Select Secret permissions, select Select all.
    • Select Certificate permissions, select Select all.
    • Select OK.
    • Select Save.
    • Retry the operation.On the Always Encrypted Results page, in the Summary pane, the Performing encryption operations task has passed.
  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:
    In the Keys blade for the selected Azure Key Vault, the keys created from SQL Server Management Studio are displayed.

Task 5: Enable Azure SQL Auditing & Threat Detection

  1. In the Azure portal, select SQL Databases, and select the FourthCoffee database.
    In the Azure portal left-hand navigation menu, SQL Database is highlighted, and the FourthCoffee SQL Database is highlighted and selected on the right.
  2. Select Auditing & Threat Detection.Under Settings, Auditing and Threat Detection is selected.
  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.In the Threat Detection section, both Auditing and Threat Detection are set to On, and the Save button is selected.
  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.Data encryption is set to on.
  1. Select Save.The Save button is selected in the SQL database blade.

About engsoon

Eng Soon is a 4-time Microsoft MVP and has nearly 5 years of experience building enterprise system in the cloud.He is also a Certified Microsoft Azure.Eng Soon also have strong technical skills and analytic skill. As a developer, Besides the development task, he also involved in Project Management, Consulting, and Marketing. He has a passion for technology and sharing what he learns with others to help enable them to learn faster and be more productive. He also took part as speaker in many nationwide technical events, such as Conference, Meetup and Workshop. Currently, looking for opportunity in Cyber Security which include Cloud Security and Application Security.

View all posts by engsoon →

Leave a Reply

Your email address will not be published. Required fields are marked *