invoke-sqlcmd service principal

Question

https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

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'

is there any way not to put the secret in the script ?

Answer 1

Hi @sakuraime , welcome to Microsoft Q&A forum.

Client secret cannot be retrieved once after we set it. So it has be to either hard-coded in the script or store it somewhere else from where we can read it. One way is to store it in a keyvault and use it as and when needed.

Please refer to below article which explains how can we store and retrieve it. We just need to set the right value of secret here.

Set and retrieve a secret from Azure Key Vault using PowerShell

Please let me know if this helps.

----------

If answer helps, please mark it 'Accept Answer'