CODEWITHSUNDEEP
azure-sql-serverinvoke-sqlcmdmulti-factor-authentication
borys86
borys86

Reputation: 1984

Invoke-Sqlcmd with account that uses MFA in Azure

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.

Upvotes: 5

Views: 2648

Answers (2)

Sage Pourpre
Sage Pourpre

Reputation: 10333

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.)

Upvotes: 2

jleyva
jleyva

Reputation: 96

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 [email protected]'
    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,

Upvotes: 1

Related Questions