Securing Azure SQL Database with firewall rules and virtual network integration

Question

Pulumi · Accepted Answer

Securing an Azure SQL Database involves several steps, which include setting up firewall rules to control the traffic that can access the database, and possibly integrating the database with an Azure Virtual Network (VNet) for enhanced isolation. Here's a step-by-step guide on how to achieve that using Pulumi and TypeScript.

### Step 1: Import necessary packages
Import the `azure-native` package, which provides the Azure resources we will use.

### Step 2: Set up the Azure SQL Server and Database
Create an Azure SQL server resource, and then define an Azure SQL database to reside within the server.

### Step 3: Configure Firewall Rules
Set up firewall rules that define which IP addresses can access the Azure SQL Database.

### Step 4: Virtual Network Integration (Optional)
If you need to integrate the SQL Database with an Azure VNet, configure virtual network rules to restrict access to the database to resources inside the VNet.

### Step 5: Export Output
Export any important endpoints or IDs that you may need to use outside of Pulumi, such as the SQL server's fully qualified domain name (FQDN).

Let's put this into a Pulumi program:

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as sql from "@pulumi/azure-native/sql";
import * as resources from "@pulumi/azure-native/resources";
import * as network from "@pulumi/azure-native/network";

const config = new pulumi.Config();
const resourceGroupName = config.require("resourceGroup");

// Create an Azure Resource Group if not already existing
const resourceGroup = new resources.ResourceGroup("resourceGroup", {
    resourceGroupName,
});

// Create an Azure SQL Server
const sqlServer = new sql.Server("sqlServer", {
    resourceGroupName: resourceGroup.name,
    location: resourceGroup.location,
    serverName: "myuniquesqlservername",
    administratorLogin: "sqladminuser",
    administratorLoginPassword: "ComplexP@ssw0rd",
    version: "12.0", // Specify the server version
    publicNetworkAccess: "Enabled",
});

// Create an Azure SQL Database within the SQL Server
const sqlDatabase = new sql.Database("sqlDatabase", {
    resourceGroupName: resourceGroup.name,
    serverName: sqlServer.name,
    databaseName: "mydatabase",
    collation: "SQL_Latin1_General_CP1_CI_AS",
    sku: {
        name: "S0",
        tier: "Standard",
    },
});

// Configure a firewall rule to allow traffic from a specific IP range
const firewallRule = new sql.FirewallRule("firewallRule", {
    resourceGroupName: resourceGroup.name,
    serverName: sqlServer.name,
    firewallRuleName: "AllowSpecificIPRange",
    startIpAddress: "0.0.0.0",  // Replace with actual starting IP address
    endIpAddress: "0.0.0.0",  // Replace with actual ending IP address
});

// Optionally, set up a virtual network rule (Virtual Network Integration)
const vnet = new network.VirtualNetwork("vnet", {
    resourceGroupName: resourceGroup.name,
    location: resourceGroup.location,
    addressSpace: {
        addressPrefixes: ["10.0.0.0/16"],
    },
});
const subnet = new network.Subnet("subnet", {
    resourceGroupName: resourceGroup.name,
    virtualNetworkName: vnet.name,
    addressPrefix: "10.0.1.0/24",
    serviceEndpoints: [{ service: "Microsoft.Sql" }],
});
const virtualNetworkRule = new sql.VirtualNetworkRule("virtualNetworkRule", {
    resourceGroupName: resourceGroup.name,
    serverName: sqlServer.name,
    virtualNetworkRuleName: "AllowVNet",
    virtualNetworkSubnetId: subnet.id,
    ignoreMissingVnetServiceEndpoint: true,
});

// Export the SQL server's FQDN for easy access
export const sqlServerFqdn = sqlServer.fullyQualifiedDomainName;
```

**Explanation:** 
- We first import the necessary modules from the `@pulumi/azure-native` package.
- Then create a new resource group if one doesn't exist.
- We proceed to create a SQL server and a SQL database within that server. Here, you should replace placeholders like server names, locations, admin login credentials, and the database name with your own.
- Next, we create a firewall rule that specifies which IP address ranges can access the SQL server. Make sure to replace `"0.0.0.0"` with the actual IP ranges you want to allow.
- Lastly, we optionally integrate the SQL server with a virtual network by creating a VNet and subnet. Subnet is set up to include a service endpoint for Microsoft.Sql, required for Azure SQL integration. A virtual network rule is also established to associate the subnet with the SQL Server.

You should replace placeholder values with actual data you intend to use, such as IP addresses, names, and credentials. Be mindful of keeping sensitive data like passwords secure. In production, you should store these securely in a secret manager or use environment variables and the Pulumi Config system.

Make sure you have the Azure Pulumi provider configured with appropriate credentials before running the program. After deploying this Pulumi program, you will have an Azure SQL Database that’s secured through specified firewall rules and optionally integrated with a Virtual Network.