Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > Other ASP.NET > ASP.NET 1.x and 2.0 Application Design
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.x and 2.0 Application Design Application design with ASP.NET 1.0, 1.1, and 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.x and 2.0 Application Design section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 30th, 2004, 10:27 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default Secure SQL Server authentication

I need to create an ASP.NET front end for an existing SQL Server Database. The Database has 200+ SQL Server User names in it. Each user name is assigned to any of the 9 SQL Server Roles. All ASP.NET pages will have sensitive data that only certain users within the SQL Server roles can view. All pages are driven by the data on the ASP application.

When a user enters a record the record gets a stamp with the USER() stamp from SQL Server. This is used to open their records next time they come into the database. Windows Authentication is not possible in this environment due to political reasons. I know, this would simplify things and make this more secure, but…(politics)

Here is my question:
I need to get the SQL Server user name and the password from the user that comes into the site. How do I keep this user name and password securely so that I can use it in all 70+ pages that the application will have? I do not want them to have to re enter their usename and password more than once, and i need this to be secure.

If I create one blanket SQL Server user that I place in the web.config file, I lose the ability to track individuals inside of the database and the database becomes useless.




Sal
__________________
Sal
  #2 (permalink)  
Old October 4th, 2004, 12:22 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You can use forms authentication to create a login system. The login actually have to do anything, but you could probably link it up to the database server's users table to verify that they are a valid SQL user. For this query you'll need some admin role user for the connection string.

Once the user is logged in, you can store the username and password in session so that you can retrieve it on all the pages that are making queries.

You could store the connection string in the web config like this:
"user id={0};password={1};Data Source=<server>;Initial Catalog=<database>;"

Then whenever you use the connection string, just run it thru String.Format:
strConnString = ConfigurationSettings.AppSettings("ConnString")
objConn.ConnectionSring = String.Format(strConnString, Session("uid"), Session("pwd"))

This way you can keep the connection details centralized but have the user part dynamic.
  #3 (permalink)  
Old October 4th, 2004, 01:13 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My database table for keeping users and passwords is SQL Servers sysxlogins table from the Master database. You mean that this table can be used in forms authentication? I am not sure if I want to use a sytem table for this, but I do not want to keep another table with usernames and passwords when sysxlogins and sysusers is keeping that already.



Sal
  #4 (permalink)  
Old October 17th, 2004, 04:02 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

If you want to use the SQL logins as your user logins then you don't have much other choice. Your login process doesn't have to really do anything aside from take a username and password. Then any time you try to make a query call using the user provided name and password the query will either work (good login) or fail (bad login). The only way you might have of telling that the user login is good when they actually log in is to try a simple query. If you get back a bad password error from SQL you'll know that the user and/or password is bad. The passwords in SQL Server are stored in some encrypted or hashed for so you may not be able to query that table directly to check the login.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't pass query string variable on secure server gnilly BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 August 24th, 2005 11:18 AM
Connect to SQL Server using NT Authentication (ADO donnie200 Access VBA 5 July 15th, 2005 06:33 AM
Secure server and cookie trouble joop Beginning PHP 2 March 9th, 2005 08:41 PM
forms authentication with sql server 2000 Lee8mm ASP.NET 1.0 and 1.1 Professional 0 September 26th, 2003 08:58 AM



All times are GMT -4. The time now is 03:04 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.