CODEWITHSUNDEEP
sql-serversql-server-2008
OneFineDay
OneFineDay

Reputation: 9024

Error while trying to connect to SQL Server - localhost

I have checked the instance name, auto close is set to true, allow remote connections on the server is checked. The server is running when I open the SQL Server configuration manager. I have even rebooted. I have created this db the same way as all others. I use Entity Framework and have checked the names in the web.config and they match. This is the default connection string from the wizard - I use for testing before I deploy. I just can't think of anything else to check to figure out why it won't connect. Working inside SQL Server everything is fine.

Here is the general 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: Shared >Memory Provider, error: 40 - Could not open a connection to SQL Server)

Config:

connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string='data source=.\SQL_1;attachdbfilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQL_1\MSSQL\DATA\A_db.mdf";integrated security=True;connect timeout=10;user instance=True;multipleactiveresultsets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />

Thanks in advance.

Upvotes: 1

Views: 6686

Answers (2)

theMayer
theMayer

Reputation: 16167

Let me preface this answer by saying that proper setup of a SQL instance is not as easy as Microsoft would like you to think with the entity framework. It's a little bit involved and requires that you put your DBA hat on for a little bit.

The error you have indicates that the web instance is attempting to connect to the SQL server using Windows Integrated Security. This will work fine if (a) the windows user that the process is running as (which can be configured in IIS) is authorized to log on to the SQL server and has a valid login in the database and (b) if the SQL server is on the same machine or in the same domain as the IIS server.

In light of this, I recommend using SQL Server authentication. If you need to know how to do this, I recommend searching for "SQL Server Authentication setup" - here is an article that I found which might help you set this up.

http://msdn.microsoft.com/en-us/library/aa337562.aspx

In general, I recommend taking the following actions:

  1. Connect to the sql server using MS SQL Management Studio.
  2. Permanently attach your database, then use the Initial Catalog property on your connection string rather than AttachDbFileName
  3. Then set up your login username and password on the SQL server, and create a login in the database for it.
  4. Make sure your login can only execute the stored procedures you want it to execute. Deny it access to running sql statements.

You will also need to add the username and password to your connection string, and set IntegratedSecurity=false.

Let us know how things go once you get your SQL server set up properly.

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56725

This error means that your provider code cannot find the SQL Server. If you have checked the server instance name (it should be <yourLocalServer>\SQL_1), then it could be the attachdbfilename= parameter, as this is a really unreliable way to specify the database to connect to (you should be using the Database Name, not the file name), because there are about a hundred reasons that the file name could change that have nothing to do with your application.

Upvotes: 0

Related Questions