[Resolved] App_Data ASPNETDB.MDF - Deployment

[Bruce Fox]

Hi again,

I'm still confused - a little.

On the topic of deployment to an SQL Server (non-express).

My intension is to migrate my web site to a production server (SQL 2005).

Currently, users login, and authenticate via the ASPNETDB.MDF (located in the web sites 'App_Data' folder).

When the migration takes place do I/should I attatch an instance of the ASPNETDB.MDF on the SQL server/create it with the aspnet_regsql.exe, or is it ok to leave it in the App_Data folder for ever more?

Ta.

[mendhak]

You'll need to run a stored procedure to attach it to the SQL 2005 production database.

It gets automatically attached if you're running SQL Express, but no production server would run SQL Express, that is why you need to get the administrators or whoever is in control of that server to attach it.

If this is a web host online, then they will refuse to attach it for you.

[Bruce Fox]

Hi mendhak,

No web host involved in this case. Install will be on two company LANs.

Attaching the ASPNETDB.MDF is achievable. I just wasn't sure if it was the way to go
on a production server to authenticate users etc.



Love your work.

[mendhak]

No problem. What work do you mean, by the way?

[Bruce Fox]

"Love your work" is a saying.

Common amongst the Aussie vernacular - well in my eyes anyways.

Anyhoo, you have been very busy I see, especialy in the web area - bugger those WinForms types (oh the good ol vb6 days). I must admit I have enjoyed developing something in VSD2008!
Back to the steep part of the curve once again....

Back on topic. Is it common under ASP.Net3.x to use an ASPNETDB.MDF attached to the SQl Server for user web authentication? Or is there a prefered way other than that?

[mendhak]

I would say it's common for small websites, but on enterprise websites one expects the 'full' SQL Server used rather than the MDF so I suppose the answer to your question is... no, it's not common. If you're going to make this a long term solution, it should be in the SQL Server instance itself because one would want the DBA to 'control' the database and not have this data file (MDF) floating around.

[Bruce Fox]

So on a 'full' SQL Server, would you have the DBA create a User/Role (ASPNETDB) databse to facilitate User web-site authentication?

[mendhak]

Yes, I'd ask them (very, very politely) to create the SQL based username/password and grant that user permissions to (again, ideally) EXECUTE the stored procedures required by the application. Or if there are no stored procedures, then let it have SELECT permissions on the table.

Then you, the developer, go into the web.config, code, or wherever the connection string is, and change the connection string to reflect the new credentials.

[Bruce Fox]

Ok, understand.

The reason I'm targeting the ASPNETDB be created on the SQL Server (aspnet_regsql.exe) is that VWD has the Login/logout/Change Password etc components that suit those operations. So I figured using the ASPNETDB is how in the background those components acces and perform Forms authentication.

If there is a better way to use those components, and not use a created instance of ASPNETDB, please let me know - I don't want to set up something that isn't right

[mendhak]

It's possible to use aspnet_regsql with a normal SQL Server. It'll work in either case, because all that these forms and membership controls (that's what they're called, I think) require the presence of certain tables and stored procedures in order to work. If you open up your database and have a look, you'll see a whole bunch of database objects named aspnet_*. These controls that you use will look at the connection string section in your web.config and appropriately follow it, connect to the specified database, and access those objects to get the data or perform the operation that they need to.

As long as the connection string is there and the database has had aspnet_regsql run against it, you'll be perfectly fine. Keep in mind that in a deployment scenario depending on what you've done in the way of configuration of various options or application settings, you may need to script out the database and get the DBA to run that script against the production database rather than getting them to run the aspnet_regsql utility against it. That is your discretion. You can always test it out.

So I suppose the answer to your question is that you can use either the .MDF or SQL Server itself, but in order to get your forms and membership controls working you will need to get those aspnet_* objects onto the database.

Hope that wasn't a confusing answer.

[Bruce Fox]

Nope - crystal clear thanks.

For what it's worth, do you and other members use the aspnet_* tables on the SQL Server to do web-page authentication?

[mendhak]

Bruce, you can't really ask that question and get a definitive answer. Each organization will have a different policy and there will be many different applications with different requirements.

However, I can tell you my observations. When it comes to small to medium scale applications that can exist in their own little bubble, it makes sense to use the provided forms authentication mechanisms. Some organizations, when using medium scale applications, will find the provided mechanism a little too limiting and will end up writing their own Membership Providers for the built in ASP.NET forms and membership controls (for example, calling a web service instead of a database, or using Oracle instead of SQL Server, or using a different or existing table of usernames). Large applications very rarely use the inbuilt mechanisms, they tend to rely more on an organization-wide integrated authentication mechanism or their custom-written solution. This is because, as you know, the large the application, the more complex the requirements.

There are always exceptions to the rule, though. Ever heard of Community Server? It uses the aspnet_* objects in its database, but because it's so large and the requirements so complex, they use their own membership provider classes. This has the unfortunate side effect of making the code rather difficult to debug, but the point is that some applications do use it!

[Bruce Fox]

Thanks for the info mendhak. I now feel comfortable with the way ahead.
Thanks for your time

Bruce.