数据库连接错误： The provider did not return a ProviderManifestToken string.

这两天在学习Asp.mvc, 学习到了Model这一章节了，使用EF进行数据库连接操作，仿照例子，修改了一下字符串，然后修改在Web.config文件中，大体如下：

  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
    <add name="MovieDbContext"
     connectionString="Data Source =.\tt; Initial Catalog =movies; Integrated Security =SSPI;"
     providerName="System.Data.SqlClient" />
  </connectionStrings>

最开始的时候一看还有“ApplicationServices”， 心想我没有用到这个字符串，估计是作者把后面几张的内容给提前贴出来了，所以就删除了，只剩下了”MovieDbContext”这个连接字符串，结果一运行报了如下的错误：

The provider did not return a ProviderManifestToken string

想也没有想，网上搜了一下，看到StackOverFlow上有人再问，仔细看了一下没有什么明确的答案。

回过来看Inner Exception :'”{"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)"}”

网上google了一把，找到一个解决方案，一看，的确是我的SQL Serve  Browser没有启动。

http://blog.csdn.net/yefengmeander/article/details/6447907

I've resolved the issue. It was due to the SQL browser service.

Solution to such problem is one among below -

• Check the spelling of the SQL Server instance name that is specified in the connection string.

• Use the SQL Server Surface Area Configuration tool to enable SQL Server to accept remote connections over the TCP or named pipes protocols. For more information about the SQL Server Surface Area Configuration Tool, see Surface Area Configuration for Services and Connections.

• Make sure that you have configured the firewall on the server instance of SQL Server to open ports for SQL Server and the SQL Server Browser port (UDP 1434).

• Make sure that the SQL Server Browser service is started on the server.

link - http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1

 

重新启动了Sql Server Browser后，发现仍然不行，没辙，准备继续看看是不是端口呀，防火墙之类的问题。

实在不想跟EF的代码，何况还不知道有没有源代码呢， 不过还是沉下心来，看了一下相关的局部变量：看到了DBContext的连接字符串如下：

data source=.\SQLExpress;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

心想，我明明配置的是.\tt， 怎么还是这个呢？

后来在MSDN社区中看到有一个同学说有同样的问题，所有的SQL Server相关的问题都排查掉了，还是没有解决掉，后来一看原来是SQL Server的身份认证的问题，也就是“ApplicationServices”， 字符串的问题。

想起来删除的ApplicationServices字符串，在web.config中看了一下，果然有用到这个字符串的地方，赶紧加上，果不其然就好了。

注: 后来复现了以下这个问题，发现把applicationService字符串删除掉了也不会出错，不知道到是为什么。

启示：这个问题其实比较简单的，而自己解决这个问题却花了一两个小时，事后感觉自己太懒惰了。

• 上来之后不仔细查看错误的原因，直接google, 太懒了。
• Exception一般都是层层包裹了的，一般都是需要看一下inner Exception才能搞定，所以应该先看一下innerException.
• 看变量相关的值最合适，通过inner Exception中已经了解到了是Connecton的问题，此时应该先看一下传到服务器端的ConnectionString是不是正确才对，太懒了。

另外一个解决SQL 连接的问题：

http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/

SQL Server 2005 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) ”

Things to check:
1. Make sure your database engine is configured to accept remote connections
• Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
• Click on Surface Area Configuration for Services and Connections
• Select the instance that is having a problem > Database Engine > Remote Connections
• Enable local and remote connections
• Restart instance

2. Check the SQL Server service account
• If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
• Usually the format needed to specify the database server is machinename\instancename
• Check your connection string as well

<connectionStrings>

<add name=”SampleConnectionString” connectionString=”Data Source=machinename\instancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName=”System.Data.SqlClient”/>

</connectionStrings>

4.You may need to create an exception on the firewall for the SQL Server instance and port you are using
• Start > Run > Firewall.cpl
• Click on exceptions tab
• Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn), and port (default is 1433)
• Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

7. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
• Start > Run > cmd
•netstat -ano| findstr 1433
•telnet myserver 1433
•ping -a myserver

Check what ports are IP addresses are being returned.

Alternative:
If you still can’t get any connection, you may want to create a SQL account on the server, a corresponding SQL user on the database in question, and just use this username/password combo in your web application.