We know most software applications, desktop, mobile, or web, require a database at the backend to store data. When we look at current software applications, the complexity is very high, and they have a high frequency of data transactions. So, we need testing of the data stored and retrieved from the database to make sure it has proper data integrity. Any database operation performed by the application is always one of the four, Create, Retrieve, Update and Delete.

We were required to do database unit tests in one of our recent client application implementations, as the application is mainly data-centric. The decisive reason to push for database unit testing is to ensure the application has data integrity. We were required to develop database test cases to include checks for data integrity and confirming business rules.

For database unit testing, we have the following frameworks that can be used:

  • Microsoft SQL Server Data Tools (Using Visual Studio)
  • tSQLt Unit Testing Framework

This blog post will give a high-level experience in implementing and demonstrating how to create test cases that use tSQLt features on AdventureWorks sample databases. We choose the tSQLt Database Unit Testing framework for Azure SQL Database to implement database unit testing. tSQLt allows us to create isolated test cases defined with the data we need; each test case runs in its own transaction.

A tSQLt unit test case is based on the AAA rule, which consists of:
Rules for Database Testing

Step One: tSQLt Environment Setup

The first step is to install tSQLt in your database.

  • Setup includes a set of objects (tables, stored procedures, functions, and more) that you add to the database you want to test. Download “tSQLt_V1.0.5873.27393.zip” from the tSQLt site, and unzip the file.
  • Run the “tSQLt.class.sql” script against the database. You can find the script in the zip file that you downloaded from the tSQLt site.
    Once the run is successful, you can find the tSQLt schema. Assigned to that schema will be tables, views, stored procedures, and user-defined functions. These are the components that do all the processing when creating and running test cases against your database. As a part of best practices, we will not be moving any objects created by “tSQLt.class.sql” and unit test cases outside the development database.

Object Explorer

Before you create a test case, you need to create a test class where the test case will be located. A test class is a schema configured with an extended property that tells tSQLt that it is a test class. To create the test class, you use the NewTestClass stored procedure part of the tSQLt schema.

NewTestClass Stored Procedure

The EXEC statement creates a test class named TestPerson. Once we’ve completed this step, we can add one or more test cases to the test class, so let’s get started doing that.

Step 2: Testing [Person].[GetAddressByCity] Stored Procedure

In tSQLt, a test case is a stored procedure that’s part of a test class and uses tSQLt elements to perform the testing. We can develop and test stored procedures and functions in a database.

As part of developing the tSQLt test case, we’ll create a test case for a stored procedure [Person].[GetAddressByCity] which will return address details for a given city.

Creating a Test Procedure by City

Use the CREATE PROCEDURE statement to create a test case. The procedure name must start with the word “test” and be created in an existing test class; otherwise, making the test case is much like creating any other procedure. The following T-SQL script creates a test case named “TestGetAddressByCitySuccess” in the “TestPerson” test class:

Follow tSQL Script to Create Test Case

As you can see, we are using CREATE OR ALTER PROCEDURE statement to create a test case. The critical part of the test case is the main body of the procedure definition, between BEGIN and END. First, we use tSQLt’s FakeTable stored procedure, which creates a unique temporary table with the same name as the table referenced within the actual stored procedure we are testing. This will ensure that the data in an actual database and the table is not updated or deleted. Any subsequent references we make in our test case to that table will always point to the test table and not the actual table in the database. Then we populate the table with test data.

AIS achieved Windows Server and SQL Server-based workloads to Azure, reflecting our commitment and investments in delivery around helping organizations migrate workloads to Azure.

In the test case, we use two temporary tables, #Expected, used to store the expected data, and #Actual, which will store data once the stored procedure is successfully run. We are keeping both temp tables schema similar.

Finally, we use the tSQLt AssertEqualsTable stored procedure to compare the data in the #Actual table to the data in the #Expected table.

Run the test case using tSQLt.Run the stored procedure.

When we run the test case, it should evaluate to true and return the following results:

Final result for unit testing

Now you have the basics, which will allow you to go a long way with unit testing using tSQLt for SQL Server. You can refer to documentation by visiting the tSQLt User Guide.

This blog will explain how a short-staffed team overcame security issues in a critical legacy application by combining modern authentication with legacy MVC frameworks. This allowed the team to upgrade the application to meet enterprise security standards without disrupting the users.


I managed an in-house application, ERMS, developed a decade ago with MVC and forms authentication. The forms authentication used custom user management with SQL Server database. The skilled developer created a productive application that served the needs of the HR, managers, and employees. ERMS was developed for AIS in-house use and had lower priority than the customer engagements. Over the years, it’s been stable, requiring few changes. When changes were required, we had to juggle resources to accomplish them. Figure 1 shows the application architecture before the change.

Upgrading Legacy MVC Forms Authentication to Azure AD


The application was developed before the standard security practices. Over time, it became non-compliant. ERMS needed to be upgraded the legacy forms authentication to Azure AD authentication. This required sweeping changes to the way the users logged in to the application, which would be a significant undertaking. The solution was not challenging, but it must be done with minimal downtime and minimal resources. In addition to this, ERMS uses custom roles that do not map to the Active Directory roles.


We considered several ways to solve this problem, as outlined below.

Upgrade Authentication and Authorization

The first option was to remove forms authentication and custom role management to use Active Directory, as shown in Figure 2. Equivalent AD roles would have to be created. The code at various layers needed to be updated to refer to the corresponding AD roles. This was not a viable option as it is risky with many changes.

Removing forms authentication

Looking to migrate your enterprise to the cloud? AIS can help you scale, reduce technical debt, and mitigate risk.

Upgrade Authentication and Use Legacy Authorization

Figure 3 shows another approach we explored to retain the existing role management and use Azure AD for authentication. This was a sensible fallback in the given context. That did not work as the User Principal in the HTTP request context was always not set, which would cause the authentication to break.

Retain the existing role management

We learned two points from the failed trials. First, we should only attempt to upgrade the authentication but not touch the custom role management. Second, it was a resource-heavy effort to integrate Active Directory with custom role management in ERMS.

Using a Connector for Authentication

The solution that worked was to have a lightweight authenticator app that the ERMS application consumes to validate the users, as shown in the high-level flow in Figure 5. This authenticator service app would validate against Azure AD, and role management would stay the same. Figure 4 shows the solution overview.

Complete Solution OverviewHigh Level Flow

The Right Approach

An independent authentication connector service and maintaining the existing role management are the key to the solution. This is a dependable approach if you are looking for a quick turnaround using minimal coding or resources.

What is Azure Web Application Firewall (WAF)?

Azure Web Application Firewall (WAF) filters, monitors, and blocks HTTP traffic. It uses Open Web Application Security Project® (OWASP) rules to protect your application. It also provides centralized protection to web applications from common exploits and vulnerabilities and protects against threats and intrusions.

Supported Services

We have three different options to create a WAF in Azure:

  • Azure Front Door: Global, scalable entry-point that uses the Microsoft global edge network to create fast, secure, and widely scalable web applications.
  • Azure Content Delivery Network (CDN): Global CDN solution for delivering high-bandwidth content. It can be hosted in Azure or any other location.
  • Azure Application Gateway: Web traffic load balancer that enables you to manage traffic to your web applications.

Azure Front Door

Azure Front Door provides centralized protection for our web applications. It prevents malicious attacks close to the attack sources before they enter your virtual network.

As shown in the below image, we placed WAF at Azure network edge locations. It will inspect every incoming traffic, so it will prevent malicious attacks from entering the virtual network.

Global WAF Policy
Reference – https://docs.microsoft.com/en-us/azure/web-application-firewall/afds/afds-overview

Azure Content Delivery Network (CDN) Service from Microsoft

Azure Content Delivery Network (CDN) provides a global and centralized solution for our web content. It will reduce load times, bandwidth, and speed responsiveness of the application.

As shown in the image, WAF deployed on Azure network edge locations around the globe. A WAF policy easily links to any CDN endpoint in your subscription. New rules can be deployed within minutes and respond quickly to changing threat patterns.

Content Delivery Network
Reference – https://docs.microsoft.com/en-us/azure/web-application-firewall/cdn/cdn-overview

Azure Application Gateway WAF

Application security is strengthened by WAF integration into Application Gateway. Protect your web applications from web vulnerabilities and attacks without modification to back-end code. We can protect multiple web applications at the same time. An instance of Application Gateway can host up to 40 websites protected by a web application firewall. In addition, we can custom WAF policies for different sites behind the same WAF. As shown below, we can also Protect our web applications from malicious bots and XSS attacks, SQL Injection, and other vulnerabilities by using Application Gateway WAF.

Application Gateway
Reference – https://docs.microsoft.com/en-us/azure/web-application-firewall/ag/ag-overview

WAF Modes

WAF policy can be configured to run in the following two modes:

  • Detection mode: When running in detection mode, WAF doesn’t take any actions other than monitoring and logs the request and its matched WAF rule to WAF logs.
  • Prevention mode: In prevention mode, WAF takes the specified action if a request matches a rule. If a match is found, no further rules with lower priority are evaluated. Any matched requests are also logged in the WAF logs.

WAF Policy and Rules

WAF policy consists of two types of security rules:

  • Custom rules that are authored by the customer
  • Managed rule sets that are a collection of Azure-managed pre-configured set of rules

Custom rules are reviewed before processing the rules in a managed rule set. A rule is made of a match condition, a priority, and action. If such a match is processed, rules with lower priorities aren’t processed. We can create rules that meet our requirements by combining managed and custom rules. For example, we can configure custom rules based on IP address, Geographical location, HTTP parameters, size constraint, rate limiting.

WAF Actions

WAF customers can choose to run from one of the actions when a request matches a rule’s conditions:

  • Allow: Request passes through the WAF and is forwarded to the back-end. No further lower priority rules can block this request.
  • Block: The request is blocked, and WAF responds to the client without forwarding the request to the back end.
  • Log: Request is logged in the WAF logs, and WAF continues evaluating lower priority rules.
  • Redirect: WAF redirects the request to the specified URI. The URI specified it is a policy-level setting. Once configured, all requests that match the Redirect action will be sent to that URI.

WAF Monitoring

Monitoring the health of your WAF and the applications that it protects is supported by integration with Azure Security Center, Azure Sentinel, and Azure Monitor logs. WAF instances are integrated and send alerts and health information to Security Center for reporting. Azure Monitor allows us to track diagnostic information, including WAF alerts and logs.

Monitor and track diagnostics
Reference – https://docs.microsoft.com/en-us/azure/web-application-firewall/ag/ag-overview

How To Deploy Azure Web Application Firewall (WAF) with Azure Application Gateway

  1. Create WAF Policy to configure the firewall. Search Web Application Firewall Policy click and Add policy. Then we need to select the type of WAF, Resource Group, Policy Name, and state.
    Basics of creating WAF Policy
    Reference – https://docs.microsoft.com/en-us/azure/web-application-firewall/cdn/waf-cdn-create-portal
  2. Select Prevent or Detect mode based on the requirement.
  3. We can configure a custom rules section to match the rule and rate limit rules. As shown in the image below, we can limit the threshold value and duration.
  4. Review your settings, then create!
Adding rate limit rule to match
Reference – https://docs.microsoft.com/en-us/azure/web-application-firewall/cdn/waf-cdn-create-portal


All organizations are exposed to a variety of malicious attacks. To protect from such, we can use Azure WAF to protect the application even from the most sophisticated threats before they reach your servers. To learn more, check out Microsoft documentation on Azure WAF or reach out to AIS.

Your cloud adoption efforts require sound security, compliance, and governance. It is our mission to make those requirements a reality. Contact AIS about our Security and Compliance Consulting Services.

As SQL developers, we develop a well-defined understanding of relational data structure over the years that makes it feel natural to manipulate relational data using the SQL query language. Switching to MultiDimensional eXpressions (MDX) queries can be painful if some basic differences between SQL and MDX are not clearly understood. In this article, I will explain those differences from a SQL developer point of view and illustrate via some examples.

Read More…