I have had a local Website up and running based on the MS Personal Website Starter Kit using Visual Studio 2005 Professional and SQL Server Express.
I am now trying to get the databases to run on SQL Server 2005 Standard Edition but can't get the connection to work.
I have changed the connection string in the Web.Config file from:
connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient"
To
connectionString="Data Source=.\scastle;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient"
But am getting the following error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Source Error:
Line 5: Sub Application_Start(ByVal sender As [Object], ByVal e As EventArgs)Line 6: AddHandler SiteMap.SiteMapResolve, AddressOf Me.AppendQueryString
Line 7: If (Roles.RoleExists("Administrators") = False) Then
Line 8: Roles.CreateRole("Administrators")
Line 9: End If
I am able to connect OK in both Server Management Studio (Server Name: SCastle, Windons Authentication) and clicking Connect to Database in Server Manager in Visual Studio (Server Name: (local)).
Have I got the connection string wrong or do I need to set up additional permissions in the database?
Thanks for your help.
Stephen
Hi,
the user instance feature only work with the Sql Server Express Edition. If you want to use the database with the Standard edition you will have to attach the database to the Server instance (to make it server controlled) and then change the connectionstring to the following (assuming that the local server holds an instance of the name scastle)
connectionString="Data Source=.\scastle;Integrated Security=True;Initial Catalog=YourDBnameafterattachment providerName="System.Data.SqlClient"
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Jens
Thanks for the response. I have tried adding the following in line with your advice but still get the same error:
<connectionStrings>
<add name="Personal" connectionString="Data Source=.\scastle;Integrated Security=True;Initial Catalog=Personal" providerName="System.Data.SqlClient"/>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.\scastle;Integrated Security=True; Initial Catalog=ASPNetDB"/>
</connectionStrings>
Any idea what is wrong?
Thanks
Stephen
|||You should then also check of you enbled remote connections (see the screencast on my site for more information)Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Thanks again Jens.
Problem has been solved by changing Data Source from ".\scastle" to "(local)".
Stephen
