AspNetSqlProvider does not work in ASP.NET web site administration tool

Recently playing around the Membership feature of ASP.NET, and trying to follow the sample article on MSDN(“Walkthrough: Creating a Web Site with Membership and User Login“). But I got error message when I launch the ASP.net Web Site Administration Tool, which occurred on the Security tab as the below:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database.

So I went ahead to click the Provider tab, and then click the Test link of AspNetSqlProvider under the Provider section. But I got the following 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.

I understand that this error was caused by the database connection. First I had to make sure I have already installed the aspnetdb in my local MS SQL server. I closed the Web Site Administration Tool, and launched a DOS command prompt. Next I ran “aspnet_regsql” under the directory of “C:\Windows\Microsoft.net\Framework\v4.0.30309\” which launched a MS SQL connectionwizard. After I was done with that wizaard, I then verified in my MS SQL 2008 R2 server. I found there was a new database instance called “aspnetdb” created and have some tables like “dbo.aspnet_Applications”, “dbo.aspnet_Users” etc.

But when I re-launched the ASP.NET Web Site Administration Tool, I got the same error message as I showed above.

I concluded that it was still caused by the Connection String. But I could not find any connection string or membership provider information in my application’s web.config file. I then checked the machine.config file on my development computer, I found the connection string read like the below:

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

From the connection string above, I believe the asp.net is trying to look up membership information from the aspnetdb.mdf file under App_Data directory. But I could not find this file under the App_Data folder of my application. According to the Walk Through article on MSDN, the Web Site Administration Tool site should be able generate an aspnetdb.mdf for me if I finish the Security Wizard. But how can I run the Security Wizard at the first place?

Since I already created the aspnetdb already, and wanted to continue with the other steps on the Walk Through article, I decided to fix this connection string and move one.

Open the web.config file under the current application root folder, and put the following inside it:

<connectionStrings>
        <remove name="LocalSqlServer" />
        <add connectionString="data source=.;Initial Catalog=aspnetdb;Integrated Security=True;" name="LocalSqlServer" providerName="System.Data.SqlClient" />
    </connectionStrings>

Now when I test the aspnetsqlprovider on the same application’s Web Site Administration Tool, it ran successfully.

But I still could not have the aspnetdb.mdf file generated automatically. Maybe I need to play around more to get it created next time.

Share
Tags: ,

10 thoughts on “AspNetSqlProvider does not work in ASP.NET web site administration tool

  1. Tangela Bacha

    I’m really inspired along with your writing talents and also with the structure to your weblog. Is this a paid theme or did you modify it your self? Either way keep up the nice high quality writing, it’s rare to peer a nice blog like this one nowadays..

    [Reply]

  2. Pingback: "you can check here"

  3. Pingback: "check"

  4. YMLim

    Thank you so much for your insightful explanation. I faced the same problem then I got an inspiration from you. I studied the connection string of the AspnetSqlServer connection string configured in machine.config, which its connection string is still using .\SQLExpress (SQL Server Express edition) rather than LocalDB as changed in VS2012.

    Then I overwrite the connection string for LocalSqlServer as follows:

    Wola! Suddenly it just works for me! The aspnetdb is added in the App_Data folder!
    Thanks!!!

    [Reply]

  5. YMLim

    <connectionStrings>
    <remove name=”LocalSqlServer”>
    <add name=”LocalSqlServer” connectionString=”Data Source=(LocalDB)\v11.0;AttachedDBFilename=|DataDirectory|aspnetdb.mdf;Integrated Security=True” providerName=”System.Data.SqlClient”/>
    </connectionStrings>

    [Reply]

Leave a Reply

Your email address will not be published. Required fields are marked *


+ six = 7

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>