I need to do an Invoke-SQLCmd in Terraform - all fine BUT I need to get the Secret for the service principal (Azure) that is being used throughout the build. So I can use this :

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that Service Principal and has granted it access to the database (in this example at least
#       the SELECT permission).

$clientid = "enter application id that corresponds to the Service Principal" # Do not confuse with its display name
$tenantid = "enter the tenant ID of the Service Principal"
$secret = "enter the secret associated with the Service Principal"

$request = Invoke-RestMethod -Method POST `
           -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`
           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$secret }`
           -ContentType "application/x-www-form-urlencoded"
$access_token = $request.access_token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'

I can get the cliendId and the TenantID quite easily within PowerShell but I cannot get the secret. So how would i get it ? although i am using the same Service Prinical during the build.

CodePudding user response：

As I have already mentioned you can only retrieve a secret value at the time of creation and after that it becomes hidden . So , its recommended to store the created in some secure place or keyvault.

As you can see for testing I used

$end_date = (get-date).Date.AddDays(365)
## Create new Secret
$createsecret = New-AzureADApplicationPasswordCredential -CustomKeyIdentifier "PowershellKey" -ObjectId $APP.ObjectId -EndDate $end_date
## Secret Value
Write-Host ("Secret Value For new Secret :")$createsecret.value