using Membership on production server (Copy Web Site tool) - Used to SQL Server 2000 enterprise edit

[Louis R.]

Hi!

I read a lot about that and I still cannot figure it out. So. I'd like to
use membership into an web application that will run on a production
server(s). On my dev machine, using Web dev Xpress 2008 and SQL Server
Xpress 2008, all is well. My Aspnetdb.mdf is running fine in my App_data
directory.

When I use the copy website utility to transfer all this on a "test"
production server and try to login or anything, I'm getting the predictable
"Failed to generate a user instance of SQL Server due to a failure in
starting the process for the user instance."

Now, my "test" prod server runs .NET 3.5, IIS 6, SQL server 2000 (Std
Edition) and SQL server Xpress 2008. I could install SQL 2008 Standard if
necessary but for now we were delaying this. I could do the same on real
prod servers when ready.

I have many questions :

1) What do I need to have and to configure on my prod server (SQL Server
2008 XPress or 2000 Standard or anything else) to use Membership with
Aspnetdb.mdf in a App_data directory ?

2) Is it possible to use many concurrent secure Web app on a production
server, using separate membership structure based on separate Aspnetdb.mdf
in
each App_data directory of each app?

3) Do I have to use SSL connection to keep secure "Username /Password" use
during login ?

4) Is it a good rule to use the App_data for other application databases
(Inventory, parts catalog, orders and financial data, ...) and how to
connect
safely their use with the Role/membership structure ?

4) Is it possible/suitable to have a "mixed model", using Windows
Authentification to get in the app zone, login in (to identify more
precisely
the user) afterward ? And why is it or is not a good way to go ...

Thanks anyway and have a good day !

 

[Gregory A. Beamer]

Try this as a start:
http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!630.entry

 

[Steven Cheng]

Hi Louis,

I think Gregory has provided a very good article that contains detailed
illustration on how to publish ASP.NET site with SQL Database used.

Here are some of my understanding and comments regarding on the questions
you mentioned in the opening post:

1) What do I need to have and to configure on my prod server (SQL Server
2008 XPress or 2000 Standard or anything else) to use Membership with
Aspnetdb.mdf in a App_data directory ?
=======================================
Generally mdf(SQL db) in App_Data is rely on SQL Express's "User
Instance", therefore, you need to make sure SqlExpress is installed and
"User Instance" is enabled on the production server.

#SQL Server 2005 Express Edition User Instances
http://msdn.microsoft.com/en-us/library/bb264564.aspx



2) Is it possible to use many concurrent secure Web app on a production
server, using separate membership structure based on separate Aspnetdb.mdf
in
each App_data directory of each app?
======================================
ASP.NET provider tools to let you initlize a certain SQL Server database to
become storage for ASP.NET sql based providers(such as membership, role,
profile ...). Therefore, you can choose whatever database/server instances
you want to use. You can let multiple ASP.NET application share the same
database(through not quite good). And you can also let each application has
their own SQL database.



3) Do I have to use SSL connection to keep secure "Username /Password" use
during login ?
==========================================
Sure, over internet, it'll be good to have HTTS/SSL to ensure credentials
transfer security since by default everything is passed as clear text on
html page(If you 're using forms authentication).


4) Is it a good rule to use the App_data for other application databases
(Inventory, parts catalog, orders and financial data, ...) and how to
connect
safely their use with the Role/membership structure ?
================================================
It's up to you. but I think maybe it's better to ask some DB guys this
question since you'll need to consider something like backup, management
when you store too many different data in a single database.



5) Is it possible/suitable to have a "mixed model", using Windows
Authentification to get in the app zone, login in (to identify more
precisely
the user) afterward ? And why is it or is not a good way to go ...
=========================================
According to sql server security, windows authentication is always
prefered. You can configure your ASP.NET application to running under a
process identity that has sufficient permission to access the db. SQL
authenticaiton(with username/password in connection string) just send clear
text(base64 encoded) info via network.



If you have any further specific questions on this, welcome to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.






--------------------

From: "Louis R." <[email protected]>
Subject: using Membership on production server (Copy Web Site tool) - Used

to SQL Server 2000 enterprise edition

 

[Louis R.]

Thank you very much for your answers.

I would have other questions, or precisions to ask about these.

1) I was aware of what "user instance" are and since I do want to have all
my Internet User share the same data according to their privilege, I did not
plan to use this technique. But, do you mean that the App_data directory in
a web app can only be used with "User instance" connections ?

2) For my Web app, I would like to use a aspnetdb.mdf to manage my security
and access privilege (Membership) and some other SQL databases containing
application data. Where should I put these databases and can I use them
indifferently with SQL Server 2008 Express or Standard edition ?

3) Do I understand correctly what you say about the login process : the only
way to protect the username/password authentification process is to use SSL
connection to login ? Could I use Windows authentification to protect this
part of the login process and still use the benefit of the Membership
technique?

4) As for the SQL Server connections I use in my Web App, if I create an
Active Directory User to access the SQL databases and use it in my aspnet
connection, is it the safer way to proceed ? Is it the way it was intended
for Web App ?

Thanks again.

 

[Louis R.]

Hi Greg !

....Peace and Grace to you also, specially in this beginning of 2009

Thanks for your article. I read it, and appreciate the way you describe the
process (screens, steps, ..).

One thing. My Web app will run on an Internet server (computer) that
belongs to us, on which we have SQL Servers and IIS6. So publishing a SQL
database is something we are familiar with, even if we have surprise from
time to time, but I was very glad to learn about this DB publishing wizard.

Also, what I understand from your article is that after publishing, we have
to go and modify the connections strings in the webconfig file. So it means
that we cannot publish this file again from our dev machine if other things
would have to change about the rest of the application as it would get the
connections strings back to their local expression. Am I right about that ?

Anyway, I would have to know if I have to keep the database files (Aspnetdb
and others) in the App_data created with my Web Dev Xpress and if I have to
"Attach" manually these dbs to the SQL Server instance I will use ?

Can I use indifferently the Xpress version or the Standard version of SQL
server ?

Thanks again.

 

[Steven Cheng]

Hi Louis,

Thanks for your reply.

For the further specific questions, here are my comments in line:


1) I was aware of what "user instance" are and since I do want to have all
my Internet User share the same data according to their privilege, I did
not
plan to use this technique. But, do you mean that the App_data directory
in
a web app can only be used with "User instance" connections ?
=====================================
"User instance" can let you use sql server mdf database file without
attaching it to a certain SQL Server instance. Sure, you can still put mdf
database file in App_Data folder. In this case, you will have to attach the
file to a sql server instance(such as SQL Express or other named or default
instance) so as to use them.


2) For my Web app, I would like to use a aspnetdb.mdf to manage my security
and access privilege (Membership) and some other SQL databases containing
application data. Where should I put these databases and can I use them
indifferently with SQL Server 2008 Express or Standard edition ?
=================================
Generally a single mdf file contains a single database. Sure, you can store
both membership and other business data into the same database. As I
mentioned above, you need to attach the database file to an instance first.


3) Do I understand correctly what you say about the login process : the
only
way to protect the username/password authentification process is to use SSL
connection to login ? Could I use Windows authentification to protect this
part of the login process and still use the benefit of the Membership
technique?
=========================
By using windows authentication, you do not need to put username/password
in connectionstring. And to use windows authentication to connect SQL
Server in your case, you need to make sure your ASP.NET application's
running account(process account) has sufficient permission to access the
database. Here is an article introducing IIS/ASP.NET process identity:

#Process and Thread Identity in ASP.NET ¨C A Practical Approach
http://blogs.iis.net/sakyad/archive/2008/11/19/process-and-thread-identity-i
n-asp-net-a-practical-approach.aspx

In short, you need to let your ASP.NET running under a fixed account, this
account has sufficient permission to access the sql server. Then, you can
just configure your SQL server connectcion to use windows
authentication(instead of putting username/password in connection string).



4) As for the SQL Server connections I use in my Web App, if I create an
Active Directory User to access the SQL databases and use it in my aspnet
connection, is it the safer way to proceed ? Is it the way it was intended
for Web App ?
============================
Just like I mentioned in #3, you can configure your ASP.NET application to
run under a specific account as long as that account has sufficient
permission to access any protected resource. Also, try not to grant to
much permission & privilege to that account so as to reduce security risk.

The article below is a good one which introducing all the common data
accessing (security specific) pattern in ASP.NET, including SQL Server
authentication(put username/pass in connection string) and windows
authentication(this is called "Trusted subsystem" pattern in this article):

#How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998292.aspx

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).


--------------------

From: =?Utf-8?B?TG91aXMgUi4=?= <[email protected]>
References: <#[email protected]>