CODEWITHSUNDEEP
asp.netsqlsql-server
nezdet
nezdet

Reputation: 51

Could not open a connection to SQL Server

I have problems connecting to my database server. The database server is not local, I am connected via its IP address.

It works fine in my development machine. After publishing the website to my server, it can not connect to my database server.

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)

Why can't my publish server connect to the database server, whereas it works fine through the development machine?

Upvotes: 5

Views: 4047

Answers (6)

Hoy Cheung
Hoy Cheung

Reputation: 1670

I have spent hours trying to connect to SQL server using sqlcmd. I disabled my firewall, checked all ip listed in "Protocols for SQLEXPRESS", edited my hosts file. I tried using different ips and machinename to connect to the server. But none of work worked. After hours of investigation, I found out that I made absolutely stupid blunder making me unable to connect. I want to remind people that the connection string is not case sensitive. But the option is!! what i did is I put

sqlcmd -s .\sqlserver

But the correct string is

sqlcmd -S .\sqlserver so watch out, people

Upvotes: 0

KeithS
KeithS

Reputation: 71565

I will agree that this sounds like a network issue and not a security issue.

Remote into the web server and ping the DB server by IP address. If this does not work, your server cannot see the DB server via that address (different subnet, incorrect firewall/proxy setup, etc). There may be a proxy address you must use to get to the DB server from the web server, or your web server may also be the gateway and IIS doesn't know to look for the DB server on the LAN. If it does work, the computers may not be talking on the same port, or the firewall may be blocking that port exiting the web server.

Upvotes: 0

Dindar
Dindar

Reputation: 3235

Is it your first time you publish your website on the web server ? if it is , Are you sure you have set your connection string properly ?

You need to create a login ( of curse a password for that too ) and the IP Address of that SQL on the web server .

So you need IP Address of the sql server host , Database Name , UId , Pwd .

Upvotes: 0

BitKFu
BitKFu

Reputation: 3697

I often had that problem. Mostly it's because of two problems.

  1. Open the SQL Server Configuration Manager.

    • Check if the SQL Server Network Configuration supports TCP/IP, if it's disabled, enable it. e.g. SqlServer 2005 Network Configuration, Protocols for SQLEXPRESS

  2. Open the SQL Server Management Studio

    • Click on the Sql Server Properties (right click on the server name and select properties).
    • After that, select the page "Security" and switch the check to "SQL Server and Windows Authentication mode".

That's all.

Upvotes: 4

p.campbell
p.campbell

Reputation: 100567

It sounds like your server can't make the network connection, rather than a security issue.

  • Ensure that any firewalls on both the DB and app servers allow traffic on the port (1433).
  • Ensure you're able to ping or tracert from both machines.

Upvotes: 1

Josh
Josh

Reputation: 44906

I'll attempt to go two for two on the psychic debugging for today...

I will assume that you are not using integrated security? If so it might explain things as the account on your local machine probably has permissions, but the SYSTEM account that is running on the server does not. Just a shot in the dark though.

Upvotes: 1

Related Questions