Using Standard SQL Security from Web Apps
-
Question about any potential security holes when using a standard sql login from a web page. We have developers who want to build a web app that will prompt for a login and password. The values (login and password) the user types in will actually be used in the connection to the SQL Server. I have recommended that developers store any login info in a user table (and verify the login programatically) and connect to the SQL Server with an "application login" where the password is encrypted and stored in a file or the registry on the web server. In either case the connection to the web server will be through an HTTPS connection.
Question: Are there any security holes by using the login info entered by the user to be the login that is connecting the web server to the SQL Server?
-
It complicates your security management on the SQL Server side tremendously. That means a greater chance of giving one of these logins more access than it actually needs. Also, it can push the security lockdown to the database server rather than the app by doing it this way, which is more a question of what your organization is most comfortable with.
K. Brian Kelley
@kbriankelley -
If this app is on an intranet and any of your users can get a client connection tool (like Query Analyzer or the free Toad for SQL Server), they can connect directly to your database and do whatever they wish, to the extent of the permissions granted. You can't assume that they would only do the things offered by the app. They may see or change data that the app wouldn't have given them access to.
-
Tom,
Do you mean "application role" like SQL Server Application role, which is a good solution, or "application login" which is a standard login and is generic? I would not use generic logins.
Glen,
I have one third-party app that modifies passwords on the way. It submits a different password to the database than user types in. This way users can not use their logins outside the application. Logins are created through the application too.
What I definetly would not do is to limit user rights on the application level while his login has more rights on the database level. I know several third-party tools working like this. Not a good idea. The application and database rights should be of similar access level
Yelena
Regards,Yelena Varsha
-
Yalena--I strongly agree with your recommendations, if individual users (rather than a program) are to be granted db permissions. Unfortunately I've seen too many 3rd-party apps that aren't set up as nicely as the one you described.
-
I call it proxied login. I am making the assumption the webusers do not need the actual logins to the database. They just need to be able to work with the database. So you have a table called app_user, and that is the login info they use to login to the app. In this table you store the actual db_login. You hard code an account to be used strictly for authentication against app_user and once authenticated you look up the db_user and login to the sql server. This way the app manages the login on the webserver and the user never knows the db login.
-
Your description is the actual way I would like to implement the application. Also, according to ASP.net documenation the most secure "application login" is to use a domain account and Windows Authentication to the SQL Server instead of a standard login.
Another recommendation (among many) on MSDN is to also encrypt your connection string. If I did that and tried to use a login entered on the web as the login that would be connecting to the SQL Server I would not be able to encrypt the string because it would be dynamic.
If standard SQL security is used (whether a user provided login and a hard code login) I also read in MSDN (and should have thought if it myself) that the password is traversing the network in a "free text" form (unencrypted).
All of the comments and recommendations have been helpful. My orgininal question was intended to determine if there were any "extra" security risks associated with using a user entered login and password in the connection string to the SQL Server besides risks such as SQL Injection. I also realize that verfying inputs is a key part of ensuring security integrity. My intuition (which is not reliable) was telling me that with this scenario (user input for connections) there was addtional risks because if a hacker gets a hold of an acutal login instead of piggy backing on a connection (SQL Injection) there might be more security risks.
Thanks for your input.
-
Francesco,
How do you store the actual DB login and password? Do you use sort of encryption? If any passwords are stored in the DB, is it the same DB as the the one they are trying to access through the application or is it a special "logon db" on a separate computer? Anyway, did you think of pasword-protecting backups then? Backups are very readable using Notepad.
Yelena
Regards,Yelena Varsha
-
It's in the same db. If they can get to the backup files, I would be more worried about physical security rather than data. Since our data is not really confidential and is not personal, it does not require stringent security measures. The db accounts corresponding to the web users are all ip locked to specific webservers anyway. Backups are password protected, so is the rar files of it (yes, different password).
I don't know why I bother to keep backups, since there is no way I am going to restore a db over a day old anyway
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply