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=123456
78" />
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 HollandAlso
If I do store my connection string with UserID & Password in web.config
file, how secure is thisd?
Terry|||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|||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.
"Terry Holland" <TerryHolland@.Community.nospam> wrote in message
news:OZrjiq2cGHA.5016@.TK2MSFTNGP04.phx.gbl...
>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=123456
78" />
> 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,
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" <TerryHolland@.Community.nospam> wrote in message
news:OZrjiq2cGHA.5016@.TK2MSFTNGP04.phx.gbl...
>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=123456
78" />
> 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
>|||Thank you.
I have successfully re-written the function to authenticate users using
Directory Services
"V" <vaibhav.gadodia@.gmail.com> wrote in message
news:1147182064.446995.240040@.j73g2000cwa.googlegroups.com...
> Hi Terry,
> Reposnses below:
>
Windows[vbcol=seagreen]
thinking[vbcol=seagreen]
> 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.
>
> 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
>|||Thanks for Vaibhav's informative inputs.
Hi Terry,
I don't think you should change the authenitcation to windows since that'll
different much from your original application design. I think you should
manually do the password change policy in your application. For the
password stored in your custom db, generally, it is recommended to store
password hash rather than clear text to make it securer...
as for data connection string in web.config, ASP.NET has provided
encryption support when deploying the application, here are the msdn
articles discussing on this:
#How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
http://msdn.microsoft.com/library/e...05.asp?frame=tr
ue
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
