Connection Pooling & Users

[Terry Holland]

I have an intranet application that comprises an ASP.Net application
connecting to a SQL Server DB
The application has 150 users. At the moment I am connecting using the
following setup

I have created a MyApp_WebUser user in SQL Server. My connection string,
which is stored in my web.config file, is

<add key="DB:CSA" value="data source=MyServer;initial
catalog=CSA;uid=MyApp_WebUser;pwd=12345678" />

and by business objects use this string to make all its connections to the
database. This way, all my connections strings are identical and I can make
use of connection pooling.

However, this is causing some problems in the business domain. My DB
contains a User table in which I have a record of all users, their
application login and their application password. When users log in via a
login page, their username and password are checked against the User table
and they get access to various pages according to their role (I also have a
Role table). There are a number of issues that I need to address here and
would appreciate advice

1) It is a business requirement that all passwords need to change every 30
days and they need to conform to a particular pattern. I figure I could
either, write my own code to enforce this in the application or allow users
to log in using their active directory passwords.

If I use their windows login I would have the advantage of letting Windows
deal with the changing of passwords etc, but would I be right in thinking
that connection string should be changed to

<add key="DB:CSA" value="data source=MyServer;initial
catalog=CSA;integrated security = SSPI" />.

If this is the case, then my connection string will be different for each
user and I will not be able to take advantage of connection pooling

2) If I stick with my User table and write my own code for enforcing the
password requirements, how could I store their passwords in the db in an
encrypted format?


Advice appreciated

Terry Holland

 

[Terry Holland]

Also

If I do store my connection string with UserID & Password in web.config
file, how secure is thisd?

Terry

 

[V]

Hi Terry,

Reposnses below:

If I use their windows login I would have the advantage of letting Windows
deal with the changing of passwords etc, but would I be right in thinking
that connection string should be changed to

NO - you are simply changing your authentication mechanism (you might
want to retain the functionality for roles and basically retain your
user and role tables too). Instead of checking the user name password
against the database, you can use Directory Services to test it against
an active directory (do not use integrated authentication, for minimum
impact to your current application). You will need to change ONLY one
function (the one which checks for the username password in the
database).

The Connection string is totally independent of the users of your
application. It can stay exactly as it is now.

2) If I stick with my User table and write my own code for enforcing the
password requirements, how could I store their passwords in the db in an
encrypted format?

The approach mentioned above will let you use the active directoy
policy, so you won't have to write your own code, but for future
purposes, you should always Hash (instead of encrypting) your
passwords. There is a function available in the FormsAuthentication
class (i think) which allows you to do that.

Third - the database connection string in your web.config file:
- If you want to keep it there, keep it in some encrypted form (instead
of plain text) and decrypt it before using. So you could decrypt it and
cache it for your application to use, every so often.

Regards,
Vaibhav

 

[Marina Levit [MVP]]

What you use for authentication to your application, and what you use to
connect to the database are not related. You can use integrated for
authentication to your application - that has no bearing on your connection
string to the database. So you can use integrated, or not.

Btw, just using integrated in your connection string, means that the
connection will be made as the ASPNET user (or whatever ASP.NET is running
under on that machine). It does not mean the credentials of the current user
of your application will be used. You can have it pass the credentials of
the user with a Web.config setting to tell it to impersonate the current
user.

If you want to roll your own security, .NET provides many encryption
implementation for you to chose from. Check out the
System.Security.Cryptography namespace.

 

[S. Justin Gengo]

Terry,

Not quite...

If you use the active directory to login your users your application may
still utilize integrated security for the sql connection and it won't run as
each individual user unless you put <identity impersonate="true" /> into the
web.config file. Instead the integrated security connection to sql server
will use the ASPNET account that the website is running then .net code with.

At this point which user account your web application will use to connect to
sql server and how you should treat such will differ based on which version
of IIS you are using and based on which version of asp.net you are using.

Could you let us know which versions you're working with?

Regards,

--
S. Justin Gengo
Web Developer / Programmer

Free code library:
http://www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Terry Holland]

Thank you.

I have successfully re-written the function to authenticate users using
Directory Services