azurepowershellazure-sql-databaseazure-sql-managed-instance

Query a database inside Azure managed instance from Powershell

My initial code queries database in my Azure SQL server:

                $sql = @{
                Server         = "$($sqlServer.ServerName).$($dbURI)"
                Database       = $db.DatabaseName
                Username       = $sqlUser
                Password       = $sqlPass
                Query          = "SELECT * FROM [dbo].[SystemInfo] WHERE SystemInfo XXXX
            }
            $testquery = Invoke-Sqlcmd @sql

I need a command to successfully query the database in AZURE MANAGED INSTANCE using powershell. I am unable to get a good structure for it and also the connection string syntax.

Solution

  • You can follow below procedure to query a database inside Azure managed instance from PowerShell:

    You should enable Public endpoint in managed instance networking tab as shown below:

    enter image description here

    This option requires port 3342 to be open for inbound traffic. You will need to configure NSG rule for this port separately. So, create inbound rule for port 3342 by following this MS document. After creating it use public endpoint which is mentioned above image Use below command to query managed instance database from PowerShell:

    $managedInstanceName = "<sqlMIName>.public.2e06a1190d03.database.windows.net,3342"
$userName = "<userNmae>"
$password = "<password>"
$Query = "select * from db.dbo.student"
Invoke-Sqlcmd  -ConnectionString "Data Source=$managedInstanceName;User Id=$userName; Password =$password; Integrated Security=False;" -Query "$Query"

    It will query database successfully as shown below:

    enter image description here