8

My task is to retrieve the users list from our Azure SQL databases. We have 100s of Azure SQL database and I want to use PowerShell to make the task fast.

I am using connection string (Active Directory Integrated). I believe I am able to login to the SQL database using the connection string with PowerShell.

However, I am getting an error while running the SQL. Below is the code and exception. Could you please help me?

Code: 

try {
    $new = 'Server=tcp:dummy.database.windows.net,1433;Authentication="Active Directory Integrated";Initial Catalog=xtoiteuitbdbsqldb01;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;'
    $sql = "SELECT * FROM sys.database_principals"
  $temp_result_object = invoke-sqlcmd -ConnectionString $new -Query $sql
} catch {
  "error when running sql $sql"
  write-host "Exception type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
    write-host "Exception message: $($_.Exception.Message)" -ForegroundColor Red
    write-host "Error: " $_.Exception -ForegroundColor Red   
}

Exception:

Exception type: ManagedBatchParser.ParserException
Exception message:
Error: ManagedBatchParser.ParserException

at ManagedBatchParser.Parser.Parse()
at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)

Improve this question
5
  • Are you using Invoke-SqlCmd from the SQLPS module? I don't believe it has a -ConnectionString parameter. Try Get-Help Invoke-SqlCmd. Commented Oct 18, 2019 at 3:23
  • I checked the Help file and it does not contain "-connectionString". What do I use? Commented Oct 18, 2019 at 3:42
  • 1
    Rather than constructing a single connection string make use of the individual -Server, -ServerInstance and -Database parameters. It's just a wrapper around the sqlcmd.exe command line tool so can basically do (most of) whatever that tool can do. Commented Oct 18, 2019 at 3:48
  • I am connecting to Azure sql database (PaaS offering). The idea is to connect to the Azure SQL Database using the connection string that has Active Directory Integration provided my Microsoft Azure to by pass providing UserID and Password. Commented Oct 18, 2019 at 4:12
  • If you're stuck with Azure AD Authentication then I think you cannot use the Invoke-SqlCmd PowerShell cmdlet. You'll have to use the sqlcmd.exe tool directly so you can use its [-G use Azure Active Directory for authentication] parameter, which the Invoke-SqlCmd cmdlet doesn't expose. See Azure AD token - sqlcmd for an example. Commented Oct 18, 2019 at 7:03

3 Answers 3

Reset to default
16

The below worked for me, Connect-AzAccount triggers interactive AAD auth via the browser

Example 11: Connect to Azure SQL Database (or Managed Instance) using an Access Token

Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0

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

### Obtain the Access Token: this will bring up the login dialog
Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).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'

Alternatively get a Managed Identity access token (source)

$resource = "https://database.windows.net"
$endpoint = $env:IDENTITY_ENDPOINT
$header = $env:IDENTITY_HEADER
$apiVersion = "2019-08-01"

$headers = @{ 'X-Identity-Header' = $header }

$url = "$($endpoint)?api-version=$apiVersion&resource=$resource"

$response = Invoke-RestMethod -Method Get -Uri $url -Headers $headers
$response.access_token
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This definitely worked for me. I'd mark it as an Answer if I could...
I am trying Invoke-Sqlcmd and is not working for me, get an error establishing connection to sql server. I have entra auth enabled for the managed instance and admin is an entra group which has my service principal added. I try to execute in cloud shell and see the same issue. Any suggestions?
@Lucky have you checked the az sql firewall rules, you may not have internet access to the sql server instance
4

You can use the .NET SqlClient provider in order to use either password or "Active Directory Integrated" authentication, e.g.:

# connect to database
$dbConn = New-Object System.Data.SqlClient.SqlConnection
$dbConn.ConnectionString = "Server=tcp:dummy.database.windows.net,1433;Database=DummyDB;Authentication=Active Directory Integrated;Encrypt=True;"
$dbConn.Open()

# construct command
$dbCmd = New-Object System.Data.SqlClient.SqlCommand
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "SELECT * FROM sys.database_principals"

# fetch all results
$dataset = New-Object System.Data.DataSet
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $dbCmd
$adapter.Fill($dataset)

# display results
$dataset.Tables | Format-Table
Improve this answer

3 Comments

I get this exception: Exception calling "Open" with "0" argument(s): "One or more errors occurred." At X $dbConn.Open() + ~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : AggregateException Exception calling "Fill" with "1" argument(s): "One or more errors occurred." At x + $adapter.Fill($dataset) + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : AggregateException
Hmmm, it may be a connection/permissions issue, but you'll need to drill down into that AggregateException to see what the underlying error is. This answer shows an example of dealing with AggregateExceptions: stackoverflow.com/a/50804912/8626917
I also got the same error and could never get any more detailed info on it.
-2

Try something like below and see if it helps: 

$Svr = "SVR"
$SvrPort = 10003
$Username = "ABC\sql"
$Password = "ABC#@!"

$Password = $Password | ConvertTo-SecureString -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$Password
$Inst = $Svr + "," + $SvrPort

Get-PSSession

New-PSSession -ComputerName $Svr -Credential $Cred

Invoke-Sqlcmd -ServerInstance $Inst -Database "master" -Query "SELECT @@SERVERNAME AS SERVER_NAME"

Get-PSSession

Get-PSSession | Remove-PSSession

Invoke-Sqlcmd -ServerInstance $Inst -Database "master" -Query "SELECT @@SERVERNAME AS SERVER_NAME"

Get-PSSession

Also refer to below post for further reference:

Active Directory Password Connection Azure SQL Failure from Azure Automation

Improve this answer

3 Comments

We do not want to use username and password. We want to use the "Active Directory Integration" option from the ADO.NET connection string.
Manjunath - see golfalot's post above.
Question is about connecting to an Azure SQL instance - PSSession isn't going to help here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.