WSAT & DB Access

PHoyt · February 13th, 2015, 01:34 PM

Hi there!

I am using a new Win 8.1 build, VS Express 2013 for Web and of course, ASP.NET 4.5.1. I also have SQL Server 2012 Express installed on this computer.

I am working through this book for the second time, after finishing a couple of other books on the same subject. This time I'm working to implement site security using the information in chapter 16.

I've been able to access the WSAT app but it won't connect to my database so I can add roles and assign users to roles.

Prior to this point I have successfully registered one user with the site that I am trying to set up. I can see the database files in my App_Data folder and the web.config file has a connection string that seems to be correct. I think is correct because I can go back to my site and login using the user name and PW that I used initially... so the database is being used.

Can anyone suggest things I might try to resolve this issue?

My config file actually has two connection strings. One for the user/roles database created by the system, with the db files within the web site folder structure. A second string points to my local SQL Server 2012 installation for a second database that contains all of the data for this web site.

Thanks,
Pete

Imar · February 14th, 2015, 03:46 PM

Hi there,

Can you describe what you mean with "but it won't connect to my database so I can add roles and assign users to roles."? Do you get an error?

If you can access the WSAT, try accessing the provider settings (shown in Figure 16-17 on page 615. What do you see there? Can you confirm everything is set up correctly?

Hope this helps,

Imar

PHoyt · February 15th, 2015, 09:45 AM

Hello Imar and thank you for your reply.

Looking back at my original post, I realize that I was not complete in describing my situation.

Today, I downloaded a fresh copy of the book's source files and opened the chapter 16 web site and went through the same steps to access the WSAT interface. Everything worked as described in the book.

My problem is when I try to start a new site. In my case, I started a new project and set up a simple Web Forms site with no content beyond all the default pages VS2013 includes.

By default it seems the web site is set up to use "forms" authentication and as soon as I run the site from VS it will generate the various db tables it needs. At this point in my simple test, I don't have a second db file to point to so I let it build a new db file and use it for security.

It is only when I try to access WSAT after this, that it is failing to connect with my db. I compared the new db tables with the db tables in the chapter 16 example and I see the same table names in my simple test site so it seems to be generating the same db structure.

I've included the current Web.config content below in case that suggests anything. I did some research yesterday and found a number of users complaining of the same problem and it seems to be a basic incompatibility between the old and the new security models. I remain confused by what I found but haven't given up!

The error message in WSAT reads:

"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."

No matter what I try in WSAT, it simply responds with this 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. "

The provider listed in WSAT is AspNetSqlProvider.

Thanks,
Pete

Code:

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-ProtectedTestSite01-20150214052636;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-ProtectedTestSite01-20150214052636.mdf"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5.1">
      <assemblies>
        <add assembly="System.Web.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
      </assemblies>
    </compilation>
    <httpRuntime targetFramework="4.5.1"/>
    <pages>
      <namespaces>
        <add namespace="System.Web.Optimization"/>
      </namespaces>
      <controls>
        <add assembly="Microsoft.AspNet.Web.Optimization.WebForms" namespace="Microsoft.AspNet.Web.Optimization.WebForms" tagPrefix="webopt"/>
      </controls>
    </pages>
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login" timeout="2880" defaultUrl="~/"/>
    </authentication>
    <profile defaultProvider="DefaultProfileProvider">
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/"/>
      </providers>
    </profile>
    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>
      </providers>
    </membership>
    <roleManager defaultProvider="DefaultRoleProvider">
      <providers>
        <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/"/>
      </providers>
    </roleManager>
    <!--
            If you are deploying to a cloud environment that has multiple web server instances,
            you should change session state mode from "InProc" to "Custom". In addition,
            change the connection string named "DefaultConnection" to connect to an instance
            of SQL Server (including SQL Azure and SQL  Compact) instead of to SQL Server Express.
      -->
    <sessionState mode="InProc" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection"/>
      </providers>
    </sessionState>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="DotNetOpenAuth.Core" publicKeyToken="2780ccd10d57b246"/>
        <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.1.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="DotNetOpenAuth.AspNet" publicKeyToken="2780ccd10d57b246"/>
        <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.1.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089"/>
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
    </providers>
  </entityFramework>
</configuration>

Imar · February 15th, 2015, 12:08 PM

Could this be a permissions issue? Two things to check:

1. Try logging in to the LocalDB instance from the connection string and see if you can access the database

2. Look at the permissions for the App_Data folder. Try granting Everyone full control temporarily. If it then works, it's a security permission.

Cheers,

Imar

PHoyt · February 15th, 2015, 04:47 PM

Hello,

I tried changing the permissions for the folder to give full permission to everyone. That didn't change the results.

I'm not sure how to log in to the LocalDB instance from the connection string to see if I can access the database. But now that I've built my own quick and dirty interface for adding roles and assigning users to roles, I'm not having any connection issues. I am accessing the same db the system set up automatically in the Account folder and I am using a SqlDataSource to make it work.

It was an interesting exercise to set up that simple interface. I've never worked with the GUID datatype before this.

By the sounds of it, the WSAT means of administering the access information isn't recommended by Microsoft any longer. Still, I would like to understand why I can't use it as the book describes.

Thanks,
Peter

Imar · February 16th, 2015, 12:14 PM

You can log in to the database using SQL Server Management Studio using (LocalDb)\v11.0 as the server you want to connect to.

>> By the sounds of it, the WSAT means of administering the access information isn't recommended by Microsoft any longer.

Yep, that seems to be the case.

>> Still, I would like to understand why I can't use it as the book describes.

I am out of ideas without seeing your site or setup. Sorry.

Cheers,

Imar

PHoyt · February 16th, 2015, 07:53 PM

Hi Imar,

No worries... I am happy with the security system as it is presently. I'm confident that I can replicate the system when I build my next content management system.

Perhaps by the time I am forced to use the newest security system Microsoft is pushing, there will be more information and examples around to work from. :-)

Thanks,
Peter