?
Solved

ASP.Net SQL Membership DB not getting created automatically

Posted on 2009-12-30
19
Medium Priority
?
606 Views
Last Modified: 2013-12-17
When I follow simple steps to create 1 user and 3 roles (Using the Website Administration Tool), after I'm done and I refresh the files showing in the Solution Explorer, an MDF database file should be automatically created in the App_Data folder, as it does in the following video (3:10), but not for me:
http://www.youtube.com/watch?v=vxxFhGF-Z7E&feature=video_response

In the website administration tool, I click on the tab 'Providers', then click on 'Select a single provider for all site management data', then click on 'Test' next to AspNetSqlProvider (the only thing listed) and I get an error message:

"Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider. "

So I did this (not sure if I even had to do it):

C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -E -S ROSE-W7\SQLEXPRESS -A all

Start adding the following features:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider
.....
Finished.

Please note I am a TOTAL NEWBIE to ASP.Net and SQL...  :{
I'm using Windows 7, VS2008,  Server 2005 Express.




Studio-Express-Object-Explorer.gif
Web.config
ScreenHunter-02-Dec.-30-14.13.gif
ScreenHunter-03-Dec.-30-14.17.gif
0
Comment
Question by:yaronusa
  • 13
  • 6
19 Comments
 
LVL 41

Expert Comment

by:guru_sami
ID: 26148259
No it won't show up under app_data when you ran aspnet_regsql.
So now to make use of that aspnetdb that you created....you will need to modify the connectionString in your web.confing.
Check these:
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
http://www.asp.net/learn/security/tutorial-04-vb.aspx
0
 

Author Comment

by:yaronusa
ID: 26148595
After adding a connection string (one wasn't there to begin with), still no MDF file shows up in the solution explorer, even though I see the aspnetdb.MDF in both the physical file system in the App_Data folder and in the MS SQL Server Management Studio Express.

<connectionStrings>
    <add name="AnotherServer"      
         connectionString="data source=ROSE-W7\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"      
         providerName="System.Data.SqlClient"/>
  </connectionStrings>

Also, going back to the Web Site Administration Tool, -> Providers ->Select a single provider for all site management data->'Test AspNetSqlProvider (the only thing listed) and I got the same error message I describe in the original post.

Any ideas?
0
 

Author Comment

by:yaronusa
ID: 26148671
I was under the impression that by modifying the Web Site Administration Tool, creating 1 user and 3 roles would automatically create the DB, and modify the Web.config file for me.

So I'm also not sure if there is some other underlying reason all this didn't go as it seemed it should, I don't know if this extra info helps you.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:yaronusa
ID: 26148715
OK, maybe this will help someone help me.

I started a new project in ASP.Net, and the first thing I did was go to the Web Site Administration Tool -> Providers. In that area, I clicked 'test' on any provider I could find, and I got the same error described in my first post.
0
 

Author Comment

by:yaronusa
ID: 26148844
I also tried enabling TCP/IP and named pipes. See image attached.

ScreenHunter-04-Dec.-30-15.58.gif
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 26149077
how about setting connectionString name to LocalSqlServer like below:
Note: the <clear /> tag added as well.

<connectionStrings>
<clear/>
   <add name="LocalSqlServer"      
        connectionString="data source=ROSE-W7\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"      
        providerName="System.Data.SqlClient"/>
 </connectionStrings>

Also did you make any other change so web.config ?
Make sure you have Authentication mode="Forms" in web.config.
0
 

Author Comment

by:yaronusa
ID: 26149506
I have done as you suggested, but the result is currently the same.

Attached is my Web.config file.

Web.config
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 26149575
hmm....sounds weird...your web.config looks fine.

I started a new website in VWD 2008. added this to my web.config....

 <connectionStrings>
        <clear/>
       <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
          </connectionStrings>


It is literaly default string from web.config.
Then Selected the Website Admin Configuration icon and selecting Forms Authentication(i.e. internet mode) I got the aspnetDB created under the app_data.

So may be not sure if it has to do something with sqlExpress.

There is another way....if you are not keen on having aspnetDB under appdata folder which is modifying your connectionString.
0
 

Author Comment

by:yaronusa
ID: 26151637
Sorry for the late reply, birthday dinner :) OK... I'm going to try some things over the next couple of hours.... let's see what happens... check back tomorrow for reply... thank you so much...
0
 

Author Comment

by:yaronusa
ID: 26152002
So here's a bit of an update:

I tried to programmatically open a connection to the aspnetdb and succeeded with this:
using System.Data;
using System.Data.SqlClient;
SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = @"Data Source=.\SQLEXPRESS;" +
                    "Initial Catalog=aspnetdb;Integrated Security=SSPI";

Then I modified the Web.config connection to this:
<connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer"
        connectionString="Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=aspnetdb;"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Now, in the Web Site Administrator Tool upon clicking Providers and testing the connection with either the role or user provider, it succeeded. This time, upon adding a user and roles, the database did in fact populate (unlike before).

Even with the above progress, I STILL do not have the aspnetdb showing under the App_Data folder. If I try to manually add an existing item (the aspnetdb.mdf) to that folder in the Solution Explorer, it adds it, and you see it with an exclamation, and you can't access it.  However, with the Server Explorer (CTRL + S) I can connect to an MDF file, and see the contents of the aspnetdb.

I'm starting to think, perhaps wrongly, but maybe there is some reason that asp.net cannot "reach" the aspnetdb.mdf file. I say this because when I go into MS SQL Server Management Studio Express, and right click on Databases -> Attach New, when I scroll through the tree view of folders I am never allowed to any deeper than my user1 account to get to this physical address (see attached image):
C:\Users\user1\Documents\Visual Studio 2008\Projects\TryMembership3\TryMembership3\App_Data
The way I attached it was using aspnet_regsql.exe -E -S ROSE-W7\SQLEXPRESS -A all. I supposed I could have used: AttachDBFilename=|DataDirectory|aspnetdb.mdf; in the connectionString of the Web.config.

In any case, it's 1:20am, and I still cannot see the aspnetdb.MDF file in the solutions explorer... any ideas?
0
 

Author Comment

by:yaronusa
ID: 26152009
I forgot to attach the image from the MS sQL Server Management Studio Express where I can't dig deeper than user1 folder to manually attach aspnetdb.mdf


ScreenHunter-05-Dec.-31-01.12.gif
0
 

Author Comment

by:yaronusa
ID: 26153564
Actually, being able to drill-down past user1 folder is not a problem, seems to be normal behavior. Does anyone have any idea why I am having a hard time having a Database automatically added to my App_Data folder?

0
 
LVL 41

Assisted Solution

by:guru_sami
guru_sami earned 2000 total points
ID: 26154230
As said in my first post...when you explicitly run aspnet_regsql.exe and set your connectionString in web.config as you did in #26152002, WebAdminTool will not create aspnetDb for you anymore under App_data folder.
I am not sure why you want to create or add that under app_data folder.
That is done by Default SqlExpress Provider in co-ordination with VS.
Check the links I provided earlier. The first link explains a bit about it under " Default Sql Express Providers" heading.
0
 

Author Comment

by:yaronusa
ID: 26161787
Hope your New Year's night and day were fantastic... :) Here's to a great 2010!

OK... back to the real world... :/

OK, I now understand what you said: since I want the database to be in the local App_Data folder, I should not be running aspnet_regsql.exe and have that connectionString that I do. Although it was the only way I could actually successfully test my provider in the Web Site Administration Tool. I got that from that first link you gave me, and I read all of it as well.

So I deleted the aspnetdb. As far as I'm concerned, if I used the Web Site Administration Tool and I create new roles and new users for the first time, the config file should automatically change to reflect that, and the App_Data folder should have it. But that just doesn't happen.

So I modified the Web.config file as you had suggested to:



   
 

I also tried with the .\SQLEXPRESS

And then accessed the Web Site Administration Tool and still nothing added to the App_Data folder in the Solution Explorer. However, and this may be important, a database called aspnetdb.mdf and aspnetdb_log.ldf are created in the projects App_Data folder. If I try to manually add the local aspnetdb.mdf to teh App_Data folder, it is added with an exclamation warning, and I can't do anything with it. If I double click it, an error message popup says that the aspnetdb_log.ldf cannot be found or has been deleted. So I go to check, and it now no longer appears.

I have repeated the above steps at least 4 different times, and every time I manually add the local aspnetdb.mdf to the project, the exclamation appears, and double-clicking it in the Solution Explorer makes the aspnetdb_log.ldf disappear.

However, from the Server Explorer (CTRL + ALT + S) I can see the local aspnetdb.mdf and connect to it.

So........... any ideas?



0
 

Author Comment

by:yaronusa
ID: 26161805
One new note: every time I access the local aspnetdb.mdf through the Server Explorer in VS2008, the aspnetdb_log.ldf get created and I see it in the Solution Explorer. However, as usual, if I double click the db I manually added to the App_Data folder in solution explorer (with the exclamation), the aspnetdb_log.ldf disappears and the error message says it can't find the aspnetdb_log.ldf...

Oy Vey....
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 26162038
"if I used the Web Site Administration Tool and I create new roles and new users for the first time, the config file should automatically change to reflect that, and the App_Data folder should have it. But that just doesn't happen"

Ok so were you able to create new user and role?
Did you try using that user credentials to login and does it work?
Also did you try refreshing the App_Data folder to see if aspnetdb is being added?
When you say manually adding aspnetdb...can you tell how are you doing that?
0
 

Accepted Solution

by:
yaronusa earned 0 total points
ID: 26162197
Using the Web Site Administration Tool, I can create roles and users. Using the Server Explorer, I can actually view the those roles and users. What I couldn't do, was have the database added to the App_Data folder, refreshing did not work, and neither did manually right-clicking on the App_Data folder in Solution Explorer, choosing Add Exising Item, then adding the aspnetdb.mdf file that was already physically in the project's App_Data folder.

BUT I FOUND THE SOLUTION (with your assistance, of course, couldn't do it without you):

My project is an ASP.Net Application Project. I started thinking, maybe that has something to do with it. So this time I opened up a new instance of Visual Studio 2008, then I pointed to File->New->Website...

Then I clicked on the Web Site Administration Tool added a couple of roles and users, refreshed the Solution Explorer, and viola, aspnetdb.mdf not only created, BUT CORRECTLY added to the App_Data folder.

The Web.config has no connection string listed, but after everything we went through, I have no doubt I can easily add it within minutes of trial and error.

So it seems there is a difference between an ASP.Net Application project and a ASP.Net Website. What is the difference.... I have no clue, but I attached the Web.config file from this project, and I'm going to try to compare it to the previous Web.config file I listed in 26149506 above.
web.config
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 26162287
ok thats why i couldn't replicate. I was using Website project and was able to see with all default settings.
Good finally you figured it out.
0
 

Author Comment

by:yaronusa
ID: 26162340
OK, I moved to close this question with my comment as the solution and 500 pts awarded to guru_sami for crucial assistance.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question