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


SQL Server Security: Login Weaknesses


SQL Server Security: Login Weaknesses

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25700 Visits: 1917
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityloginweaknesses.asp

K. Brian Kelley
@‌kbriankelley
rainsley
rainsley
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

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



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25700 Visits: 1917
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
@‌kbriankelley
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26027 Visits: 2749
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25700 Visits: 1917
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
@‌kbriankelley
rainsley
rainsley
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

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



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25700 Visits: 1917
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
@‌kbriankelley
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26027 Visits: 2749
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
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