Share This!

Friday, June 27, 2008

Choosing Another Database for Membership and Roles


By default, Visual Studio Express likes to store all its membership and roles data in a local user instance provider called AspNetSqlProvider that uses a database called ASPNETDB.MDF in your APP_DATA directory in your project. This type of database is called a user instance.
I think it would be beneficial to expose some terms here.
  • provider: this is an asp.net object that supplies information to the website software. There can be numerous providers in an application and a provider doesn't necessarily connect to a database. It could just as easily connect to an XML file, a text file, a random number generator or a golden retriever...
  • membership: is the information about members, i.e. login, password, email address, etc.
  • roles: This data can be kept separate from the membership data and describes what roles are assigned to each user (admin, guest, moderator, whatever) It also contains information about what roles have access to which features in your site.
  • user instance: This is a database stored in your APP_DATA directory. There are severe performance issues with this type of database, and many ASP hosts do not support user instances.
  • MSSQL Express: This is the free version of Microsoft SQL Server Database (MSSQL). even though a user instance database is this type of a database, in this article MSSQL Express database refers to a database created with SQL Server Management Studio Express tools. These databases are not a part of your website, but can be accessed by one or more websites. The data does not exist in your website folders and must be published separately.
  • MSSQL: Microsoft SQL Server is the full paid version of SQL server (the one you'll probably use on your live website if you are a corporate developer, or you're using a professional ASP.net web hosting service.
Ok. So you (like me) want to stop Visual Web Developer from using the user instance, and tell it to use a MSSQL Express database, and you do not want to write your own provider objects. Here is the step by step plan to do this.

Create a New Database

This assumes that you are not using an already existing database... if you already have a MSSQL Express database you can skip this step. Just open SQL Server Management Studio Express (download it if you don't already have it) and create an empty database.

Add the Membership and Roles Tables

There is a special program you can use for this, but you must learn the secret handshake. This program is part of the .NET framework and resides in your .net folders. The program name is Aspnet_regsql.exe and I suggest you find it by browsing to C:\WINDOWS\Microsoft.NET\Framework and doing a search for Aspnet_regsql.exe. On my system I find one copy of it in the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder (strangely not in the v3.0 or v3.5 folders).
Launch the program.
Click Next.
Select Configure SQL... and click Next.

The Server is the Windows name of your computer, but WAIT, don't pop open that Database list or you'll get the dreaded...
Connection failed
Failed to query a list of database names from the SQL server. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
...error.
This is not Microsoft's finest work, and this is where you must know the secret handshake I mentioned earlier. You must alter your Server Name to make this tool recognize that we're using SQLExpress. Add \SQLEXPRESS to the end of your computer name. Mine reads THEDOCTOR\SQLEXPRESS.  It also worked to use .\SQLEXPRESS.  Note also that if you named your SQL server something else when you installed it, you need to use that name.  The pop-down list will be enabled to open and there will be a list of all the databases on your machine.
Databases can be shared across machines too - in case your database isn't on the same machine that you develop on.

Select your new database and click Next.
Review and if all is well, click Next again.
Click Finish.

The database is ready for you to create membership and roles info. Now all we have to do is...

Tell web.config about the new database.

There are 3 steps for this.
  1. create a connection string.
  2. hook up the Membership provider.
  3. hook up the Roles Manager.
Here are the sections of my web.config that worked.

1. The Connection String

<connectionStrings>
<add name="MYDATABASE" connectionString="Data Source=THEDOCTOR\SQLEXPRESS;Initial Catalog=AFIDatabase;Integrated Security=True" providerName=".NET Framework Data Provider for SQL Server" />
</connectionStrings>
The bold text should be changed to reflect your database and server names. The name property (in green) will be needed to identify the connectionstring to the providers.

2. The Membership Provider

<membership defaultProvider="MyProvider" userIsOnlineTimeWindow="30">
<providers>
<clear/>
<add connectionStringName="MYDATABASE" applicationName="/" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="4" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" name="MyProvider" type="System.Web.Security.SqlMembershipProvider" />
</providers>
</membership>
The clear removes any references to the default databases from machine.config.
Note that the Membership object contains the definition of the provider which contains a reference to the connection string. The applicationName property is used to identify the application - you can share this name across many websites to allow a single signon for many sites.
So the membership object uses the MyProvider provider which uses the MyDatabase database.
( Membership object -> MyProvider -> MyDatabase )

3. the Roles Manager

<roleManager enabled="true" defaultProvider="MyProvider">
<providers>
<clear/>
<add connectionStringName="MYDATABASE" applicationName="/" name="MyProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</roleManager>
Note that even though I named the provider MyProvider again, it is in fact a different object. However, this allows me to use the Single Provider setting in the website settings page in Visual Web Developer.
Now if you open the ASP.NET Configuration page from Visual Web Developer (Website - ASP.NET Configuration) and click the "Providers" tab, then the "Single Provider" option, you should see the new provider you just created, and it should already be connected.
If the configuration page crashes for any reason, there are a few things to try.
  • ensure that the names of your providers match the "defaultProvider" property exactly.
  • ensure that you have used the same applicationName everywhere.
  • ensure that the connectionStringName in your providers exactly match the name in your connectionStrings section.

For Further Reading

Many thanks to Rob Mills and Guru Bhai for their help figuring this out.

Tuesday, June 17, 2008

Using A Different Database as a Membership Provider in ASP.NET

There should be no need for me to ask this but after 2 days of ditzing around trying to make this work I am at the end of my patience. Linux is looking better all the time.

Ok, I created a website with security and it works great on my desktop, the performance sucks (utterly) on the test webserver, and I cannot use it at all on the production server. That's because we must use real MSSQL in production rather than a user instance, the production environment does not support user instances.

All I need is to tell my membership, role, and security objects, "Look over here, not over there". One would think that altering a connectionstring and replicating the membership tables would be all thats needed.

But NO.

Plus, I can't seem to find a comprehensive tutorial on this anywhere, so I'm mixing info from blogs and msdn and whatnot trying to make this work. Most of the tutorials out there are for MSSQL2000 (I'm using 2005) and a mix of ASP.NET 2.0 and 3.5 (Im using 3.5 - I think)

Here is what I want.

On my workstation, I want the authentication to look in my local MSSQL Express database for ALL security info.

In Production, I want the web server to look in the FULL MSSQL database for this info.

I do NOT NOT NOT want to re-engineer membership and roles objects.

Here's what I have found so far.

http://msdn.microsoft.com/en-us/library/sx3h274z.aspx - generic - no specifics

http://msdn.microsoft.com/en-us/library/6e9y4s5t.aspx - this adds my new database to the MEMBERSHIP selection but not the Single Provider or Role Provider

http://msdn.microsoft.com/en-us/library/2fx93s7w.aspx - This helps you create the table structure in your database, but leaves out 1 critical piece of info... the fact that I had to type "/sqlexpress" after the server name or it crashes when you try to drop down the list.

http://forums.asp.net/p/980214/2369556.aspx - This is where I found out you have to type "/sqlexpress"

http://msdn.microsoft.com/en-us/library/ms998317.aspx - not sure if "Forms Authentication" is what I'm doing or not...

Friday, June 6, 2008

Extreme Frustration with XML

Ok, given the following XML...
<?xml version="1.0" encoding="utf-8" ?>
<queries>
    <query name="MatchUser1">
        <sql>
            SELECT     st_abbr AS state, lic_number AS licensenumber, lname AS lastname, fname AS firstname, zip
            FROM         GovtRegistry
            WHERE     (st_abbr = @STATE) AND (lic_number = @LICENSE) AND (fname = @FIRSTNAME) AND (lname = @LASTNAME) AND (zip LIKE @ZIP)
        </sql>
    </query>
    <query name="MatchUser2">
        <sql>
            SELECT     st_abbr AS state, lic_number AS licensenumber, lname AS lastname, fname AS firstname, zip
            FROM         GovtRegistry
            WHERE     (st_abbr = @STATE) AND (lic_number = @LICENSE)
        </sql>
    </query>
</queries>

How do I do this?

  XmlDocument document = new XmlDocument();
  document.Load("SomeFile.XML");
  XmlNode myQuery=document.find("MatchUser1");

  //this should give the text from the "sql" field of MatchUser1.
  string SQL = myQuery["sql"].value;  

What am I missing here? All I can find online is how to manually walk from node to node, testing the types of the nodes. The whole point of XML used to be that it was simple to organize, store, and retrieve data!

Azeez wrote:
Hi Bryan,
I came across your  resume and was interested in speaking to you in regards to an 
opportunity with  one of my clients. Iam interested in reviewing your resume and 
having a  conversation to see if we can potentially work together. I would appreciate
if  you can send me a word version of your most updated resume and a number to reach 
you by during the day.

Job Title:     Web UI  Developer
Location:     Ann Arbor,  MI
Duration:     6 Months contract 2 hire
Job openings:
One opening for network programmer focusing on User  Interfaces (UI) for Network 
switches.
User interfaces include WEB UI, CLI,  and SNMP.
The applicant must have experience in embedded system WEB  UI.
The GateD user interface programmer must have a strong desire to work  with network 
protocols and user interfaces. 

Education Requirements are: 
Bachelors in Computer Science or  Computer Engineering with and 2-5 years of 
Experience, or Masters in Computers  Science and 1-3 years of experience.

Applicants for the job should have the following skills: 
Strong C  skills,
PERL, Python, TCL, Shell Scripting,
Experience with Apache Web  server
Be familiar with Cisco’s Command Line interface,
Strong skills in  operating systems and experience with embedded operating systems,
Have a  strong theoretical background in network software with 3+
upper classes in  networking, routing protocols,
Have strong background and experience in  creating user interfaces, 
Applicant should have a strong background  in: TCP/IP,  Routing protocols (RIP, 
RIPng, 
OSPF (v2/v3), BGP, IS-IS, PIM,  MSDP), Switching protocols (STP, RSTP, MSTP, 
802.1aq, TRILL), Wireless protocols  (802.11, military radios),
Strong background in 802.11 Wireless devices  (CAPWAP),
Experience writing Network Management protocols (SNMP, XML, Agent  X, SMUX, etc.),
Experience with Web servers (Apache),
Experience with the  Linux operating system,
Experience with embedded operating systems, 
Strong theoretical background in compilers and debugging tools, 
Experience with routers (cisco, juniper, 3com, extreme), and 10G switches  
(Force-10),
Experience with wireless controllers (Cisco, Trapeze),   and
Theoretical and practical experience creating network test  automation

Personal requirements:
The GateD project works in a high connected team  with multi-site development.
Individuals applying must be self-pace,  self-learning, and have experience working 
with teams.
Individuals must have  strong verbal skills with an ability to quickly come to 
resolution of  inter-personal and technical issues.

All groups with the GateD project operate on a team-approach.
The  project requires strong software discipline in software process (specification, 
 coding, and test).
The project is looking for team members who want to  advance their skill set and 
become industry leaders in routing.

Thanks & Regards,
Azeez Khan | Azeez@catamerica.com | Azeez.cat@gmail.com

Sr.Technical Recruiter | CAT Technology Inc.
"Committed to Human  Excellence Through IT"
Hasbrouck Heights, New Jersey. 

Office: (201) 255-0319 Ext : 279 | Fax: (201) 727-9296
www.catamerica.com

http://www.catamerica.com

Azeez: I have highlighted the skills I have in green, and those I lack in red. If you had really seen my info online, you would have already known this.

  Applicants for the job should have the following skills: 
 Strong C  skills,
 PERL, Python, TCL, Shell Scripting,
 Experience with Apache Web  server
 Be familiar with Cisco’s Command Line interface,
 Strong skills in operating systems and experience with embedded operating systems,
 Have a strong theoretical background in network software with 3+ upper classes in  networking, routing protocols,
 Have strong background and experience in  creating user interfaces, 
 Applicant should have a strong background  in: TCP/IP,  Routing protocols (RIP, RIPng, OSPF (v2/v3), BGP, IS-IS, PIM,  MSDP), Switching protocols (STP, RSTP, MSTP, 802.1aq, TRILL), Wireless protocols  (802.11, military radios),
 Strong background in 802.11 Wireless devices  (CAPWAP),
 Experience writing Network Management protocols (SNMP, XML, Agent  X, SMUX, etc.),
 Experience with Web servers (Apache),
 Experience with the  Linux operating system,
 Experience with embedded operating systems, 
 Strong theoretical background in compilers and debugging tools, 
 Experience with routers (cisco, juniper, 3com, extreme), and 10G switches  (Force-10),
 Experience with wireless controllers (Cisco, Trapeze),   and
 Theoretical and practical experience creating network test  automation

It's pretty obvious that what you did was not come across my resume, rather you harvested my email address and are blasting this out to 10,000 people hoping for a hit. If you did your job (for the money a broker gets, I expect this), you would not have bothered me with this. As it is, you just come across as lazy. I don't work for lazy agents.

Contact Us

Name

Email *

Message *