powershellazure-active-directoryazure-cloud-shellinvoke-sqlcmd

Run SQL query using powershell and AAD authentication with an SSO account

I am trying to run a set of queries from Cloud Shell using powershell that require and Azure AD user. I generally use invoke-sqlcmd using the server admin and password but the specific query I want to run require an AD user. I would like to run the query as myself. I have found that I can use the ConnectionString parameter to do this, but my problem is I do not have a password as I login to Azure via SSO. I basically want to mimic the process of going to my DB in Azure, going to query editor, using AAD authentication and running a query, but with powershell. Is there a way I can do this? Example of what I am trying to do below:

$serverName = 'server'
$dbName = 'database'
$query = 'CREATE USER [AAD_User] FROM EXTERNAL PROVIDER;'
Invoke-Sqlcmd -ServerInstance $ServerName -Database $dbName -Query $query

When I attempt this I get:

Invoke-Sqlcmd: Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.
Solution

  • I tried to reproduce the same in my environment and got the results like below:

    As SSO is enabled and you do not have a password, you can try generating access token to perform the action.

    I used the below script to add the user in Azure Database:

    Connect-AzAccount

$connectionString = "Server=testrukserver.database.windows.net;Initial Catalog=testdb;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
$accessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $connectionString
$sqlConn.AccessToken = $accessToken
$sqlConn.Open()
Invoke-Sqlcmd -ServerInstance testrukserver.database.windows.net -Database testdb -AccessToken $accessToken -query 'CREATE USER [test@xxx.onmicrosoft.com] FROM EXTERNAL PROVIDER'
$sqlConn.Close()

    enter image description here

    The user is added sccessfully in the Azure Database like below:

    enter image description here

    Reference:

    Connecting to Azure SQL Database with AAD Authentication using Powershell by alex stuart