Retrieving an Azure Key Vault secret using Powershell

 Here's the script I used to retrieve secrets from an Azure Key Vault.

Clear-Host

$VaultName = "your-vault-name"

Write-Output ""

$SearchName = Read-Host -prompt "Secret Name "

$secretName = $SearchName

$secretValue = $(Get-AzKeyVaultSecret -VaultName $VaultName -Name $secretName -AsPlainText)

Write-Output "$($secretName) | $($secretValue)"

I used to have this script searching the whole key vault using the -match  operator based on a search string.  But I have changed that since because it tries to access the key vault a lot which in the future might cause issues if we get some sort of auditing to the key vault resource.  Also, I find it really slow to return anything when the key vault is fully populated with thousands of secrets.

Force Invoke-Webrequest to use TLS 1.2

Today, I needed to force an Invoke-WebRequest command to use TLS 1.2 to test access to Azure Key Vault. I ran the command below to force it:

[Net.ServicePointManager]::SecurityProtocol =[Net.SecurityProtocolType]::Tls12

And, here's the Invoke-WebRequest command I was using for testing:

$(Invoke-WebRequest -UseBasicParsing -Uri https://your-key-vault-name/healthstatus).Headers

Disabling a SQL Agent Job Using T-SQL

Yesterday, I had to run a full backup of a 764 GB database to clear up the differential backup that grew up to 600 GB (almost the same size as the database) because of enabling TDE on the database after a full restore.  

This database is being restored every first weekend of the month.  And we enable TDE encryption after.  A full backup is immediately run after. 

At a high level, here are the SQL Agent Job steps that we had:

1.  Restore the database from backup
2.  Enable TDE
3.  Run a Full database backup

The issue here is that after the TDE job step, the job goes on and immediately runs the full backup step.  This causes the next differential backup to be huge.  As it will change all the pages in the database after the full database backup.

To Remedy the issue, I had to add a few steps to the restore job, and I had to disable the differential backups until the TDE encryption was done.

At a high level, the job now looks like this:

1.  Disable the differential backup job
2.  Restore the database from backup
3.  Enable TDE (loop until the encryption is done)
4.  Run a Full database backup
5.  Enable the differential backup job

Here's the code I used to disable/enable the differential backup job:

 
EXEC dbo.sp_update_job 
    @job_name = N'Database Backup - DBNAME - Diff w Compress', 
    @enabled = 0  
GO 
 
EXEC dbo.sp_update_job 
    @job_name = N'Database Backup - DBNAME - Full w Stripe and Compress', 
    @enabled = 0  
GO  

Note that @enabled=0 means to disable, and @enabled=1 means to enable.

And as a bonus, here's the code I used to loop until the encryption is done:

 
DECLARE
       @encryptionState INT
 
BEGIN
 
SELECT @encryptionState = encryption_state
FROM sys.dm_database_encryption_keys
WHERE DB_ID('DBNAME') = database_id
 
 
IF @encryptionState = 3
       BEGIN
              SELECT 'DBNAME is encrypted.  Proceed to next step';
       END
ELSE
       BEGIN
              SELECT 'DBNAME is still encrypting.  Loop every hour to check'
              ;THROW 51000, 'Trying again next hour', 1
       END
 
END

I set it up to retry every hour for 3 days.