Another - Cannot connect to Database Issue.

I've read several issues and resolutions related to my problem, but none of the resolutions have worked.

I have Visual Studio 2005 installed on my local machine.

My SQL Server 2005 is on a Member Server.

On a fresh Web application, I have no problem accessing the ASP.NET Configuration File to set up Users and Roles. The reason is obvious, the data is in SQLExpress and Local. You can see the database ASPNETDB.MDF residing under the App_Data Folder of the project.

How do I get this in the SQL Server 2005 System on the Member server?

Whenever I do the Copy Web Site procedure to post my application to the Web Server, I get an error stating that SQLExpress is not installed on the target server. That's correct because my target server has SQL 2005.

I've tried changing the Machine.Config. I've tried changing the Web.Config. Neither solution was successful. Both send me errors that no connection can be made.

I've also seen various recommendations concerning the ASPNET database. One camp says store your members and roles in the same database as your Data. The other camp says keep them seperate. Is there a rhyme or reason for either one? The data housed in this Database is what I'm after but how in the world to I get there?

I am on week #4 trying to get something going. I'm starting to feel like Thomas Edison when he said he discovered 900 different ways his experiment did not work.

Hi,

you will have to copy the database to the remote server, attach it on the (non SQL Server Express) system using the GUI or sp_attachdb. THen you will have to change the connectionstring not to use the UserInstance anymore (because you now will have the instance connected to a server instance rather than a user instance).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

OK. For my case, I think I found the solution.

Here is the situation:

SQL Server 2005 is on a member Server.

VS 2005 is on Windows XP Work Station.

Development project is on Windows XP Work Station.

When running ASP.NET Configuration, all Users, roles, etc. end up in the App_Data folder in the ASPNETDB database. After testing application, it is copied to Server but the ASPNETDB errors out because it is looking for SQLExpress and not SQL Server 2005.

Here is what I did to get around the issue:

1. Made sure the SQL Server DB had the ASPNETDB.

if it is not there,

run aspnet_regsql.exe

click next

Select Configure SQL server for applications

Click next

Add the server name and LEAVE the database as <default>

Click next

Click next

That will create the ASPNETDB.

2. From inside my application, used Server Explorer to Connect to the SQL ASPNETDB database.

3. Right click on the ASPNETDB, select properties, locate connection string, and copy connection string.

4. Inside my Web.Config file, I created a new connection string as such.

<appSettings/>

<connectionStrings>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=<myServer>;Initial Catalog=aspnetdb;Integrated Security=True;"/>

</connectionStrings>

5. I deleted the ASPNETDB from the app_data folder on my local drive.

Now when I run ASP.NET Configuration or my application, I'm posting data to the SQL Server and not the App_Data folder.

While looking for solutions, I ran across a couple of sites that recommended having users and roles inside the application database. Other recommended using the ASPNETDB database. I never did find a specific answer for one way or the other. All I know, what I have is now working and after 4 weeks of frustration, I'm going to stick with this for a while until someone can show me a better way.