June 25, 2002 at 8:47 am
Lots of good points, need a solution. I wrote a script to watch sa logins and map to hostnames if an unfamilar host name uses sa i send a page with the host name, spid and time occurred. How about a password cracker ran against sql login? Thats possible right does not lock out after X attemps. Also enabled failed logins and send them via email from the sql error log with FINDSTR from dos.
June 25, 2002 at 11:35 am
Interesting topic a few things...
Wouldn't it be nice if there was an encrypted data source option, or some tool that provides an easy way to store a user/password encrypted in the registry and works with ADO..
I like the app role over the SQL Login because of the separation of authentication and permissions. Even if someone does steal the app role password, they still need to be able to connect to the database. Also very useful if you want to give certain access based on NT ( say the ability to run the reporting stored procs or view tables ) but only allow updates through the application which has the role password.
June 25, 2002 at 11:55 am
Gotta agree with Andy.
Would love to use WA, but there is always something...that is the issues Andy outlined.
Actually, cannot think of a situation where we used WA for any web applications.
Plus, I hate it when a database is not "owned" sa, but rather by the WA user. [just one of my pet-peeves]
What's the business problem you're trying to solve?
June 25, 2002 at 3:15 pm
Well said Andy, and I totally agree. One thing I always ask when I'm at a client when the topic comes up -- how well is your NT/2000 environment managed? I don't know how many times I've seen someone using WA (because MS recommends it), yet the security policy does not expire passwords, allows any size password and repeating passwords, etc. Even those companies that do have solid security policies have poor ongoing practice where workstations are left unlocked, user accounts are shared, etc.
In a perfect world, WA is better, but it's critical that you evaluate your NT/2000 security before making the selection.
Personally I'd rather see stricter security for native logins -- expiring passwords, strong password enforcement, etc.
My 2 cents. Well done Andy!
June 25, 2002 at 3:20 pm
Totally agree on the SQL Logins. What about locking the account after 3 failed attempts!!!
Feel free to submit these to sqlwish@microsoft.com. It goes to the dev team.
Steve Jones
June 25, 2002 at 3:37 pm
"Feel free to submit these to sqlwish@microsoft.com. It goes to the dev team."
Been done, repeatedly 🙂 The real question is, if a hardier SQL Authentication engine were available, would people use it? Oracle has basic security policy capabilities build in, but I have yet to see an environment where it's actually implemented.
Ok, that makes 3 cents. Again, great article Andy, keep it up.
June 25, 2002 at 4:27 pm
Thanks again for everyones comments so far. Im pleased to see that a few of you agree with me!
Andy
June 26, 2002 at 5:24 am
Thanks for bringing out this article. This has set me thinking - we are about to finish an app (SQL being the RDBMS) and now this has set us thinking!!
June 26, 2002 at 7:29 am
I'm looking forward to your next installation of The Case for SQL Logins. I've been the DBA for only 8 months and read everything I can get about security. Currently, we only use Windows authentication (trusted account) for those who have Administrator access to the database. All others must have a SQL login.
-SQLBill
June 26, 2002 at 9:31 am
Thanks for bringing up the subject. Our team uses mixed mode. We have a web application written in Cold Fusion. We have to use SQL logins for Cold Fusion to talk to SQL Server and for remote database administration at our hosting facility.
We recently got a document from the government saying SQL Servers not running WA only are a security risk and would be considered a fairly serious finding during an audit.
We have been looking at ways to get WA to work with remote administration and interaction with coldfusion, but there is no clear, easy solution. Seems like there should be.
June 26, 2002 at 10:08 am
June 27, 2002 at 1:21 am
I agree WA is very rarely secure. I don't know about you guys but I don't want any NT Admin to be able to hijack my databases. Also you have no control over someone that leaves their workstation unlocked. Then the cleaning crew that comes in late at night can check out your DB too! Nope not for me at all.
Also Paul Thornett has a very good point about MSDE. Besides the obvious issues with the BUILTIN\Administrators group being setup as an sa by default, MSDE also leaves the sa password blank, binds to all IP's on your box, and runs under a system account. You may as well be posting a big fat sign for anyone to attack your machine. Microsoft should force an sa password and bind it to a loopback ip (127.0.0.1), that way MSDE doesn't continue to be a SQL worm portal.
June 27, 2002 at 7:05 am
I think people are messing things up here.....
There is a way to log in to a SQL Server ( SQL login or WA) but there is no reason whatsoever to give any user exept some SA
rights in a databse in an application.
I do believe most data is queried by applications.......
In that case let the app use an application role.
I do really do believe there is absolutely NO reason to give a user that works with an application rights in a database other than with an app role.
So when we focus on applications an d logins to SQL we really should keep in mind the there is only one way.
1. User gets Access to a SQL server (SQLA/WA)
2. User gets the rights assigned to an approle in a specific database.
June 27, 2002 at 8:41 am
We use WA and approles where we can, but that's not too many places. Packages and other 3rd party tools don't really seem to like WA, so we don't really have a choice. As dba I really like to know who's connected to the db when there are performance problems, so approles are satisfactory only in controlled situations.
June 27, 2002 at 10:11 am
I think that this is a good article about a very hot issue. My opinion is that the administrator has to evaluate carefully what risks are involved before deciding the way to go. Not so easy to ponder sometimes, but worth taking the time to do it.
Viewing 15 posts - 31 through 45 (of 75 total)
You must be logged in to reply to this topic. Login to reply