Azure Db – connectedcircuits

This article is about connecting an Azure WebApp to a SQL Server VM which is hosted inside an Azure Virtual Network. Typically a SQL Server VM would be hosted inside an Azure Virtual Network (VNet) to isolate it from the internet by blocking all inbound and outbound internet traffic using a Network Security Group (NSG). In this scenario, connectivity to the SQL Database is achieved by using the new VNet Integration feature found on the App Service component. Using this feature removes the requirement of an App Service Environment (ASE) for the WebApp thus reducing overall hosting costs.

Using VNet integration provides private outbound access from your App Service to resources in your VNet using the RFC1918 internal IP address allocation range (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16) by default.

Scenario

A web application is hosted in a WebApp which requires a connection to the SQL Database hosted inside a VNet.

The network topology of this scenario is shown below which uses the Regional VNet Integration option where both the WebApp and SQL VM are in the same region. Here we have a VNet called Backend which has two subnets, one for the VNet Integration used for delegating called IntegDeleg and the other to host the SQL Server VM called DataStore.

Configuration Walkthrough

The following are the sequence of steps used to setup VNet Integration between a Web App and SQL Server with the assumption the SQL Server VM is already hosted inside a VNet.

1. Adding a VNet subnet

2. Provisioning an AppSvc Plan

3. Provisioning a WebApp

4. Setting up the VNet Integration

5. Validating SQL Server Security Settings

6. Testing connectivity to the SQL Server

7. Updating the Web App SQL Connection String

1. Adding a VNet Subnet

A dedicated subnet used by the VNet Integration feature is required to be added to the existing VNet hosting the SQL Server VM. The IP range should match the maximum number of AppSvc plan instances when fully scaled out as each instance would require a IP address. For this scenario I will be using a /27 prefix giving a total range of 32 address, however 5 address are used internally by Azure leaving 27 usable addresses for each plan instance.

2. Provisioning App Svc Plan

To use VNet Integration, you will need to provision an App Service plan using newer V2 scale units. Note if you are currently using V1 App Services, you will need to provision a new plan using V2 and migrate you apps to this new plan.

To confirm if you have selected the newer V2 App Services, the Premium plans should be shown as P1V2, P2V2 and P3V2. Here I will be using a Standard Plan S1 for this scenario highlighted below.

3. Provisioning Web App

Create a new Web App and ensure it is in the same region as the VNet. Also ensure you have selected the App Service Plan you created above.

4. Enable VNet Integration

Under the Web App that was provisioned above, click on the Networking menu item to view the networking options and then click on “Click here to configure” under the VNet Integration heading.

Once the VNet Configuration form opens, click on the “Add VNet” to open the Network Feature Status blade. Select the VNet that hosts the SQL Server and then the Subnet that was created earlier for the VNet Integration. Then press OK to save the changes.

After you hit OK, the VNet Integration should be connected and ready for testing the connectivity. Remember the VNet Integration will route all outbound RFC1918 traffic from the WebApp into your VNet.

5. Validating SQL Server Security Settings

To reduce the surface area of attack, ensure the SQL Server can only accept connections within the VNet. This is done by setting the “SQL connectivity” option to Private (within Virtual Network) under the Security menu of the SQL Virtual Machine.

Also check the NSG attached to the SQL Server VM to ensure there is a rule to disable all outbound internet traffic. If there is a inbound rule called default-allow-sql as highlighted below, it is advisable to delete this rule if not required. This inbound rule default-allow-sql is normally created when the security on the SQL Server VM allows SQL connectivity via Public (Internet) connections.

6. Testing connectivity

To check connectivity between the Web App and the SQL server, we can use the tcpping command from a console window. Go to the Web App that was created previously and click on the Console menu item to open a console window similar to below.

In the console window type the command tcpping <sql vm private ip address>:1433. All going well you should get a reply similar to that below where 10.0.2.4 was the private IP address of my SQL Server VM using the default port 1433.

7. Updating the Web App SQL Connection String

Once the connectivity has been verified, the next step is to update the connection string on the Web App to use the private IP address of the SQL Server VM. Typically the connection string should look something like this:- Server=10.0.2.4;Database=coreDb;User Id=myusername;Password=mypassword;MultipleActiveResultSets=true

After the connection string has been updated to use the private IP address, you should be able to test your application. Here I am just adding some new tasks in the TodoList web application and confirming the records are written to the database.

Conclusion

VNet Integration provides an easy and cost effective solution to access databases hosted within a VNet without resorting to a dedicated ASE. Also using rules on the NSG applied to the SQL Server VM provides the capability to block all internet traffic and allow only RFC1918 internal addresses to have access.

More information about VNet Integration can be found on the MS docs site https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet.

Enjoy…

When trying to execute a long running SQL query using the Logic App SQL Connector, you will most likely hit the 2 minute execution timeout limit on the connector.

Even if you move the SQL query to an Azure function and use ADO.Net to access the backend database, you will hit the 10 minute execution timeout limit of the function when using the consumption plan.

An Alternative Approach

The solution here is to use the Azure Automation service to execute a PowerShell script which calls a SQL query. There is now a new connector available in Logic Apps to execute an Automation Runbook which you have the option to wait until it completes before moving onto the next action shape in the workflow.

Implementation

First find and add an Automation Account to your Azure resource group. Set the Create Azure Run As account to No.

Once the Automation Account has been created, click on the Credentials on the left menu pane to store the SQL Server login credentials.

Then add the SQL username and password and click Create. We will be using this as the Credential parameter in our PowerShell script.

Now click on Runbooks to create a new runbook. This is where we add a PowerShell script to execute.

Add a name for the Runbook and ensure the Runbook type is set to PowerShell Workflow, then click the Create button at the bottom of the page.

After the Runbook has been created, click on the Edit icon to add the PowerShell script.

Inside the PowerShell editor, paste the following code. Basically we are using ADO.Net object to create a SQL connection and to execute the stored procedure. To ensure the command object does not timeout, set the CommandTimeout = 0.

workflow ExecuteSQLStoredProcedure
{
    param(
        [parameter(Mandatory=$True)]
        [string] $SqlServer,
        
        [parameter(Mandatory=$False)]
        [int] $SqlServerPort=1433,
        
        [parameter(Mandatory=$True)]
        [string] $Database,
        
        [parameter(Mandatory=$True)]
        [string] $Sproc,
        
        [parameter(Mandatory=$True)]
        [PSCredential] $SqlCredential
    )
 
    # Get the username and password from the SQL Credential
    $SqlUsername = $SqlCredential.UserName
    $SqlPass = $SqlCredential.GetNetworkCredential().Password
    
    inlinescript {
        # Define the connection to the SQL Database
        $SQLConn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
        
        # Open the SQL connection
        $SQLConn.Open()
 
        # Define the SQL command to run. 
        $Cmd =  New-Object System.Data.SQLClient.SqlCommand 
        $Cmd.Connection = $SQLConn 
        $Cmd.CommandTimeout=0
        $Cmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $Cmd.CommandText = $using:Sproc
 
        $Cmd.ExecuteNonQuery()
 
        # Close the SQL connection
        $SQLConn.Close()
    }
}

After the code has been pasted into the editor, save and remember to publish it after it has been saved.

Now to add a Logic App to execute the Runbook. For this demo, I will simply use the scheduler connector to fire the Logic App. In the designer add a Recurrence trigger and for the next action, search for automation and select the Create Job Azure Automation.

The parameters defined in the PowerShell script will be shown in the properties page of the connector as shown below. I will be executing a stored procedure called “dbo.LongProcess” which has a WaitFor Delay ‘00:15’ T-SQL statement. This will simulate a long running query of 15 minutes.

To get the results of the SQL query add the Azure Automation Get job output shape after the Create job shape.

Testing

To confirm the Logic App and SQL query does not timeout, run the trigger manually. After a period of 15 minutes, which is the delay period defined in the stored procedure, the Logic App will complete as shown below.

The output of the stored procedure will be shown in the Get job output shape.

To return a recordset as JSON from the PowerShell script, replace the code $Cmd.ExecuteNonQuery() with the following:

$reader = $Cmd.ExecuteReader()

$table = new-object "System.Data.DataTable"

$table.Load($reader)

#--Exporting data to the screen --#

$table | select $table.Columns.ColumnName | ConvertTo-Json

Note if you return a very large recordset, you may exceed the allocated memory,. In this scenario, you will need to batch the results.

Enjoy…

Category: Azure Db

Connecting an Azure WebApp to a SQL Server VM inside a VNet

Scenario

Configuration Walkthrough

1. Adding a VNet Subnet

2. Provisioning App Svc Plan

3. Provisioning Web App

4. Enable VNet Integration

5. Validating SQL Server Security Settings

6. Testing connectivity

7. Updating the Web App SQL Connection String

Conclusion

Like this:

Using Azure Logic App and an Automation Runbook to execute a long running SQL Stored Procedure

An Alternative Approach

Implementation

Testing

Like this:

Scenario

Configuration Walkthrough

1. Adding a VNet Subnet

2. Provisioning App Svc Plan

3. Provisioning Web App

4. Enable VNet Integration

5. Validating SQL Server Security Settings

6. Testing connectivity

7. Updating the Web App SQL Connection String

Conclusion

Share this:

Like this:

An Alternative Approach

Implementation

Testing

Share this:

Like this: