[RESOLVED] Built in Webforms security - Access or Sql Server?

[dolot]

I've been looking into the built in security that comes with a new webforms application in VS 2012. I noticed that it creates a .mdf file for you with all the tables and such to run the login screen and manage security. My dev lead, however, wants all the backend databases to be in Access - at least for development.

Is there a way to have VS create the backend security database in Access?

Any thoughts on this subject would be appreciated. Noobism going on here.

[szlamany]

Wow - really? The dev lead wants that? MS has a really cool security layer and needs ms sql express to power the backend of that.

If you want to host your data in ACCESS in your backend that's fine.

No reason to touch the authentication stuff. There are stored procedures within that sql express database. Check them out - open the DB in SSMS and look at it.

[dolot]

I think he's concerned about being able to deploy it to the customer for user testing - that they won't have sql express installed on their system and so it won't work. Are we worried about nothing?

I tried opening the sql express database in SSMS, but I couldn't - my version of SSMS is too old.

[szlamany]

They have to have IIS installed on the server - right?

I would imagine that SQL Express is installed along with IIS - since it seems the authentication relies on it.

Hopefully someone with more IIS knowledge will come along and back me up on this.

[dolot]

Well, what I'm thinking is that the guy may just install it on his local machine - and that machine may be an XP box.

[sapator]

I can't believe i am doing this (access advice) but here goes. You boss is an idi...Err sorry, i meant here, knock yourself off :
http://forums.asp.net/t/989020.aspx/1

[szlamany]

Where does IIS run in that?

[dolot]

Sorry, I didn't mean to imply that my boss was installing this on his local machine, but rather the customer, and since he works for the military he's rather hamstrung on what he can do.

[szlamany]

Where does IIS host the website from?

[dolot]

So I did some experimentation. I manually took all the tables and relationships from the MS generated Sql server db and put them into an access db, and then created a connection string for the access db and pointed everything in web.config to the new access db connection string. But it didn't work.

"A null was returned after calling the 'get_ProviderFactory' method on a store provider instance of type 'System.Data.OleDb.OleDbConnection'. The store provider might not be functioning correctly. "

Not sure what that means, but I'm guessing there's more to this than just copying over the tables. I'm thinking that default login pages are relying on the backend being sql server. I didn't see any stored procs. in the sql database. There were some data types that didn't match between sql server and access (UniqueIdentifier in sql server - used long integer in Access.) Just not sure what's going on here.

[szlamany]

Why are you not answering my question?

[szlamany]

There are stored procedures within that sql express database. Check them out - open the DB in SSMS and look at it.

I already told you that the SQL database had stored procedures - now you can see for yourself.

Should have looked before you copied tables over.

ACCESS does not support stored procedures - from what I have heard (I don't do access myself).

But again - why are you not answering my question about IIS???

[szlamany]

I can't believe i am doing this (access advice) but here goes. You boss is an idi...Err sorry, i meant here, knock yourself off :
http://forums.asp.net/t/989020.aspx/1

I do not think the link you gave has anything to do with an ACCESS DB...

[dolot]

I already told you that the SQL database had stored procedures - now you can see for yourself.

Should have looked before you copied tables over.

ACCESS does not support stored procedures - from what I have heard (I don't do access myself).

But again - why are you not answering my question about IIS???

I knew it could never work if there were stored procs. Access doesn't have them. So I checked for stored procs in the sql server database, and according to Server explorer it doesn't have any. So I figured all the logic was in the asp.net code somewhere.

As far as the IIS question, I didn't really understand where it was going. Where does IIS host the website from? I suppose it hosts it from the computer on which it is running. If you could flesh that question out some more perhaps I could answer it.

[szlamany]

I guess that means you do not know that IIS is a service that has to be installed on a SERVER in order for a customer of yours to be running an ASP.Net web application.

When you run the app in the VS IDE it starts a little development "IIS service" for you.

A client of yours will not have that - right?

[szlamany]

http://msdn.microsoft.com/en-us/library/aa478949.aspx

Look down at

"Table 2-2. Stored procedures used by SqlMembershipProvider"

[gep13]

Hello,

Out of the box, the ASP.Net Membership Provider works for a SQL Server Database. There is nothing to stop you from using another database backend though, but you would need to create your own implementation of the Membership Provider, in order to allow the ASP.Net Engine to access the data that you need. Luckily for you, there are many providers out there already, including a Provider for Access.

Here is an MSDN article that talks you through this:

http://msdn.microsoft.com/en-us/libr...v=vs.100).aspx

And this article goes into more detail:

http://www.4guysfromrolla.com/articles/120705-1.aspx

This:

http://www.binarywater.com/OleDbASPN...pProvider.html

seems to be a complete implementation, but I can't say that I have used it.

NOTE: There is a lot of work and configuration involved in setting this up, so unless you REALLY have to, I would suggest that you stick with what comes out of the box.

[gep13]

I knew it could never work if there were stored procs. Access doesn't have them. So I checked for stored procs in the sql server database, and according to Server explorer it doesn't have any. So I figured all the logic was in the asp.net code somewhere.

The Membership Provider for SQL Server definitely uses Stored Procedures.

[szlamany]

@gep13 - but isn't the need for IIS more predominant in this aspect? Is there a non-install version of IIS that runs on workstations??

[gep13]

Given a developer workstation, using at least IIS Express, is pretty much a requirement. Relying on Cassini won't work for a lot of things, and deploying and testing to IIS is a must.

[szlamany]

I understood that his boss wanted to have installs running without SQL - using ACCESS instead.

But in the long run IIS is a must - or is CASSINI something you are allowed to run locally in a production website?

[sapator]

We shouldn't be talking for this anyhow. Clearly his boss must fire himself immediately. SQL express or mysql or any other standard database system should be used, not a db wannabe.
The article i gave is access related and i found it following another article that gives a complete and ready to go Access membership database but since the OP does not care or replied and simply ignored it i should keep the database for myself rather than making his idiotic boss happy.
(Sorry Gary about one bad word)

[gep13]

I understood that his boss wanted to have installs running without SQL - using ACCESS instead.

But in the long run IIS is a must - or is CASSINI something you are allowed to run locally in a production website?

Using Cassini in Production, in my opinion, would be a big no. It would have to be IIS, or Apache (using mod_mono). Cassini is a development server, and should only be used as such.

Gary

[gep13]

(Sorry Gary about one bad word)

I'm watching

[szlamany]

Wow - hate to belabor this point then!

Back to the OP - DOLOT!!

If you are going to host this website on a server - for your customers - that has IIS installed - you get MS SQL Express installed with that automatically.

Your boss does not have to worry about SQL installation. Actually getting ACCESS installed on a server running IIS seems a bit harder in my opinion!

[dolot]

Hello,

Out of the box, the ASP.Net Membership Provider works for a SQL Server Database. There is nothing to stop you from using another database backend though, but you would need to create your own implementation of the Membership Provider, in order to allow the ASP.Net Engine to access the data that you need. Luckily for you, there are many providers out there already, including a Provider for Access.

Here is an MSDN article that talks you through this:

http://msdn.microsoft.com/en-us/libr...v=vs.100).aspx

And this article goes into more detail:

http://www.4guysfromrolla.com/articles/120705-1.aspx

This:

http://www.binarywater.com/OleDbASPN...pProvider.html

seems to be a complete implementation, but I can't say that I have used it.

NOTE: There is a lot of work and configuration involved in setting this up, so unless you REALLY have to, I would suggest that you stick with what comes out of the box.

Thanks, Gary. I'll take a look at this info. Your information - as well as you attitude - has been helpful, as usual.

[gep13]

Thanks, Gary. I'll take a look at this info. Your information - as well as you attitude - has been helpful, as usual.

Not a problem at all. Let me know if you have any questions.

There are a few pieces to the puzzle that need to fit together here, and maybe, it is worth a little time just to recap them here.

Out of the box, Visual Studio 2005 (I think) and upwards normally install a version of SQL Server Express. This makes running some of the out of the box templates really simple to do, as there is already a SQL Server instance there ready to be used. It also eases the transition to a production server, as you can simply export the data from the SQL Server Express instance, and import into your production SQL Server.

In addition, Visual Studio also ships with a built in Web Server, commonly referred to as the ASP.Net Development Server, or Cassini web server. This allows deployment and debugging on an ASP.Net Application, without the need to get IIS involved. However, Cassini has some limitations, and it is only when you deploy to an actual IIS instance, that you can do some more involved testing. However, in order to install IIS, you need admin permissions on teh machine that you are running on, which not every developer has. As a half way house, Microsoft started shipping IIS Express, which has most of the features of a full IIS instance, but doesn't require admin access to install. Within your ASP.Net Application Properties, you can specify whether you want to use the ASP.net Development Server (the default), an IIS Express instance, or a full IIS instance.

With all of these things in place, setting up something like an ASP.Net Application, that uses the Membership, Roles, adn Profile Providers, is really simple. Your Visual Studio instance will create teh project template, and install a new SQL Server Express (an mdf file) which it connects to the local SQL Server Express instance, and runs the application under the ASP.net Development Server.

All of these things you can change though. You can use an Access Database, a MySQL Database, an Oracle Database, etc. However, you need to configure a Membership Provider for each of these other databases, you don't get that for free. However, it is just "easier" to use SQL Server Express, as 9 times out of 10, it is installed on teh machine that you are running Visual Studio on.

When it comes to deploying to production, it is unlikely that you will have Visual Studio installed, therefore you won't likely have SQL Server Express (as there are limitations to this which would stop you using it in production), which means you have to create the database elsewhere, and also host the website elsewhere, namely a full IIS instance.

Hopefully all this makes sense. If not, feel free to ask.

Gary