4

Anyone of you knows an option to execute a SQL command using PowerShell with Azure AD account that has MFA enabled? What is an alternative? Do I need to create a service principal for that?

I had no luck but only found this cmdlet, Add-SqlAzureAuthenticationContext but when I try to run Invoke-Sqlcmd I am getting the following error:

Invoke-Sqlcmd : The target principal name is incorrect. Cannot generate SSPI context.

borys86
  • 1,894
  • 2
  • 13
  • 13

2 Answers2

2

To connect to an azure database using AAD credential (mfa or not), you need to provide the -AccessToken parameter with a token of the authenticated user or service principal.

Take this for instance.

Connect to Azure SQL Database using an access token

# Obtain the Access Token: this will bring up the login dialog
Connect-AzAccount -TenantId 'Tenant where your server is'

#AZ Module
 $AccessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

 $SQLInfos = @{
    ServerInstance = 'SERVERNAME.database.windows.net'
    Database = 'DBNAME'
    AccessToken = $AccessToken
}

Invoke-Sqlcmd @SQLInfos -Query 'select * from sys.tables'

If you don't need or want the manual credentials entry, you can make use of a service principal that is configured with proper access to the server / database and use this instead to obtain your token.

Using service principal client ID / secret to get the access token

$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"
$AccessToken = $request.access_token

References

MSdoc - Invoke-Sqlcmd

SecretManagement / SecretStore modules

(This second link is not directly related but if you go the Client ID / Secret route, consider storing your credentials in a secret vault rather than in your script directly.)

Jthorpe
  • 9,756
  • 2
  • 49
  • 64
Sage Pourpre
  • 9,932
  • 3
  • 27
  • 39
  • After a day of hair pulling, I discovered that this solution work as long as I \*don't\* call `Connect-AzAccount` (which the author of this post dutifully copied form the referenced docs) – Jthorpe Mar 19 '22 at 18:27
  • @Jthorpe `Get-AzAccessToken` won't work if you are not connected. Since Azure save the session context on your machine though, you technically remain connected and do not need to connect every time. Also, if you use `Connect-AzAccount` without specifying the tenant id, you might connect to your "default tenant" which might be different and hinder the `Get-AzAccessToken` step. If you are doing it in a different context (eg: Azure function, scheduled task), you might need to use a service principal or nothing at all (since Az function connect elsewhere) – Sage Pourpre Mar 19 '22 at 23:42
  • As for the second example, it does not require any `Connect-AzAccount` call. My point is if you use `Get-AzAccessToken` without the `Connect-AzAccount`, it might work while you are connected from a previous session... until it doesn't. Just something to keep in ( Since I don't know your exact context, I might have missed something too ) – Sage Pourpre Mar 19 '22 at 23:45
  • In my case, `Connect-AzAccount` connected me to an account that didn't have sufficient permissions, but leaving it out connected me to my windows login credentials which did have sufficient permissions. (I have several accounts and haven't figured out how to tell connect-azaccount which account to connect to -- which strangely didn't give me the usual azure AD popup with account picker and likely just used some other cached credentials...) – Jthorpe Mar 20 '22 at 01:59
  • This worked; I preferred to use the non-hashlist syntax: `Invoke-Sqlcmd -InputFile "C:\example.sql" -ServerInstance 'SERVERNAME.database.windows.net' -Database 'DBNAME' -AccessToken $AccessToken` – mlhDev Apr 28 '22 at 12:28
1

Not really an answer.

Tried the following 

    Add-SqlAzureAuthenticationContext -Interactive
    $sql = 'SELECT @@SERVERNAME AS ServerName';   
    $ConnectionString = 'Data Source=tcp:azSERVER.database.windows.net,1433;Initial Catalog=DBNAME;Authentication="Active Directory Interactive";User ID=USERXX@DOMAINYY.COM'
    Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql -Verbose

and got

Invoke-Sqlcmd : One or more errors occurred. At line:4 char:5

Please notice that I want to use the interactive flag,

jleyva
  • 96
  • 6