SQL Server: cannot connect from local website (SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Question

I'm developing a website using Visual Studio 2008 and Sql Server 2008 Express. I entered into developing, so code is right as there are some coleagues of mine running it correctly with the same tools. The problem is: once I run the website I cannot make it comunicate with the database. They both are in my local machine. The error thrown is:

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)

The strange stuff is that I can navigate the db throught Visual Studio itself, via the Data Explorer panel. By doing that, I read the connection string from properties, and it looks like

Data Source=*MY_MACHINE*\SQLEXPRESS;Initial Catalog=*MY_DB*;Integrated Security=True

I also tried some change like

Data Source=.\SQLEXPRESS;Initial Catalog=*MY_DB*;Integrated Security=True

Data Source=localhost\SQLEXPRESS;Initial Catalog=*MY_DB*;Integrated Security=True

both with no results.

Thanks

Answer 1

The problem is the ASP.NET worker process does not have rights to access the database.

There are a number of ways to fix this, but the simplest is to give local group everyone rights to the database. Then you should be able to connect.

or

You could change the connection string to not use Integrated Security but give it a username and password

You should also see an appropriate error message in your event log (saying USER such and such was not allowed to access the database) then you can give rights to just that user.

When you are running as yourself you are connecting as yourself and you have rights. When IIs is running then it is running as the worker process and that user can't connect.