Using MSSQL 2000 with .net2 (Ditching SQL express 2005)

[adam]

I thought I would share this with you all

Amongst the dll and scary files installed with .net2 is a file called aspnet_regsql.exe

This will set up tables and views in your database so it can be used to work with membership roles and the like in your .net2 application.

C:\WINDOWS\Microsoft.NET\Framework\<versionNumber> \aspnet_regsql.exe -W

the -W option will bring up a wizard making it easier to set up.

Finally you need to set up your web.config file to work with your database:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <appSettings/>

  <connectionStrings>
    <add name="SkySouthDB" connectionString="Data Source=mssql.active-ns.com;Initial Catalog=skysouth;Persist Security Info=True;User ID=*****;Password=*****"
        providerName="System.Data.SqlClient" />
  </connectionStrings>

  <system.web>
    <roleManager enabled="true" defaultProvider="CustomizedProvider">
      <providers>
        <add connectionStringName="SkySouthDB" name="CustomizedProvider"
          type="System.Web.Security.SqlRoleProvider" />
      </providers>
    </roleManager>


    <membership defaultProvider="CustomizedProvider">
      <providers>
        <add name="CustomizedProvider"
             type="System.Web.Security.SqlMembershipProvider"
             connectionStringName="SkySouthDB"
             minRequiredPasswordLength="5"
             minRequiredNonalphanumericCharacters="0" />
      </providers>
    </membership>

  </system.web>
</configuration>

See: http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx

[WhiskyFudge]

The same method but with different options can also be used for personalisation and the other things (can't think of them in the top of my head) where asp.net2 is good enough to create database elements for you.

[GalaxyTech]

I have recently had a problem when using Users and Roles in my ASP.NET 2.0 application with the Login control. I used the aspnet_regsql.exe application to populate my SQL Server 2000 database on the catalyst servers with the tables necessary to use the membership API. I then configured my application's web.config to use this database for membership and roles similar to the way mentioned by Adam above.

I then used the ASP.NET 2.0 Website Administration tool to add users and roles to the database.

When I added a login control to my website I tested it locally and everything was working great. The website running on my local machine was connecting up to the SQL Server database on the catalyst servers fine and using the users and roles contained within successfully.

However, when I loaded my website up to the catalyst servers and called the login page it failed to authenticate my username!

I eventually found a blog by ASP.NET 2.0 guru Scott Guthrie entitled "Always set the 'applicationName' property when configuring ASP.NET 2.0 Membership and other Providers".
http://weblogs.asp.net/scottgu/archi...Providers.aspx

Adding the applicationName attribute to my custom membership and role provider fixed the problem. My website authentication now works when run locally and remotely on the catalyst server.

Just thought I would pass this on to save anyone else wasting time with it!

[WhiskyFudge]

Thank-you for that GalaxyTech. For any asp.net dev's out there I would strongly recommend reading Scott G's blog. He's an very inteligent guy who is in charge of pretty much everything webby MS do (asp.net, atlas, IIS, etc)