question

BobbyMahaffey-9280 avatar image
0 Votes"
BobbyMahaffey-9280 asked BobbyMahaffey-9280 commented

Azure SQL Server and SQL managment studio

Hello,
I am trying to connect an Azure Resource-type SQL Server to my SQL Management studio.

In SQL Management studio i am using
type: database engine
name: .database.windows.net.1433 " plus my server name"
auth: SQL Server Auth
username: azure sql server name
password: azure sql server password

After trying to connect for about 20 sec. this pops up.
............................................................................................................
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) (.Net SqlClient Data Provider)
..............................................................................................................

Any help would be appreciated.

sql-server-generalazure-sql-database
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BobbyMahaffey-9280,

There are several possible reasons for the error:
Incorrect connection string>>please check your connection string

Named Pipes was not enabled on the SQL instance>> go to SQL Server Configuration Manager, see Server has NP enabled.

Remote connection was not enabled>>check out: when you right click on the Server in SSMS, in properties>Connections, the Remote server connections part, you have enabled the “Allow remote connections to this server” check box

Server not started, or point to not a real server in your connection string>>Go to the Control Panel>Search for services>Open local services window from your search results>Restart your MSSQLSERVER service

Here are the same problems you are experiencing:https://stackoverflow.com/questions/9945409/how-do-i-fix-the-error-named-pipes-provider-error-40-could-not-open-a-connec

1 Vote 1 ·

Named Pipes was not enabled on the SQL instance>> go to SQL Server Configuration Manager, see Server has NP enabled.

Remote connection was not enabled>>check out: when you right click on the Server in SSMS, in properties>Connections, the Remote server connections part, you have enabled the “Allow remote connections to this server” check box

Server not started, or point to not a real server in your connection string>>Go to the Control Panel>Search for services>Open local services window from your search results>Restart your MSSQLSERVER service

You don't seem to have read the question you are posting an answer. Or do you have any suggestions for how to start an Azure SQL Database from SQL Server Configuration Manager?

0 Votes 0 ·

Thank you for responding. Here are some of the answers to your proposed fixes. Something I did not mention and should have is in the Azure portal I created a server. Then in Azure DevOps I created a release pipeline that stores the connection string information. Using the Azure SQL DacpacTask Input the connection string information for SQL Management studio.

  1. In SQL Management Studio -> tools> there are no named pipes to enable. Even searching with find? function there are no items names np.

  2. The server is in Axure Portal, not SSMS but i did make sure the firewall rules has my ip installed.

  3. Lastly, i have been trying to establish this connection for a week with no success.

Thanks,
Bobby



0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered BobbyMahaffey-9280 commented

name: .database.windows.net.1433 " plus my server name"

Don't know exactly what this means, but if your server name is nisse, you should enter nisse.database.windows.net. No need to specify a port number.

On the Options tab, you should also specify the name of the database in your server you want to connect to, unless you want to land in master. You cannot change the database while you are connected.

If your database is serverless, keep in mind that it takes a while to start up the database, and it is perfectly normal to get this error on the first attempt, and maybe also on the second.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much for commenting and also address other replies.
Taking out the port number on the server name worked perfectly.

Thank you sooooo much,
Bobby

0 Votes 0 ·
Yufeishao-0810 avatar image
0 Votes"
Yufeishao-0810 answered

In SSMS, when you want to connect to your server, if a server listens on port 1433, to connect to a server from behind a corporate firewall, the firewall must have this port open. So we have to set firewall rule. It is important, you should check it.

There is a way to set firewall rule:https://www.c-sharpcorner.com/article/use-sql-server-management-studio-to-connect-and-query-an-azure-sql-database/

And there is a way to connect to Azure SQL database using SSMS:https://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-ssms#query-data

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.