question

sakuraime avatar image
0 Votes"
sakuraime asked AnuragSharma-MSFT commented

invoke-sqlcmd service principal

https://docs.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 ?


azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT commented

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'




· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks. but if using keyvault, what's the permission to get the secret from that computer while running the powershell script ?

Get-AzKeyVaultSecret


as I think I need to do MANUAL "Connect-AzAccount", in order to get "Get-Azkeyvaultsecret" to work ...

0 Votes 0 ·

Hi @sakuraime, thanks for the reply.

Yes you are right that if we are running the PowerShell script from external systems, we need to connect using 'Connect-AzAccount' command and then work with the vault accordingly.

0 Votes 0 ·

any work around for this ?

0 Votes 0 ·
Show more comments