Post reply

security question

  • December 18, 2006 at 5:43 pm

    #61736

    I have an application that uses a sql database, is there any way to keep sommeone from connecting to it with query analyzer and runninga query?  they need to access the database through this application but I need to know if i can prevent direct access with qa.

    thanx

  • December 19, 2006 at 12:53 am

    #678859

    I don't think you can prevent some one using the QA but if they are using any perticular login you can deny that login...

    Application roles may help you in this regard...

    Check BOL "Establishing Application Security and Application Roles" topic...

    MohammedU
    Microsoft SQL Server MVP

  • December 19, 2006 at 6:44 am

    #678900

    Application roles are designed for just such a situation. The user has permissions that are only activated when the applilcation supplies the password for the application role. Outside the application the application role does not exist so the user has only limited permissions (i.e. none) when not connecting through the application.

  • December 19, 2006 at 8:24 am

    #678925

    Third vote for app roles. Andy Warren has written about them and this is why he doesn't like Windows auth for anything other than login. Prefers to have them connect and then use app roles for all security.

  • December 19, 2006 at 10:12 am

    #678954

    Also, don't install the Client Tools on their system. That way they shouldn't have Query Analyzer available. And if they aren't an admin, they shouldn't have the ability to download programs onto their computer (okay, that's in a perfect world and not always the case).

    -SQLBill

  • December 20, 2006 at 9:45 am

    #679159

    And install a virus on their computer that causes their keyboard to emit an electrical shock if they try to install the client tools themself.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • December 20, 2006 at 9:51 am

    #679164

     Now that idea warrants some more attention from my part .

  • December 20, 2006 at 1:05 pm

    #679205

    If your application is some website or citrix published and

    if you have access to secpol.msc on the DB server you can also restrict access to sql server port for particular IPs like web or citrix server and ofcourse DBAs.

  • December 20, 2006 at 1:51 pm

    #679223

    thanx to all for the help

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

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