-> Requirement will be to query an Azure SQL Database via PowerShell and display the results. We will not list the SQL Server Instance, Username and password as clear text in PowerShell script, instead we use the secret from Azure Key Vault to achieve this.

Create an Azure SQL Database

-> Complete tabs “Networking”, “Security”, “Additional settings” and “Tags” as appropriate.

Create an Azure Key Vault

-> Complete tabs “Access policy”, “Networking” and “Tags” as appropriate.

-> Get into Azure Key Vault “sqlcon-key-vault-eastus”. Click on “Secrets” and then “Generate/Import”,

-> Value as mentioned in above screenshot is as below,

Server=tcp:jbdw.database.windows.net,1433;Initial Catalog=prod-jbsqldb01;Persist Security Info=False;User ID=<username>;Password=<Password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

-> Click Create.

Get the required Data using PowerShell

##Script to display database names available## 
## Version 1.0 
##Author : Vivek Janakiraman## 
##cls ##Clearing Screen 

Connect-AzAccount

##Key in the correct Subscription below##
Set-AzContext -Subscription "00000000-0000-0000-0000-000000000000"

$keyvault = "sqlcon-key-vault-eastus" ##Provide the Azure key Vault name##
$ConnectionString = "prod-jbsqldb01-connection-string" ##Provide the the name of the secret created within Azure key Vault##


$Vault = get-azkeyvault -VaultName $keyvault
$Secret = get-azkeyvaultsecret -Name $ConnectionString -VaultName $keyvault
$SecretValue = $Secret.SecretValue 
$ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecretValue))

$tsqlcommand = "select name from sys.databases"; 

#SQL Connection - connection to SQL server 
$sqlConnection = new-object System.Data.SqlClient.SqlConnection; 
$sqlConnection.ConnectionString = $connectionString; 

#SQL Command - set up the SQL call 
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand; 
$sqlCommand.Connection = $sqlConnection; 
$sqlCommand.CommandText = $tsqlcommand; 

#SQL Adapter - get the results using the SQL Command 
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand 
$dataSet = new-object System.Data.Dataset 
$recordCount = $sqlAdapter.Fill($dataSet) 

#Close SQL Connection 
$sqlConnection.Close(); 

#Get single table from dataset 
$data = $dataSet.Tables[0] 
$data

-> Below is the output,

-> There is a possibility of below error while executing the script,

Exception calling "SecureStringToBSTR" with "1" argument(s): "Value cannot be null.
Parameter name: s"
At C:\temp\Connection_Template_Working.ps1:16 char:1
+ $ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToSt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
 
Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 0."
At C:\temp\Connection_Template_Working.ps1:22 char:1
+ $sqlConnection.ConnectionString = $connectionString;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting
 
Exception calling "Fill" with "1" argument(s): "The ConnectionString property has not been initialized."
At C:\temp\Connection_Template_Working.ps1:33 char:1
+ $recordCount = $sqlAdapter.Fill($dataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

Exception calling “SecureStringToBSTR” with “1” argument(s): “Value cannot be null.

-> This error occurs when parameter $ConnectionString is wrong. It should be same as what is seen in below screenshot in Azure key Vault,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.