Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Kelley » SQL Server Security: Login Weaknesses
Add to briefcase

SQL Server Security: Login Weaknesses Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
K. Brian Kelley
Posted Saturday, July 19, 2003 12:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Wednesday, May 08, 2013 5:14 AM
Points: 6,583, Visits: 1,787
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityloginweaknesses.asp

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #14355
rainsley
Posted Friday, August 15, 2003 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 1:27 AM
Points: 5, Visits: 7
I write apps that use SQL server in small companies -- we expect them to not be generally available to internet users. My concern is that operator X complains that they want some change that is not being approved or is being delayed -- has a buddy that "knows" MS SQL. If the operator has authority to access the tables via Enterprise manager, query analyzer etc, these "helpful" buddies can cause a lot of problems. I prefer the application to log into the SQL Server with a password restricted to the database it needs and that the SQL password not be relesed to the end users (since the app know it) -- let the windows security control access to my Application not MS SQL Server.


Post #71308
K. Brian Kelley
Posted Friday, August 15, 2003 12:25 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Wednesday, May 08, 2013 5:14 AM
Points: 6,583, Visits: 1,787
This is easily accomplished in one of two ways.

(1) Service accounts under which the components run (Windows authentication).

(2) Grant users the right to connect to the database using Windows authentication but grant them no rights (and assign nothing to public). Use application roles.

The main concern here with SQL Server authentication is someone who has the capability to sniff does so with malicious intent, even on the internal network. You can mitigate this by either encrypting between client and server (SSL/IPSec/Multiprotocol) or by using Windows auth.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #71309
Andy Warren
Posted Saturday, August 16, 2003 7:43 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384
I'm still taking the other side on this. Using components with service accounts - to me - increases the complexity for simple apps and to me sidesteps part of what NT authentication is supposed to do - let the user have access - in favor of funneling all access through a component. I dont know that its wrong (in any sense of the word) and it will work.

I agree about granting nothing to public, hate application roles. Good idea, a bad implementation in my view.

If - make that a BIG IF - you're less concerned about packet sniffers than you are a user connecting via MS Query, VBScript, etc, I think a sql login makes apps easier to manage and more secure.

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 #71310
K. Brian Kelley
Posted Saturday, August 16, 2003 11:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Wednesday, May 08, 2013 5:14 AM
Points: 6,583, Visits: 1,787
I'm in agreement with you, Andy. I don't much like service accounts and I hate application roles. Maybe hate isn't a strong enough rule... if you try to do things like resource pooling, app roles really makes a big mess of things.

Rainsley, I left out another option. I typically drive all my data access through stored procedures, so even if they go to "touch up" system, they can only do so through the sprocs. Meaning they can't do anything other than what they normally can do in the app itself. Ownership chains rule.



K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #71311
rainsley
Posted Monday, August 18, 2003 7:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 1:27 AM
Points: 5, Visits: 7
I much appreciate this thread!
I indeed use stored procedures for updates, deletes and inserts and some of the more complex select queries. However, many of the queries need more flexibility so I am often writing queries on the fly from QBE screens. I am substituting all apostrophees in text fields when I do that... So far all is well. Do you have any thoughts about how to make these select queries any safer?


Post #71312
K. Brian Kelley
Posted Monday, August 18, 2003 8:46 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Wednesday, May 08, 2013 5:14 AM
Points: 6,583, Visits: 1,787
You might want to take a look at some of the dynamic SQL articles by Robert Marda. He covers how to get more flexibility in your stored procedures without having to resort to granting access to the base tables.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #71313
Andy Warren
Posted Tuesday, August 19, 2003 4:52 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384
Definitely read all you can on dynamic sql, not just for security but performance too. One thing you can do quickly, check for semi colons - the most common hack is to add a semi colon to the end.

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 #71314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.