Setting up access to SQL DB through ASP Page

Question

Setting up access to SQL DB through ASP Page

JRJ

SSC Veteran

Points: 223
More actions
June 7, 2008 at 9:31 am

#99150

Hi,
I have an existing web application with ASP scripts running on IIS and a SQL server DB. The user who connects are part of the domain and connects to the Database using the Windows NT authentication (there is a system DSN created with Windows Authentication and on the sql server a login created for the domain-user group and necessary permissions were assigned for the login) This works without a problem.
Now I need to allow external users who are not part of our domain to acceess the same DB to read some data (not to write). I don't want to create domain accounts for them, so they cannot use the same way to connect to the DB. I think to create a login in the SQL server (with limited permissions) and the web page (external users) connects using this login and differentiate the users by maintainning a table with username/password.
Please let me know whether I am in the right direction, if not please give me some advice on how to do it. Also let me know where do I have to be careful in setting up this as I am dealing with some sesitive data.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Do you mean to say that external users connect thru the web page and the other "normal" users do not.

If this is the case then having a seprate login will work out. Just make sure that you give the external users login only those permissions that are neccessary.

You can also have a domain user account with limited permissions mapped to a sql login. This way you have the more powerfull windows security working for you.

I hope you are using stored procedures to handle the data so that you can set the permissions on the procedures.

I did not understand what you meant by having table with the users/pwd stored in it.

"Keep Trying"

JRJ SSC Veteran Points: 223 More actions · Answer 2

External and normal users connect through the web page, however normal users have user accounts in our domain and external users don't.

I need to differentiate the external users, for this I keep a table with username and passwords in the database, hope this is clear.

Thanks for your response, please give me feedback.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

Keep a login for the external users with limited permissions.

You can differentiate external users by checking the sys.sql_login table or other system tables/views. you do not need a separate table just for that unless you have other use for the table.

Hope i have understood things properly...

"Keep Trying"

JRJ SSC Veteran Points: 223 More actions · Answer 4

thanks for your reply. I think your understood the situation properly, however I will explain the situation once more in detail.

The internal users create some reports related to the external users, and the internal users are part of our domain and their usergroup name is mapped in the DB and write/read permissions is granted for them; these users access the DB through a differnt page to write these reports.

Now, I am creating a page for the external users so that they can access the DB to view reports belongs to them. When they access this page I connect to the DB using the Login (this have only read permission) that I created; this page has a login and uses a table like follows:

userName Password ExternaluserID

extUser1 ***** 1

extUser2 ***** 2

externaluserid is connectd with the reports; so when they are logged in, I can display the reports which belongs to them.

All the external users connects to the DB using the following connection string in my web page:

Dim str_Connection

str_Connection = "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = testA; User Id =testuser; Password=*****"

Hope this makes sense, or may be there is a better way to do this.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Now things are more clear...

I think you "know" who your external users are going to be.

You are storing the external user data so that each external user gets to see the only that report that the user is entitled to see.

I see nothing wrong with your design.

Make sure you give minimal permissions to the login for external user and test properly. If you can test with tools that "break" the code that much better. I have seen the security breached a number of times in my projects.

If you are using dynamic queries make sure you are not open to sql injection attacks.

"Keep Trying"

JRJ SSC Veteran Points: 223 More actions · Answer 6

Thanks Chirac for your time. I gave only read permission for the external login.