The Case for SQL Logins - Part 1
-
How does ORACLE handle authentication?
-
FINALLY! I'm glad to see someone finally wrote an article with the case for using SQL logins. People where I work have shown me articles in SQL/VB/ASP-type magazines saying you should only use WA... but there are times when you need to use a "program account" to give a user elevated permissions in a database through accessing a program. In one of my databases, users are allowed to change data through a specific web application that I wrote, and only through that application (the tables are complex for end users to be looking at, and we also need some specific change auditing). They also need to be able to connect to the database with read-only priviledges to look at the data. Fortunately, this is pretty much what they've done for the past thirty years with System 1032 on the VAX (using a program account), so it wasn't hard to make the case for SQL logins to my supervisor.
Side note: Something is weird with the forum... I'm not seeing anything on pages 2-4, and the screen for entering a message has a black background... is anyone else seeing this?
-
I support what the article recommends.
You have to be careful using WA for internet and intranet apps because the user that IIS and now ASP.Net run under is sometimes a real mystery. I've seen different behavior based on the connection protocol (TCP/IP vs Named Pipes), IIS Isolation Level, and Active Directory vs NT Domain.
-
I would rather have WA security. You can then manage users in the Domain through Groups, and grant WA access to Groups only.
Another benefit is when you move a database using detach/attach, if all your users are WA, you do not have to worry about remapping users due to changes in SQL Security.
-
Nice article Andy. People hate entering and keeping track of multiple passwords. One study said that if users have to remember more than 3 passwords they will write it down somewhere handy. For some people one is too many. WA assists us here, because we at least know who the person is.
My preferred option is a two prong defense. Using WA we identify and verify the user. By using WA permissions to control access at the application executable we also restrict the application to only those authorised (works for intranet and applications) security. Then by using approles we lock access down to that application. Only the developers and dbas know the approle password.
In reality, most of the databases I look after are vendor supplied and use a single sql login / password. Is it a problem? Apparently not, as management is quite prepared to accept the extra risk.
At least with SQL Server we have several options to choose from. On other database systems you can only use the login / password.
-
This article has both Pro's and con's but as a practice the applications are developed using Windows Authentication it is really hard to digest this so easily .
--Ramesh
Sydney
Ramesh -
We have not made much use of WA yet since we have applications managing the user security. As we move to terminal server we are starting to see a lack of visibility in who is accessing the database, because we see the application logon, and the terminal server workstation name for all users!
-
My scenario:
Our intranet (running on IIS) uses WA. From there we connect to the db using a common sql login. As far as I know, this results in having no way to tell which specific users are accessing the db. Since we have lots of speed issues, I think that it may be useful who is making what requests that are causing the slowdown.
So in this scenario, is it worthwhile connecting to the db using WA? Or is there another solution?
-
just my two cents... we can't use application roles (application covers multiple databases) and we utilize wa because the connection info isn't stored anywhere (not passed across the wire in an unencrypted format). we only use sp's for access, no direct sql is allowed (currently we have over 2600 sp's in 2 db's) we are in a domain, but don't use domain accts for the web app, get connection pooling through the use of a local nt acct (use same local nt acct on sql server and web servers), all access from inside (not through web app) is handled by nt groups with specific rights to read/write data to the db's. Web app handles all application security because of complexity of the application. user's login with username and pwd(encrypted) and the app set's their rights. have approx 5k users, and they are self managing through the app.
-
Where I work we designed our application from the ground up to use SQL Security. Even though at this time all users connect to a domain control and could use WA, we wanted to allow users to get at the data via the internet (no domain controler needed), but at the same time know who the heck was connected to the database (for figuring out who may be responsible for any blocking or locking problems that arise from time to time).
There was one person who posted and said that WA is not as good as SQL Logins for connection pooling. I disagree -- Using WA for connection pooling has an insigificant performance hit (when it first goes to the domain controler to authenticate) but from the point of first authenication on it does not constantly hit the domain controler.
WA is better for connection pooling because for connections to be pooled the connection string must match EXACTLY for each connection made to use the pool. With SQL login the connection string could differ from user to user. It is more likely that each connection string would be more exact with WA.
Usually connection pooling is a good idea for a web server that needs ad hoc access to the database, for other applicaitons where the user is known it may be a better choice to use logins.
Edited by - RyanNerd on 10/30/2002 3:16:45 PM
-
Great article. Disagree with most of it, but it made me rethink a lot of things. On my team only developers, the network manager and the CTO have access to the database. Everyone else goes through the application. Because of that, we used mixed authentication. Developers us NT Authentication and are encouraged to lock their computers. The app which provides data for the user uses a sql login. And, the SQL login is only known to the DBA, the network manager and the CTO.
It works out very well when a developer leaves the company. No need to change anything as once the person loses access to the network, they lose access to the database.
As for the users, why would they want access to the DB when they can get it with all the formatting and tinsel that they crave through the application?
There have been rare cases where a non-Developer proved that they needed and were able to handle access to the DB, so we gave them a login with very strict permissions.
-
I appreciate the comments. So which part did you disagree with? As far as the rare power user, you went with sql login over NT?
Andy
-
I recently had an experience where not all the applications from a medium size (300 Million a year) software company would work with Windows Authentication even though their main product would. Setting up SQL logins solved the problem. The software company insisted that this was not possible but it worked.
-
Hi, Yes you are right about WA authentication. It really creates a security problem more over i have the experoience of user opening the database using his login ID of Win 2k. So i always suggest to use the SQL security as its better than teh windows security.
Thankx
Jeswanth
--------------------------------
-
As always the straight way has some curves.
Indeed, ms-office / vbs / ... are often not taken into account when considering security.
IMO WA is very good if it is no problem if users access the db directly or without the predesigned application.
Application roles work very nice, but are often rejected because you have to disable connection pooling to be hassle free. (and how do you maintain their password)
SQLAuth is just like with other rdbms-providers. The weak spot with sqlserver is that you don't have any policies. And how often do you see a post-it-note on a desk with all application logins and passwords ?
Another point wit sql-auth is ... btw ... who has networkcommunication encrypted ??? ..... and also on your wirless network ???
How many times is security part of the developmentphase of a project and not just a concern to the SO or the DBA. How often does it occur that the dba is considered as the "pain in the ***" because she/he keeps on nagging about security, not using dynamic sql, ... ?
Keep in mind most security issues come from within. (ethics, trials, ...)
Great discussion goin on
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 61 through 75 (of 75 total)
You must be logged in to reply to this topic. Login to reply