SAST and DAST in SQL Server DevOps: Tools and Best Practices

DevOps made deployment faster and more frequent—but that speed can be dangerous if you’re not paying attention to security. For SQL Server teams, that means catching vulnerabilities before they hit production. That’s where SAST and DAST come in. They’re not complicated concepts—one checks your code before it runs, the other pokes at your running system to find weaknesses.

SAST vs DAST: How They Work Together

SAST (Static Analysis) is like a code review from a security bot. It reads your code, your infrastructure templates, and your configurations before anything runs. It catches hardcoded passwords, SQL injection vulnerabilities, and missing input validation.

DAST (Dynamic Analysis) is the hacker simulator. It takes your running application and tries to break it—sending malicious input, trying to access restricted data, looking for configuration mistakes. It finds vulnerabilities in actual behavior, not just patterns.

The Tools You Actually Need

Scanning SQL Code for Injection Vulnerabilities

If you’re writing T-SQL, you want tools that understand SQL patterns. Here are the practical options:

Redgate SQL Code Analysis — Commercial, understands T-SQL anti-patterns including dynamic SQL and injection risks
tSQLt with custom checks — If you’re already using tSQLt for unit tests, you can add static checks
Microsoft SQL ScriptDom — Free, open-source. Build your own analyzer if you have specific rules

Checking Your Infrastructure Code (Bicep, Terraform, ARM Templates)

This is where most teams start because misconfigured databases are low-hanging fruit:

Checkov — Scans Terraform, Bicep, and ARM templates. Free, catches exposed SQL servers and unencrypted backups
PSRule for Azure — Microsoft’s tool, purpose-built for Azure. Good integration if you’re all-in on Azure
TFLint — Focused on Terraform but really good at catching configuration mistakes

Scanning Your Application Code

Your web app or API is the gateway to SQL Server:

SonarQube / SonarCloud — Works with most languages, has SQL plugin support
GitHub Advanced Security (CodeQL) — Free if you’re already using GitHub. Catches SQL injection patterns in application code
Checkmarx — Enterprise tool if you need it, but overkill for most teams

Testing Your Running Application (DAST)

OWASP ZAP — Free, open-source. Great for testing web apps that connect to SQL. You can run it in your pipeline
Burp Suite Community — Also free. More powerful if you need it, but steeper learning curve
Microsoft Defender for DevOps — If you’re on Azure, this is native integration

Both OWASP ZAP and Burp can test for SQL injection, insecure API endpoints, and other web vulnerabilities that lead to database breaches.

Real Examples

The Bad SQL Code

Here’s a stored procedure that’s vulnerable to SQL injection:

-- DON'T DO THIS
CREATE PROCEDURE sp_GetUserByUsername @username NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT UserId, Email FROM Users WHERE Username = ''' + @username + ''''
    EXEC sp_executesql @sql
END

If someone passes admin' OR '1'='1 as the username, the query becomes:

SELECT UserId, Email FROM Users WHERE Username = 'admin' OR '1'='1'

And now they get all users. Oops.

How to fix it:

-- DO THIS INSTEAD
CREATE PROCEDURE sp_GetUserByUsername @username NVARCHAR(100)
AS
BEGIN
    SELECT UserId, Email FROM Users WHERE Username = @username
END

Parameterized queries protect you. SAST tools will flag the first version immediately.

The Bad Bicep Configuration

param adminPassword string = 'DefaultP@ss123'

resource sqlServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  name: 'contoso-sql-prod'
  location: 'eastus'
  properties: {
    administratorLogin: 'sqladmin'
    administratorLoginPassword: adminPassword
    publicNetworkAccess: 'Enabled'
  }
}

resource firewallRule 'Microsoft.Sql/servers/firewallRules@2022-02-01-preview' = {
  parent: sqlServer
  name: 'AllowAllAzureIps'
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '255.255.255.255'
  }
}

Problems Checkov will find:

✗ Hardcoded password
✗ SQL Server exposed to the internet (publicNetworkAccess: 'Enabled')
✗ Firewall rule allows all IPs
✗ Missing encryption settings

Fixed version:

param keyVaultId string
param adminUsername string = 'sqladmin'

resource sqlServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  name: 'contoso-sql-prod'
  location: 'eastus'
  properties: {
    administratorLogin: adminUsername
    administratorLoginPassword: listSecrets(keyVaultId, '2021-06-01').value
    publicNetworkAccess: 'Disabled'
    minimalTlsVersion: '1.2'
  }
}

resource firewallRule 'Microsoft.Sql/servers/firewallRules@2022-02-01-preview' = {
  parent: sqlServer
  name: 'AllowAppSubnet'
  properties: {
    startIpAddress: '10.0.1.0'
    endIpAddress: '10.0.1.255'
  }
}

Run Checkov against it:

$ checkov -f main.bicep --framework bicep

Check: CKV_AZURE_11: "Ensure that SQL server enables encrypted connections"
  PASSED
Check: CKV_AZURE_28: "Ensure that sql servers do not allow ingress 0.0.0.0/0 (ALL ip)"
  PASSED
Check: CKV_AZURE_68: "Ensure that sql servers do not have public network access enabled"
  PASSED

The Bad Web App (Node.js)

const express = require('express');
const mssql = require('mssql');
const app = express();

const config = {
  user: 'sa',
  password: 'YourPassword123',
  server: 'localhost',
  database: 'userdb',
};

app.get('/api/users/:id', async (req, res) => {
  try {
    const userId = req.params.id;
    
    // VULNERABLE: user input concatenated directly into query
    const pool = new mssql.ConnectionPool(config);
    const result = await pool
      .request()
      .query(`SELECT * FROM Users WHERE UserId = '${userId}'`);
    
    res.json(result.recordset);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

An attacker calls /api/users/1' OR '1'='1 and gets all users.

Fixed version:

app.get('/api/users/:id', async (req, res) => {
  try {
    const userId = req.params.id;
    
    // SAFE: parameterized query
    const pool = new mssql.ConnectionPool(config);
    const result = await pool
      .request()
      .input('userId', mssql.Int, userId)
      .query('SELECT * FROM Users WHERE UserId = @userId');
    
    res.json(result.recordset);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

CodeQL (via GitHub Advanced Security) will catch the first version automatically.

Building Your Pipeline (The Practical Part)

Here’s an Azure DevOps pipeline that actually works:

trigger:
  branches:
    include:
    - main
    - develop

pool:
  vmImage: 'ubuntu-latest'

variables:
  buildConfiguration: 'Release'

jobs:
- job: SAST_IaC
  displayName: 'Scan Infrastructure Code'
  steps:
  - checkout: self
  
  - task: Bash@3
    displayName: 'Install Checkov'
    inputs:
      targetType: 'inline'
      script: |
        pip install checkov        
  
  - task: Bash@3
    displayName: 'Run Checkov on Bicep'
    inputs:
      targetType: 'inline'
      script: |
        checkov -f ./infra/main.bicep \
          --framework bicep \
          --check CKV_AZURE_28,CKV_AZURE_68,CKV_AZURE_11 \
          --output cli        
    continueOnError: false

- job: SAST_SQLCode
  displayName: 'Scan SQL Scripts'
  steps:
  - checkout: self
  
  - task: PowerShell@2
    displayName: 'Analyze T-SQL for Dynamic SQL'
    inputs:
      targetType: 'inline'
      script: |
        $sqlFiles = Get-ChildItem -Path './database' -Filter '*.sql' -Recurse
        $issues = @()
        
        foreach ($file in $sqlFiles) {
          $content = Get-Content $file.FullName -Raw
          
          if ($content -match "EXEC\s*\(" -and $content -match "\+|''" ) {
            $issues += "Dynamic SQL with string concatenation: $($file.Name)"
          }
          if ($content -match "sp_executesql.*\+" ) {
            $issues += "Concatenation in sp_executesql: $($file.Name)"
          }
        }
        
        if ($issues.Count -gt 0) {
          Write-Host "##vso[task.logissue type=error]Found security issues in SQL code:"
          $issues | ForEach-Object { Write-Host "  - $_" }
          exit 1
        }
        Write-Host "No dynamic SQL issues found ✓"        

- job: BuildAndTest
  displayName: 'Build Application'
  dependsOn: [SAST_IaC, SAST_SQLCode]
  condition: succeeded()
  steps:
  - checkout: self
  
  - task: UseDotNet@2
    displayName: 'Use .NET 8.0'
    inputs:
      packageType: sdk
      version: '8.0.x'
  
  - task: DotNetCoreCLI@2
    displayName: 'Restore NuGet packages'
    inputs:
      command: 'restore'
      projects: '**/*.csproj'
  
  - task: DotNetCoreCLI@2
    displayName: 'Build'
    inputs:
      command: 'build'
      arguments: '--configuration $(buildConfiguration)'

- job: DeployToStaging
  displayName: 'Deploy to Staging'
  dependsOn: BuildAndTest
  condition: succeeded()
  steps:
  - task: AzureResourceGroupDeployment@2
    displayName: 'Deploy Bicep Template'
    inputs:
      azureSubscription: 'MyAzureConnection'
      action: 'Create Update'
      resourceGroupName: 'rg-staging'
      location: 'eastus'
      templateLocation: 'Linked artifact'
      csmFile: './infra/main.bicep'
      overrideParameters: '-environment staging'

- job: DAST_SecurityTest
  displayName: 'Run Security Tests on Staging'
  dependsOn: DeployToStaging
  condition: succeeded()
  steps:
  - checkout: self
  
  - task: Bash@3
    displayName: 'Install OWASP ZAP'
    inputs:
      targetType: 'inline'
      script: |
        docker pull owasp/zap2docker-stable        
  
  - task: Bash@3
    displayName: 'Run ZAP Baseline Scan'
    inputs:
      targetType: 'inline'
      script: |
        docker run -v $(Build.ArtifactStagingDirectory):/zap/wrk:rw \
          --rm owasp/zap2docker-stable zap-baseline.py \
          -t http://staging-app.azurewebsites.net \
          -r zap_report.html        
    continueOnError: true
  
  - task: PublishBuildArtifacts@1
    displayName: 'Publish ZAP Report'
    inputs:
      PathtoPublish: '$(Build.ArtifactStagingDirectory)/zap_report.html'
      ArtifactName: 'security-reports'

This pipeline:

Scans Bicep and SQL code before anything gets deployed
Builds and tests the application
Deploys to staging only if SAST checks pass
Runs OWASP ZAP against the staging environment
Publishes the security report as an artifact

How to Start (Realistically)

You don’t need to implement everything at once. Here’s a reasonable roadmap:

Week 1: Add basic SQL code scanning to your pipeline (the PowerShell script above)

Week 2: Add Checkov for your infrastructure code. Takes 10 minutes to set up.

Week 3: Integrate into your existing CI/CD. Make failed checks block deployments.

Week 4: Set up OWASP ZAP against your staging environment. You can run it manually first, then automate it.

Month 2: Start requiring teams to fix findings before merging PRs.

Month 3: Review what you’ve caught and iterate on your rules.