How to execute DDL SQL Scripts on Azure Managed instance using Azure portal CLI

Question

I can do all the activities around the frame work of Azure managed Instances like create MI, update, delete tasks and as well I can also take a backup and restore, but how to connect to Azure MI using Azure CLI on portal and execute DDL scripts like select * from db.schema.table.

I am getting the error :-

Connection failed. Error: Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)"

can anyone let me know how to connect to Azure MI for DDL operations using Azure CLI on portal.

once this is successful I want to use same scripts in azure devops pipelines.

Request help.

I have tried these type of commands to make a connection and execute SQL scripts

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionString

And also using invoke-SQLCMD

but no luck

Answer 1

Connection failed. Error: Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)"

When the SQL server is not configured to allow remote connections, the error may be raised. To configure it, go to the managed instance networking tab, enable Public endpoint, and use that endpoint as shown below:

This option requires port 3342 to be open for inbound traffic. You will need to configure an NSG rule for this port separately. Go to the subnet of the VNet and select NSG, go to inbound security rules and Add a rule that has higher priority than the deny_all_inbound rule as shown below:

Then you will be able to connect the SQL managed instance successfully through Azure CLI using the command below:

sqlcmd -S tcp:<serverName>.database.windows.net,3342 -U <userName> -P '<password>' -d db -Q "<query>"

For more information, you can refer to this.