IIS 7 can't connect to SQLServer 2008

Question

Sorry if this is the most seen question on the web, but this is my turn. I am trying to publish my asp.net mvc app on IIS 7 under MS Sql Server 2008. I am on a Windows Server 2008 virtual machine. I get the following classical error:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Under SQLServer, Allow remote connections is checked. My connection string is:

Data Source=.\MSSQLSERVER;Initial Catalog=mydbname;User Id=sa;Password=mypassword

I also tried with no username/password and "Integrated Security=true". There is only one instance of SQLServer installed. I tried to access my web page locally and remotely. There is no active firewall on the virtual machine.

Thx for your help.

Answer 1

Make sure you have TCP/IP set up as a transport in your SQL Server configuration tool.

Answer 2

is that the actual data source line from the web config?

If so then it's should be in quotes of course, for safety add a ; on the end and check that is the actual name of your instance, you can check windows services for your instance name.

try substituting localhost instead of . and can you connect from sql server management studio using the credentials in your connection string?

Answer 3

As mentioned by Robert, try:

ConnectionString="Data Source=(LOCAL)\MSSQLSERVER;Initial Catalog=mydbname;User ID=sa;Password=mypassword"

I'm not sure if connection strings are case sensitve, but I notice that you have 'Id' instead of 'ID'.

Edit: Am not sure if you need \MSSQLSERVER?

Answer 4

are you sure it's a named instance of SQL?

try

Data Source=.;Initial Catalog=mydbname;User Id=sa;Password=mypassword

UPDATE:

from this site

did you try the following:

• Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
• Make sure SQL Browser service is running on the server.
• If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

Answer 5

Thanks guys for the try. I found the solution and it is related to an info that I forgot to give. I hope it can help someone as new as me on these things.

I use NHibernate, and the connection string is actually in the nhibernate.cfg.xml file. The one in the web.config file is actually used by my various providers (users/roles). I fixed that by removing the connection string from the NH config file. I now retrieve it with:

string connectionString = ConfigurationManager.
ConnectionStrings["myConnectString"].ConnectionString;

and I set it in NH with:

Configuration cfg = new Configuration();
cfg.Configure(cfgFile);
cfg.SetProperty(NHibernate.Cfg.Environment.ConnectionString, connectionString);

Now I get:

Cannot open database "mydb" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

But this is another story, for another question if I can't find the answer.

PS: I had to use "." as the server name otherwise .\MSSQLSERVER was producing a new error "invalid connection string". Thx Ian and Jared for the tip.

Answer 6

Hello sir,

I am conneting with the sqlserver 2008 database. I have develop the application on the .net 3.5 framework. I got the problem with ii7 to upload the site and database connectivity error occurs. please give your comment as far as possible.

Nirav dave