Solved

Web.Config and MSSQL Database different than ASPNETDB

Posted on 2011-02-14
14
672 Views
Last Modified: 2012-05-11
Hi,

I am stumped for the moment trying to configure my connection string in my web.config file.  I am using a database that is different than the default aspnetdb that gets automatically created.  I created a database will all the user and membership tables and copied it into my websites App_Data folder.  The problem is when I add new users either programmatically or through the ASP.net Website Administration Tool, these get stored in the database originally created in SQL Server Management Tool (which resides somewhere under Program Files).   My original connection string (which I have commented out while working on the new one) is:

<!--<connectionStrings>
      <remove name="LocalSqlServer"/>
      <add name="LocalSqlServer"
            connectionString="Data Source=TIM-LAPTOP\SQL2008EXPRESS;Initial Catalog=ChicagoPartyAnimals;Integrated Security=True"                  
            providerName="System.Data.SqlClient"/>
    </connectionStrings>-->

My current string is:

<connectionStrings>
      <remove name="LocalSqlServer"/>
      <add name="LocalSqlServer"
            connectionString="Data Source=TIM-LAPTOP\SQL2008EXPRESS;AttachDbFilename=|DataDirectory|ChicagoPartyAnimals.mdf;Integrated Security=True;User Instance=true"
            providerName="System.Data.SqlClient"/>
    </connectionStrings>

My current string is causing an error in the Website Administration Tool when going to the Security Tab.

Any thoughts or guidance as to where I can find the solution?  All of my attempts at search have proved futile thus far.

Thank you.
0
Comment
  • 7
  • 6
14 Comments
 
LVL 15

Expert Comment

by:jorge_toriz
Comment Utility
Can you post the error that "Security Tab" shows?
0
 
LVL 13

Expert Comment

by:agarwalrahul
Comment Utility
you can by adding this to web.config file:

<configuration>
    <connectionStrings>
        <remove name="LocalSqlServer"/>
        <add name="LocalSqlServer"
             connectionString="Data Source=TIM-LAPTOP\SQL2008EXPRESS;AttachDbFilename=|DataDirectory|ChicagoPartyAnimals.mdf;Integrated Security=True;User Instance=true"
            providerName="System.Data.SqlClient"/>
    </connectionStrings>
   <system.web>
        <membership
             defaultProvider="SqlProvider"
             userIsOnlineTimeWindow="20">
             <providers>
                <remove name="AspNetSqlProvider" />
                <add name="SqlProvider"
                    type="System.Web.Security.SqlMembershipProvider"
                    connectionStringName="LocalSqlServer"
                    enablePasswordRetrieval="false"
                    enablePasswordReset="true"
                    requiresQuestionAndAnswer="true"
                    passwordFormat="Hashed"
                    applicationName="/" />
            </providers>
        </membership>
    </system.web>
</configuration>
0
 

Author Comment

by:tim_chicagopartyanimals
Comment Utility
The error I get from the Security tab is:

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: Cannot open user default database. Login failed. Login failed for user 'TIM-LAPTOP\Tim Jendro'.

After I had initially posted the question I had successfully gone in and created a user using the Website Adiminstration Tool.  I am not sure what changed, but now I am back to getting the error.

Thank you for your help.
0
 
LVL 15

Expert Comment

by:jorge_toriz
Comment Utility
Add TIM-LAPTOP\Tim Jendro as a sysadmin of your sqlexpress installation
0
 

Author Comment

by:tim_chicagopartyanimals
Comment Utility
From all appearances that is already the case.  I checked through both the sysadmin under Server Roles as well as on the Server Roles page under my user in the Login Properties in SQL Management Studio.

I am attaching my entire web.config text:

<configuration>
    <!--<connectionStrings>
      <remove name="LocalSqlServer"/>
      <add name="LocalSqlServer"
            connectionString="Data Source=TIM-LAPTOP\SQL2008EXPRESS;Initial Catalog=ChicagoPartyAnimals;Integrated Security=True"                  
            providerName="System.Data.SqlClient"/>
    </connectionStrings>-->
  <connectionStrings>
      <remove name="LocalSqlServer"/>
      <add name="LocalSqlServer"
            connectionString="Data Source=TIM-LAPTOP\SQL2008EXPRESS;AttachDbFilename=|DataDirectory|ChicagoPartyAnimals.mdf;Integrated Security=True;User Instance=true"
            providerName="System.Data.SqlClient"/>
    </connectionStrings>
    <system.webServer>
      <modules runAllManagedModulesForAllRequests="true">
          <add name="DomainServiceModule" preCondition="managedHandler" type="System.ServiceModel.DomainServices.Hosting.DomainServiceHttpModule, System.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </modules>
      <validation validateIntegratedModeConfiguration="false" />
    </system.webServer>
    <system.web>
      <httpModules>
        <add name="DomainServiceModule" type="System.ServiceModel.DomainServices.Hosting.DomainServiceHttpModule, System.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </httpModules>
      <compilation debug="true" strict="false" explicit="true" targetFramework="4.0"><assemblies><add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /></assemblies></compilation>
      <authentication mode="Forms" />
      <roleManager enabled="true" />
      <membership>
        <providers>
          <add connectionStringName="LocalSqlServer" enablePasswordRetrieval="false"
            enablePasswordReset="true" requiresQuestionAndAnswer="false"
            applicationName="ChicagoPartyAnimals" requiresUniqueEmail="true"
            passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7"
            minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"
            passwordStrengthRegularExpression="" name="ChicagoPartyAnimalsMembershipProvider"
            type="System.Web.Security.SqlMembershipProvider" />
        </providers>
      </membership>    
      <profile enabled="true">
        <properties>
          <add type="System.Int32" defaultValue="10" name="DefaultRows" />
        </properties>
      </profile>
    </system.web>
    <system.serviceModel>
      <serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />
    </system.serviceModel>
</configuration>

When I get this functioning ultimately I am looking to have users create a member with just username, email, and password.
0
 
LVL 15

Expert Comment

by:jorge_toriz
Comment Utility
<membership defaultProvider="ChicagoPartyAnimalsMembershipProvider">
        <providers>
          <add connectionStringName="LocalSqlServer" enablePasswordRetrieval="false"
            enablePasswordReset="true" requiresQuestionAndAnswer="false"
            applicationName="ChicagoPartyAnimals" requiresUniqueEmail="true"
            passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7"
            minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"
            passwordStrengthRegularExpression="" name="ChicagoPartyAnimalsMembershipProvider"
            type="System.Web.Security.SqlMembershipProvider" />
        </providers>
      </membership>
0
 

Author Comment

by:tim_chicagopartyanimals
Comment Utility
Thank you Jorge for your help.  I believe I am making progress, however something still is not quite right.  I added the defaultProvider and went into the ASP.net Website Administration Tool and it looked good.  Immediately it showed there were 0 users and I was able to manually go into the Security tab and create a user.  I then went back to my Visual Studio (2010) project and went to the Server Explorer to view the data in the table that was just added.  I was a little suprised to find there were two users in the table, not one.  I looked at the date modified timestamp on the mdf file in the app_data folder and it coincided with the time I added the user.  I had closed the Website Administration Tool and now when I go back in, I get the same error as before.  Nothing has changed in my web.config file inbetween adding the user and then attempting to go back in.  The other thing is I had initially created the database the SQL Server 2008 Management Studio, then copied the file mdf file from my C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008EXPRESS\MSSQL\DATA folder and placed it in my website App_Data folder under my Visual Studio project.  Perhaps this was not the correct way to go about it.  This is all new to me and I am sure there is something I am missing.  I really appreciate your help and advice on this issue I am having.

Thank you.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 15

Accepted Solution

by:
jorge_toriz earned 250 total points
Comment Utility
Can you delete your app_data database and then let ASP .NET creat it?
0
 

Author Comment

by:tim_chicagopartyanimals
Comment Utility
I deleted the app_data database and relaunched the project and the Website Administration Tool created a new one.  All seems good.  I think that somehow what was appearing in the Server Explorer in Visual Studio was the database under Program Files and since they were the same name it was causing an issue once I connected to view the table data?????  There is nothing appearing there now.

Since I am a rookie at this and want to avoid creating any of these issues again, what are your recommendations for creating the other tables I need?  Should I remove the first one I created and then attach the database ASP.net just created to SQL Server Management Studio?  Can they both be attached?  I have about 10 tables I need to get into the new database and want to do it with the least amount of pain as possible.  Your insight is appreciated.

Thanks again for your help.
0
 
LVL 15

Expert Comment

by:jorge_toriz
Comment Utility
You can use Server Explorer within Visual Studio to create your desired objects
0
 

Assisted Solution

by:tim_chicagopartyanimals
tim_chicagopartyanimals earned 0 total points
Comment Utility
I can consistently reproduce the behavior now.  I deleted the database in the app_data folder.  Started Visual Studio, went into the Website Administration Tool which creates a new database.  I can create some users, close it, open it, create more users, close it.  Everything is fine and all data is there.  As soon as I open the database in Visual Studio which shows the tables etc... in the Server Explorer, after that if I start the Website Administration Tool, I get the error.  I have to completely close Visual Studio and restart.  At that point I can then go back into the Website Administration Tool.  Now that I know this, I will limit when I go in there.  Typically I should not have to.  What is puzzling is why this is happening in the first place.
0
 

Author Comment

by:tim_chicagopartyanimals
Comment Utility
The real problem that I can now duplicate still exists however since I know how it is caused, I can work around it.
0
 
LVL 15

Expert Comment

by:jorge_toriz
Comment Utility
Well, I think that was not the best approach but it work... at last, that was the target
0
 

Author Closing Comment

by:tim_chicagopartyanimals
Comment Utility
The suggested step highlighted the fact that the issue that I thought I was facing was really a symptom of something else going on.  The real problem was not with my web.config file, but rather something related to viewing the database in Visual Studio then going into the ASP.net Website Administration Tool.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
The viewer will learn how to dynamically set the form action using jQuery.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now