本机的Sql Express .mdf上传到服务器后无法访问的情况。

[nanhe0065]

在本机上做了一个极简单的应用。添加新项：Sql数据库, 在App_Data目录下生成一个DataBase.mdf文件。在此mdf文件里建了一个表，输入了一些数据。然后在服务器资源管理器上直接拖拽到Form上生成GridView。在本机上一切正常。

等上传到服务器上，建好虚拟目录后再访问，提示：
在建立与服务器的连接时出错。在连接到 SQL Server 2005 时，在默认的设置下 SQL Server 不允许进行远程连接可能会导致此失败。 (provider: SQL 网络接口, error: 26 - 定位指定的服务器/实例时出错)
...
------
本机生成的web.config里的连接字符串：
<connectionStrings>
<add name="DatabaseConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>


以为这应该像是用Access的mdb文件一样轻松才对，结果好郁闷~

[namhyuk]

1、服务器上必须装有Sql Server Express。
2、Sql Server外围用应服务器->服务和连接的外围应用服务器->SQLEXPRESS->远程连接->选择：本地接连和远程连接，同时使用TCP/IP和named pipes.

[nanhe0065]

又出现状况了，提示：
Unable to open the physical file "C:\XXX\App_Data\Database.mdf". Operating system error 5: "5(拒绝访问。)".
An attempt to attach an auto-named database for file C:\XXX\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

痛苦啊~

[namhyuk]

Here is another interesting error message while trying to use the auto-attach feature of the SQL Server 2005:



An attempt to attach an auto-named database for file C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be Opened, or it is located on UNC share.



There are several reasons for the above error. In most cases, it has nothing to do with the last part of the error message - "A database with the same name exists, or specified file cannot be Opened, or it is located on UNC share".

Here is some work arounds to get rid of the above error message:

1. Open SQL Server Management Studio and attach the .mdf file manually to the SQL Server. (Select the 'Databases' node, Right click and select 'Attach')

Now change your web.config and change the connection string to point to the pre attached database instead of auto attach.

2. SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again.

3. The windows account "ASPNET" must have write permission on the folder. If it is a Windows 2003 machine, provide write access to the account "NETWORK SERVICE" on the folder, instead of account ASPNET.

4. You may want to disable the "User Instance=true" and try.

5. Are you using beta versions of VWD, Visual Studio 2005 or SQL Server 2005 Express? Uninstall them and install the released versions.

6. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode. (Select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select the node 'Security' to view/change the authentication mode)

7. Make sure your connection string is correct. It must be something like below:

"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"

When you use Integrated Secuiryt=SSPI in the connection string, make sure you are using <identity impersonate="true" /> in the <system.web> section of the web.config and also make sure to specify a valid windows account for the "Anonymous User" access section in IIS.

[nanhe0065]

照这么讲Sql Express的auto attach功能不好用啊。

我也是手动Attach后，再把web.config里的连接字符串改成
Data Source=.\SQLEXPRESS;Initial Catalog=***;Integrated Security=True
后才能进行访问。

折腾了一晚上！结果最后还是得手动附加.mdb，实在郁闷~

[nanhe0065]

应该是手动附加.mdf~

总之，郁闷的很。