The Case for SQL Logins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » The Case for SQL Logins - Part Two

The Case for SQL Logins - Part Two

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Sunday, August 18, 2002 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/thecaseforsqlloginsparttwo.asp>http://www.sqlservercentral.com/columnists/awarren/thecaseforsqlloginsparttwo.asp

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #6226

Bill Hallinan

Posted Monday, August 19, 2002 8:43 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, December 17, 2004 8:15 AM
Points: 17, Visits: 1

Thank you for the summary. One note: we were not able to use SQL logins when there was a one-way trust relationship between the domain the server was in (trusted) and the domain the application server was in(untrsuted). In this case we used WA and had to duplicate the username/password in both domains.

Post #39785

bozo7

Posted Monday, August 19, 2002 10:54 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1

I still think a good reason to have WA is that it forces the person to have authenticated to our domain. Using SQL Logins someone could (in theory) execute lots of commands or even use SQL Enterprise Manager and never authenticate to the domain. Not the Win2k or NT domain security is the best in the world either. In my mind it would be more difficult to first join the domain (I could be wrong, wouldn't be the first time).
Ross

Post #39786

Andy Warren

Posted Monday, August 19, 2002 3:40 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Fair point Bozo. Assuming some sort of diligence forcing someone to log in to the network does present a degree of security. If you use the keymaster concept, no one really has the sql login though, so it's really down to someone hacking the login/password, or the keymaster table! Long passwords are still tough to crack - see the review Steve Jones recently posted, and require access to the sysxlogins table to get the value to test against anyway.

Bill, trusts are interesting. Afraid I know more theory than practice on that one, never really had to develop in that environment. Duplicating username/pwd truly horrible, what a pain to have to keep up with that!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #39787

conwaydean

Posted Monday, August 19, 2002 4:25 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 22, 2005 5:49 AM
Points: 11, Visits: 1

It is an interesting article but one aspect that might be more explored is the use of Access 2000/XP as a front end to SQL Server. Access does not encrypt the password when you save it, so that you have a single logon for the application and then you build your own security users / roles set of tables. You then write your stored procedures to check these tables for authentication on each request, so that all access to the system is through Access. But there is a gaping hole in the security because of the way an Access project stores the original connection value.

Maybe there should be a follow up article on Access projects as a UI for SQL and how to tie it down considering the problem I have just pointed out. Writing a dedicated app is not an option because the home of this app is a govt agency and there is no way they want to deploy 10 or more so exes to the workers. Access project offers a simple way (web is out for similar reasons) to have a powerful rich client and no headaches for deployment.

Post #39788

mbando

Posted Tuesday, October 01, 2002 9:13 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, September 20, 2006 7:47 AM
Points: 52, Visits: 1

You make good points and I apologize for my late response.

One point you miss is that you can easily do dynamic searches via stored procs by selecting the data set from the tables or views involved into a temp table within the stored proc, and then executing your dynamic search against the temp table. This eliminates the permissions problem normally presented when using dynamic SQL within a stored proc.

Post #39789

Andy Warren

Posted Tuesday, October 01, 2002 8:43 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Interesting idea. I'll have to think about that for a while!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #39790

Steve Jones - SSC Editor

Posted Wednesday, October 02, 2002 9:36 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

Wouldn't you still need permissions to load the temp table?

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #39791

sevenstar

Posted Tuesday, December 03, 2002 10:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 05, 2003 12:00 AM
Points: 1, Visits: 1

I tend to agree with this article!

This is also a good approach for databases that were not designed "correctly" and have left the gates wide open. It is far easier and cost effective (after the fact) to provide application level security through one login/encrypted password per app (and thereby lock down all users) than it would be to rewrite everything to use stps and views.

I also tend to think it is faster to develop in the first place using dynamic SQL. However, if a company were to pay to have a full time db admin around to setup and manage everything, the other way would not be too bad.

Thanks for the article.

-Rob Hajicek
SevenStar Technologies
www.SevenStarTech.com
rob@SevenStarTech.com

Post #39792

« Prev Topic | Next Topic »

Permissions