Best use of ASP.NET membership tools and SQL logins/roles

[DatabaseDude]

I'm just now learning of the membership tools available in ASP.NET 2.0 ... looks very powerful, and relatively easy to manage. It brought up some questions about how they'd relate to SQL logins and roles.

An app that I am expecting to begin working on shortly will have a hosted SQL Server 2005 as its data source, using SQL authentication. (By hosted, I mean a hosting company will provide the hardware and maintain the server, but it will not be shared with other customers) The database will have several SQL roles, with SQL logins assigned to each. Stored procedures, views, etc will all have their permissions tied to user roles. There is the likelihood that users will be interfacing with means other than the ASP.NET pages (VB.NET forms are a possibility ... want to leave this functionality open) so permissions being based on data is fairly important.

Questions:
- How do folks typically work with such an app ... use ASP.NET membership in conjunction with SQL logins/roles, or just choose which of the two is best for their situation?
- Better to use Windows authentication? (possible in such a scenario?)

I'm not much on server technology, and just learning the ASP.NET authentication ... thanks much for everyone's insight

 

[jbenson001]

I have used the sql roles/permissons for login and object access in the DB, and use the Memebership portion for the permissions/access in the application.

 

[jmeckley]

The ideal situation for this scenario is to have at least 3 layers:
Data Access Layer (DAL) there are many 3rd party tools for this. I use a product called [tt]LLBL Gen Pro OR Mapper[/tt]. It runs about $350 per user. You could also design your own, or use another companies product. This layer maps closely to the db schema and takes care of your CRUD operations. (Create, Read, Update Delete).

Business Logic Layer (BLL). this is where stuff like validation, auditing, and logic specific to your needs reside.

Presentation Access Layer (PAL) is some type of presentation manager. it should be GUI engnostic. this ties user input to business logic.

Once this is in place you can tie any type of Graphical User Interface (GUI) to your logic. Web, Desktop, Console...

Now these are your logical tiers. You will need to determine what is the best phsical seperation of these layers. The most common practice is to build each layer into their own assembly(s)(project). Then reference the projects with one another.
Example:
BLL references DAL
PAL references BLL

A very common, an sometimes abused, pattern is the MVP or MVC pattern. Google this for more information. There are alot of other patterns out there that may be better suited to your situation, just depends on the requirements.

You'll also need to descide how to deliver the layers. WCF is a hot buzz word right now with .Net 3.0 (backwards compatible with 2.0). Webservices and remoting are other options, or you may just choose to bundle the whole package together (server for web app, and client for desktop).

As for authentication. first determine how to structure your project and what the layers (logical and physical) will be. Then you can start to answer questions like how will authentication effect this system.

in your situation you need to decide if all the users are within the same domain forest? if so Windows authentication is an option. If not then username/password management is required. while asp.net has some very nice controls, how will this tie back to a desktop interace, or webservice?

Since your DB server will have a predetermined set of usernames/roles you will, at some point, need to map the authencicated user to the db connection string.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[kss444]

Just my 2 cents. Microsoft has a lot of "Application Blocks" that are free. One block is for data access, another is for security and then there are others.

I don't remember how many they have, but I have looked at them and they seem pretty good to me, although I have not used them.

 

[jmeckley]

kss44, I totally forgot about Enterprise Library 2.0. This is a great project, I use the exception and logging blocks. (I wouldn't recommend Ent Lib 3.0 just yet, it's still in Beta). here's the website [

http://www.microsoft.com/downloads/...70-406B-4F2A-B723-97BA84AE80B5&displaylang=en

]

Jason Meckley
Programmer
Specialty Bakers, Inc.