Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The Case for SQL Logins - Part Two Expand / Collapse
Author
Message
Posted Sunday, August 18, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 11:48 AM
Points: 6,783, Visits: 1,879
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #6226
Posted Monday, August 19, 2002 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, August 19, 2002 10:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Monday, August 19, 2002 3:40 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 11:48 AM
Points: 6,783, Visits: 1,879
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #39787
Posted Monday, August 19, 2002 4:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, October 1, 2002 9:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Tuesday, October 1, 2002 8:43 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 11:48 AM
Points: 6,783, Visits: 1,879
Interesting idea. I'll have to think about that for a while!

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




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #39790
Posted Wednesday, October 2, 2002 9:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:52 PM
Points: 33,189, Visits: 15,327
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
Posted Tuesday, December 3, 2002 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 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 »

Add to briefcase

Permissions Expand / Collapse