SQL Server Security: Login Weaknesses

  • Comments posted to this topic are about the item SQL Server Security: Login Weaknesses

    K. Brian Kelley
    @kbriankelley

  • 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.

  • 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

  • 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/

  • 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

  • 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?

  • 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

  • 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/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply