Visual Basic/SQL Security Model

[Patt]

I am currently writing a VB application that is connecting to a SQL back-end, and I'm not sure how to proceed (it has been years, literally since I programmed).

If I use Windows authentication for the login to SQL I have a problem because everyone will have access to this program. If I use SQL authentication, I think I'm going to have a bit of a nightmare trying to manage all the various accounts that will be created (400+).

Does anyone have experience writing something similar, or know of links to content? I've done some Googling, but mainly have found info only on the SQL (2000) security model, which although helpful, isn't exactly what I'm looking at.

Any help would be most appreciated.

:beer:

 

[torpid]

Originally posted by: Patt
I am currently writing a VB application that is connecting to a SQL back-end, and I'm not sure how to proceed (it has been years, literally since I programmed).

If I use Windows authentication for the login to SQL I have a problem because everyone will have access to this program. If I use SQL authentication, I think I'm going to have a bit of a nightmare trying to manage all the various accounts that will be created (400+).

Does anyone have experience writing something similar, or know of links to content? I've done some Googling, but mainly have found info only on the SQL (2000) security model, which although helpful, isn't exactly what I'm looking at.

Any help would be most appreciated.

:beer:

Could you elaborate on the "problem" above? Do you mean that you don't want everyone to have access to it and windows auth would allow it? I don't believe that is the case. You can still specify security levels in SQL server. Windows authentication just means authentication, not authorization. The credentials are authenticated, and then it is still up to your policies in SQL to authorize the user to various entities.

 

[Patt]

I worded that pretty poorly ... everyone in the organization is due to have access to this application. Using Windows Auth., they get access to the SQL server. I can, as you said, authorize the user to various entities after they have been authenticated, but I still want to prompt the user for a UserName and Password to limit some of what they can see.

This is an HR database, so they're really being sticky about only being able to see your own records, and no one else's, unless you're an admin. Fair enough. What I want to do is have them log in, using a forced re-entry of their Windows login in and password, and authenticate them that way, and at the same time, authorize them access to only their particular records. Make sense?

 

[torpid]

Originally posted by: Patt
I worded that pretty poorly ... everyone in the organization is due to have access to this application. Using Windows Auth., they get access to the SQL server. I can, as you said, authorize the user to various entities after they have been authenticated, but I still want to prompt the user for a UserName and Password to limit some of what they can see.

This is an HR database, so they're really being sticky about only being able to see your own records, and no one else's, unless you're an admin. Fair enough. What I want to do is have them log in, using a forced re-entry of their Windows login in and password, and authenticate them that way, and at the same time, authorize them access to only their particular records. Make sense?

Yes. I don't know about forcing them to log in again, but I can say for sure that you are going to have to implement the record level rights programmatically.