Can't execute query to an Azure SQL Server from PowerShell

Question

I have a SQL Azure database and I try to execute the query from PowerShell.

I am using Invoke-Sqlcmd Command.

I get this error message:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

But if I connect from Management Studio, everything is OK.

I setup a proper firewall rule.

Does anybody know what may happen?

Can you share details of what parameters are you passing in the command. — Kaushal Kumar Panday
– Kaushal Kumar Panday, Commented Jul 12, 2017 at 14:31

Pang · Accepted Answer · 2017-11-30 01:14:11Z

3

Answer recommended by Microsoft Azure Collective

On your connection string, specify TCP as the protocol like

tcp:mymssqlserver.database.windows.net

Your PowerShell may look then like:

$server = " tcp:mymssqlserver.database.windows.net,1433" 
$database = "master" 
$adminName = "admin@my-azure-sql" 
$adminPassword = "P4SSW0rd" 


$connectionString = "Server=$server;Database=$database;User ID=$adminName;Password=$adminPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" 
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

edited Nov 30, 2017 at 1:14

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jul 12, 2017 at 14:25

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Gibson - MSFT Over a year ago

In Alberto's answer, particularly important is the connection timeout setting, ensure it's 30 seconds or more.

Collectives™ on Stack Overflow

Can't execute query to an Azure SQL Server from PowerShell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related