April 19, 2011 at 12:35 pm
Fairly common scenario: My existing application has an SQL Server back-end, and business logic on the front-end. To connect to the App, obviously a connection to SQL must first be made. After that, the business logic fires up, and requires the user to enter a user name and password (this info is stored in an SQL table).
Right now most of the time, the connection to SQL is anonymous... in other words, either using an SQL Login, or the IUSR_SERVERNAME account through IIS. But we also have some clients who want to use their Windows Account for single-sign on.
We can add these Windows accounts as allowable SQL Logins, so that they get access to the SQL Server through that Windows account. But then they still need to enter their table-based user name and password for the app to allow them access. Obviously, entering auth information twice (Windows Account, and Application name/password) is frustrating.
The idea is to modify the SQL table that holds the Application login information. We would add a field to store the SYSTEM_USER data. When someone connects with that Windows Account, it would find that corresponding record in the login table, and that would tell the app what functions can be run. However, we also need to leave to option of doing it the old way as well.
My question is... is this a viable option? So if their record in the login table matches their Windows Account, it logs them into the app automatically. But if they connect to SQL via a generic method, then they have to enter a name/password from the app.
I am looking for what I don't know. Is this going to open up any security problems? Thanks in advance!
April 19, 2011 at 12:44 pm
tksitp (4/19/2011)
Fairly common scenario: My existing application has an SQL Server back-end, and business logic on the front-end. To connect to the App, obviously a connection to SQL must first be made. After that, the business logic fires up, and requires the user to enter a user name and password (this info is stored in an SQL table).Right now most of the time, the connection to SQL is anonymous... in other words, either using an SQL Login, or the IUSR_SERVERNAME account through IIS. But we also have some clients who want to use their Windows Account for single-sign on.
We can add these Windows accounts as allowable SQL Logins, so that they get access to the SQL Server through that Windows account. But then they still need to enter their table-based user name and password for the app to allow them access. Obviously, entering auth information twice (Windows Account, and Application name/password) is frustrating.
The idea is to modify the SQL table that holds the Application login information. We would add a field to store the SYSTEM_USER data. When someone connects with that Windows Account, it would find that corresponding record in the login table, and that would tell the app what functions can be run. However, we also need to leave to option of doing it the old way as well.
My question is... is this a viable option? So if their record in the login table matches their Windows Account, it logs them into the app automatically. But if they connect to SQL via a generic method, then they have to enter a name/password from the app.
I am looking for what I don't know. Is this going to open up any security problems? Thanks in advance!
Sounds like you may already have a security problem, depending upon how you store those passwords. If those passwords are unencrypted, then you have issues, especially if you open up your DB to Windows Auth. Users with access via Windows Auth could potentially use Access or Excel to directly connect to your DB, query the table, and get the credentials needed to log on to the app. Obviously this would be a bad thing as they could impersonate other users and do some serious damage.
I would make the two methods completely independent, or better yet, do away with the SQL Auth methodology.
April 19, 2011 at 12:55 pm
I believe your concerns are already addressed. The passwords in the login table are hashed/salted. Additionally, any access to the database through these accounts is limited to running stored procedures only. There is no querying of the database tables (with the obvious exception of elevated users like the dbowner). The only permissions granted for these users are execute on the relevant SPs.
So back to my original question (unless there is another issue you see). Is what I am describing a valid way of handling this scenario?
April 19, 2011 at 1:59 pm
Thanks jeff.mason. Oh, one other thing... we are working towards getting rid of SQL authentication, and we are looking at this as a step in that direction.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply