Solved

Web.Config and MSSQL Database different than ASPNETDB

Posted on 2011-02-14
14
677 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
ID: 34893199
Can you post the error that "Security Tab" shows?
0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 34894224
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
ID: 34897536
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 15

Expert Comment

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

Author Comment

by:tim_chicagopartyanimals
ID: 34897783
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
ID: 34898013
<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
ID: 34898315
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
 
LVL 15

Accepted Solution

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

Author Comment

by:tim_chicagopartyanimals
ID: 34898597
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
ID: 34898761
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
ID: 34898931
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
ID: 34899914
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
ID: 34900120
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
ID: 34936441
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

776 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