Powershell Query on Azure SQL Table

Question

I have a script that runs perfectly with an on-prem SQL Server. I'm trying to run the same thing using Azure SQL and can't seem to figure it out. Any help would be greatly appreciated.

It returns an error on the Fill portion. Specifically

Exception calling "Fill" with "1" 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: 0 - A connection attempt failed because
the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"
At C:\smtpsend\Test.ps1:24 char:1

• $SqlAdapter.Fill($DataSet)
• ~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : NotSpecified: (:) [], MethodInvocationException
• FullyQualifiedErrorId : SqlException

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: 0 - A connection attempt failed because
the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"
At C:\smtpsend\Test.ps1:26 char:1

• $sqlConnection.Open()
• ~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : NotSpecified: (:) [], MethodInvocationException
• FullyQualifiedErrorId : SqlException

Code:
$SQLServer = "xxxxxx.database.windows.net"
$SQLDBName = "xxx"
$uid ="mss_test"
$pwd = "pwd"

$SqlQuery = "select * from azemailqueue where msgsent=0;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;Encrypt=True"
$SQLConnection.Open
$Test = $SqlConnection.ConnectionString

$SqlCmd = new-object system.data.sqlclient.sqlcommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $SqlQuery
$Command = new-object system.data.sqlclient.sqlcommand
$Command.CommandType = [System.Data.CommandType]::Text
$Command.Connection = $SqlConnection
$Command.CommandText = $SqlQuery

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

Answer 1

Hey,
Can you please clarify from where are you executing your powershell?
In case if it from a local server, can you try accessing that Azure database from that local server ?
Because you might need to whitelist the IP of that server in firewall

Answer 2

Hey,
This is a common issue in case of Azure serverless :
https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview

As it takes sometime to become active.
So during the initial execution, it might be in paused state thereby causing the error.
Please try again as the code seems to work properly for normal azure sql satabase.

Answer 3

You were right the first time. It was a network issue. Cisco ACL blocking traffic to the net. My tests were from a Dev system (which I though were the same).

Thank you for reaching out. I really appreciate it.