Enhancing SQL PaaS Security: Automating Azure Blob SAS Key Rotation Using Azure Runbooks

#________________________________

#Input parameters
#________________________________
$subscriptionId =xxxxx-459a-409f-8339-xxxxxxxx
$resourceGroupName = "ProdRG"
$storageAccountName = "Prodstorageaccount"
$keyVaultName = "Prodkeyvault"
$secretName = "SASForHRTK"
$SASexpiryDate =90 # days
$automationAccountName = "AutomationAccount"
$global:NewSASKey="";
$database ="master"
        
        
Connect-AzAccount -Identity 
Set-AzContext     -subscriptionId $subscriptionId | Out-Null *>$null

# Retrieve SQL Managed Instances and construct DNS names
$sqlInstances = Get-AzSqlInstance | Select-Object ManagedInstanceName, DnsZone
$SQLMIList = $sqlInstances | ForEach-Object {
    $dnsName = "$($_.ManagedInstanceName).public.$($_.DnsZone).database.windows.net,3342"
    [PSCustomObject]@{
        DNSName             = $dnsName
    }
}

#_______________________________________________________________
# Variables used
#_______________________________________________________________
Write-Output "[$(Get-Date)]: __________________ Beginning of SAS Key Rotation Script __________________"
Write-Output "[$(Get-Date)]: Input Parameters for Execution:"

# Add input parameters
Write-Output "`n[$(Get-Date)]: Subscription ID         : $subscriptionId"
Write-Output "[$(Get-Date)]: Resource Group Name       : $resourceGroupName"
Write-Output "[$(Get-Date)]: Storage Account Name      : $storageAccountName"
Write-Output "[$(Get-Date)]: Key Vault Name            : $keyVaultName"
Write-Output "[$(Get-Date)]: Secret Name               : $secretName"
Write-Output "[$(Get-Date)]: SAS Expiry Date           : $SASexpiryDate"
Write-Output "[$(Get-Date)]: User Principal Name       : $UserPrincipalName"
Write-Output "[$(Get-Date)]: Connected to subscription: [$( (Get-AzSubscription -subscriptionId $subscriptionId).Name )]`n"
Write-Output "[$(Get-Date)]: SQL Managed Instances identified in the current subscription `n"
Write-Output ("_" * 60 + "`n");  $SQLMIList.DNSName;  Write-Output ("`n" + ("_" * 60))

#_______________________________________________________________
# Generate a new SAS token and update Keyvault
#_______________________________________________________________
function GenerateSASToken_And_UpdateKeyVault {
    $context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey ((Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName -WarningAction SilentlyContinue)[0].Value ) -WarningAction SilentlyContinue
    $expiryTime = (Get-Date).Adddays($SASexpiryDate).ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ")
    
    try {
        $sasToken = New-AzStorageAccountSASToken -Service Blob -ResourceType Container,Object -Permission "rwdlact" -ExpiryTime $expiryTime -Context $context
        Write-Output "[$(Get-Date)]: SAS Token generated successfully"
        $global:NewSASKey=$sasToken
    } catch {
        $ErrorOnSASkeyGeneration=$True
        Write-Output "[$(Get-Date)]: Failed to generate SAS Token. Error: $_"
        return ("ErrorOnSASkeyGeneration")
    }
    $securesasToken = $sasToken | ConvertTo-SecureString -AsPlainText -Force
    

    if ($ErrorOnSASkeyGeneration -ne $True)
    {
        # Check if the secret exists in Key Vault
        $secret = Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $secretName -ErrorAction SilentlyContinue 
        try {
            if ($secret) {
                # Update the existing secret with the new SAS token
                $res=Set-AzKeyVaultSecret -VaultName $keyVaultName -Name $secretName -SecretValue $securesasToken -Expires $expiryTime -ContentType ("SASToken_Created_"+(Get-Date -Format "yyyy-MM-dd_HH:mm:ss"))
                Write-Output "[$(Get-Date)]: Secret updated in Key Vault - $keyVaultName with key - $secretName." 
            } else {
                # Create a new secret in Key Vault
                $res=Set-AzKeyVaultSecret -VaultName $keyVaultName -Name $secretName -SecretValue $securesasToken  -Expires $expiryTime -ContentType ("SASToken_Created_"+(Get-Date -Format "yyyy-MM-dd_HH:mm:ss"))
            Write-Output "[$(Get-Date)]: New Secret created in Key Vault - $keyVaultName with key - $secretName."
            }
        } catch {
            # Handle the error
        Write-Output "[$(Get-Date)]: An error occurred while updating or creating the secret in Key Vault."
        Write-Output "Error Message: $_"
            $exception=$True
        }
    
    }

}

#_______________________________________________________________
# Update Database scope credentials on SQL Managed instance
#_______________________________________________________________
function UpdateDatabaseScopeCredential {
param ([string]$sqlmi)

$myCred = Get-AutomationPSCredential -Name "CredentialforMI"
Write-Output "[$(Get-Date)]:Connecting to the Server : $sqlmi"

try {    
    #Using managed identity        
    $secureToken = Get-AzAccessToken -ResourceUrl "https://database.windows.net/" -AsSecureString
    $token = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secureToken.Token))
    $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=$sqlmi;Database=$database;Encrypt=True;Connection Timeout=30;")
    $Conn.AccessToken = $token
    
    $Conn.Open()
    $Command = $Conn.CreateCommand()
    $Command.CommandText = "EXEC $database.[dbo].[UpdateDatabaseScopedCredential] '$storageAccountName', '$NewSASKey'"
    
    $rowsAffected = $Command.ExecuteNonQuery()
    if ($rowsAffected -eq -1) {
        Write-Output "[$(Get-Date)]: The command executed successfully."
    } else {
        Write-Output "[$(Get-Date)]: $rowsAffected rows were affected."
    }
    Write-Output "[$(Get-Date)]: Updated the SASKey on $sqlmi"

} catch {
    Write-Output "[$(Get-Date)]:An error occurred: $_"
    } finally {
     if ($Conn.State -eq 'Open') {
    $Conn.Close()
    }
  }
}

Write-Output "[$(Get-Date)]: Attempting to generate a new SASToken for Storage account :[$storageAccountName] and update Keyvault :[$keyVaultName]"
GenerateSASToken_And_UpdateKeyVault

Write-Output "[$(Get-Date)]: Attempting to update the database scope credential for SQL Managed instances"
Write-Output "$SQLMIList`n"
foreach ($SQLMI in $SQLMIList) {
    UpdateDatabaseScopeCredential -sqlmi $SQLMI.DNSName
 }

Write-Output "[$(Get-Date)]: __________________ End of SAS Key Rotation Script __________________"

Use [DBA]
go

CREATE OR ALTER PROCEDURE UpdateDatabaseScopedCredential
    @Storactaccount NVARCHAR(128),
    @NewKey NVARCHAR(MAX)
     WITH ENCRYPTION
AS
BEGIN
set nocount on
-- Declare variables for dynamic SQL
DECLARE @sql NVARCHAR(MAX);
DECLARE @dbName NVARCHAR(128);
DECLARE @credentialName NVARCHAR(128);


IF OBJECT_ID('dbo.SASKeyRotationLogging', 'U') IS NULL
BEGIN
CREATE TABLE SASKeyRotationLogging (Timestamp DATETIME DEFAULT GETUTCDATE(), Details NVARCHAR(MAX));
END

PRINT REPLICATE('_', 100)+ CHAR(13) + CHAR(10)+ '[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Starting the script to update the credentials on Server [' + @@SERVERNAME + '].';
INSERT INTO master..SASKeyRotationLogging (Details) VALUES ('[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Starting the script to update the credentials on Server [' + @@SERVERNAME + '].');

INSERT INTO master..SASKeyRotationLogging (Details)
SELECT '[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Database name: ' + name
FROM sys.databases
WHERE name LIKE '%_ems';

-- Cursor to iterate over databases
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name LIKE '%_ems';



OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN

   -- Construct the dynamic SQL to be executed in each database
   SET @sql = '
        USE [' + @dbName + '];
             
             DECLARE @Result TABLE (CredentialName NVARCHAR(128));
             INSERT INTO @Result (CredentialName)
             SELECT c.name AS CredentialName 
        FROM sys.database_scoped_credentials AS c
        INNER JOIN sys.external_data_sources AS ds ON ds.credential_id = c.credential_id
        WHERE c.credential_identity = ''SHARED ACCESS SIGNATURE'' AND ds.type = 5 
        AND ds.location LIKE ''%' + @Storactaccount + '%''; -- Corrected LIKE syntax

             -- Update the credentials
        DECLARE @credName NVARCHAR(128);
        DECLARE cred_cursor CURSOR FOR
        SELECT CredentialName FROM @Result;

        OPEN cred_cursor;
        FETCH NEXT FROM cred_cursor INTO @credName;

        WHILE @@FETCH_STATUS = 0
        BEGIN
             
             -- Construct the ALTER DATABASE SCOPED CREDENTIAL command
            DECLARE @alterSql NVARCHAR(MAX);
            SET @alterSql = ''ALTER DATABASE SCOPED CREDENTIAL ['' + @credName + ''] WITH IDENTITY = ''''SHARED ACCESS SIGNATURE'''', SECRET = ''''' + @NewKey + ''''';''

            EXEC sp_executesql @alterSql;
                    
                    PRINT ''['' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + ''] [Database: ' + @dbName + '] Updated the key for credential ['' + @credName + ''].''
                    INSERT INTO master..SASKeyRotationLogging (Details) VALUES (''['' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + ''] [Database: ' + @dbName + '] Updated the key for credential ['' + @credName + ''].'');

 
             FETCH NEXT FROM cred_cursor INTO @credName;
             END ';

   -- Execute the dynamic SQL
   EXEC sp_executesql @sql, N'@Storactaccount NVARCHAR(128)', @Storactaccount;
   FETCH NEXT FROM db_cursor INTO @dbName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;
PRINT '[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] End of script to update the credentials on Server [' + @@SERVERNAME + '].'+ CHAR(13) + CHAR(10)+REPLICATE('_', 100);
INSERT INTO master..SASKeyRotationLogging (Details) VALUES ('[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Ending the script to update the credentials on Server [' + @@SERVERNAME + '].');

END