Page 1 of 1

Store VWD user credentials in DBMS Store user credentials and group data in DBMS instead of in default fi

#1 oldSwede   User is offline

  • D.I.C Regular
  • member icon

Reputation: 4
  • View blog
  • Posts: 464
  • Joined: 08-January 16

Post icon  Posted 22 November 2009 - 01:04 PM

Using a DBMS for storing WAT security data

This is tested with Visual Web Developer 2008 Express Edition (VWD) and SQL Server 2005 Express Edition (SQLS) under XP.

When programming in VWD you can use built in functions for user validation, login and creation. These functions reside in the Toolbox sidebar under the Login heading. This is great since you don't have to handle security yourself, meaning that cookies and such are taken care of for you. In your code you can use functions like User.Identity.IsAuthenticated e.g. in the Page_Load event handler to lock users out from the page if they are not logged in.

That's nice, but...
All the user data is stored in data files in the App_Data folder that is part of your solution. Now, that might not be a big problem if your site only has a few users. But with lots of users logging in and out you might want the data to be stored in a DBMS like SQL Server. This is how you go about to do that:

There are four steps to go through:
1) Create a new empty DB in the instance you want to use.
2) Use C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe to create tables within the DB.
This creates the tables and other stuff that WAT needs in the DB.
3) Create a connection string for the new DB in your web.config file.
To connect to your DB instead of the default file based one.
4) Configure providers in your web.config file for use with this DB
To tell VWD what connection string to use and some other stuff.
5) Choose the new providers from within the WAT tool.
To make VWD use your DB instead of the default file based one.

In detail:
----------
1) Create a new empty DB in the instance you want to use.
Using e.g. SQL Server Management Studio Express you create a new database in an exsting or new instance of your DBMS.
Lets say I have an instance named JensSrvr and that I create a new empty DB named JensWATdata in that instance.

2) Run C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe to create tables within the DB.
- Tell it what server and instance the DB should reside in (in my case the network name of my computer and my instance: ACER17\JensSrvr).
- Choose "Windows authentication" if the instance is on the computer you are working on. (Otherwise you should probably use "SQL Server authentication")
- Choose the database to hold the WAT security data, in my case JensWATdata.
- Click next and next again.

3) Create a connection string for the new DB in your web.config file.
If you use SQL Server Authentication your connection string should have 'User Id=****;Password=****' (with your username and password in place of ****) instead of 'Integrated Security=True'.
	<connectionStrings>		<!-- connectionStrings go right in the top level of the web.config -->
		<add name="AcerWATSQLServer" connectionString="Data Source=ACER17\JensSrvr;Initial Catalog=JensWATdata;Integrated Security=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>	



4) Configure providers in your web.config file for use with this DB
When choosing providers you have a choise "Select a single provider for all site management data" or "Select a different provider for each feature (advanced)" and unfortunately I have not found a way to get the new provider to be available as a second single provider. This is not a problem but irritating (tell me if you find out how to). Instead you will have to choose "Select a different provider for each feature" which means we will have to make two providers but both will use the same connection string.
4.1) Membership provider
	<system.web>		<!-- Membership and role managers go right under "system.web", the "<system.web>"-tag already exists in the file. -->
		<membership defaultProvider="JensMemberProvider">
			<providers>
				<add connectionStringName="AcerWATSQLServer" enablePasswordRetrieval="false"
					enablePasswordReset="true" requiresQuestionAndAnswer="false"
					applicationName="/" requiresUniqueEmail="true" passwordFormat="Hashed"
					maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6"
					minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
					passwordStrengthRegularExpression="" description="Jens provider"
					name="JensMemberProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, &#xD;&#xA;								Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
			</providers>
		</membership>



4.2) Role manager provider
	<system.web>		<!-- Membership and role managers go right under "system.web, the "<system.web>"-tag already exists in the file. -->
		<roleManager enabled="true" defaultProvider="JensRoleProvider">
			<providers>
				<add connectionStringName="AcerWATSQLServer"
					applicationName="/"
					description="Jens provider"
					name="JensRoleProvider"
					type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, &#xD;&#xA;								Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
			</providers>
		</roleManager>



5) Choose the new providers from within the WAT tool.
- Start VWD, click menu "Website" -> "ASP.NET Configuration". This will start the WAT tool.
- Choose "Provider Configuration" -> "Select a different provider for each feature (advanced)"
- Now choose your member provider (in my example JensMemberProvider) and your Role manager provider (in my example JensRoleProvider).

After all this you should be able to add users through the WAT tool and see them in the new tables through SQL Server Management Studio Express.

Regards
/Jens

Is This A Good Question/Topic? 0
  • +

Page 1 of 1