SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Case for SQL Logins - Part Two


The Case for SQL Logins - Part Two

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11873 Visits: 2730
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
Bill Hallinan
Bill Hallinan
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.



bozo7
bozo7
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 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



Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11873 Visits: 2730
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
conwaydean
conwaydean
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.



mbando
mbando
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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.



Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11873 Visits: 2730
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63865 Visits: 19116
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
My Blog: www.voiceofthedba.com
sevenstar
sevenstar
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search