Post reply

Disabling Query Analyzer access

  • August 2, 2008 at 10:33 pm

    #122624

    Is there anyway to limit DB accounts so they can only connect through the front end and not via SQL Query Analyzer, etc? Is this possible?? Pls advice..

  • August 3, 2008 at 12:49 am

    #852399

    Hi

    We are fotunate in that we have an ERP system that makes use of a single account to connect to the DB and this is a Windows account.

    So users do not have access to the server / DB at all.

    Is there an application that users have access to? or is the data a collection of tables for reporting?

    What about allowing the users to have their own copy of the data? (Log Shipping?) or nightly jobs that transfer data to "their" server (what frequency of updates do they require?)

    Let us know some more details

    Thanks

    Kevin

  • August 3, 2008 at 1:22 am

    #852403

    The only access the users need to have to the db is through the application, which connects to the db thru the specified app role.This is already present. We need to disble user access through the Query Analyzer only.

  • August 3, 2008 at 6:15 am

    #852419

    Waseem Jaleel (8/3/2008)

    The only access the users need to have to the db is through the application, which connects to the db thru the specified app role.This is already present. We need to disble user access through the Query Analyzer only.

    Why dont you make sure they cant install the sql tools?

  • August 3, 2008 at 8:02 am

    #852425

    Would there be any other way other than not installing query analyzer?

  • August 3, 2008 at 11:33 pm

    #852519

    Hi - you are right - you need to restrict access. Some advanced users even make use of Excel to connect to SQL and run queries that way.

    I would suggest that in terms of security you consider the following:

    * Remove all public access to the DB (no read rights for all users, roles etc besides required app role and admin accounts)

    * If the WinNT account is not bound to the app role then deny access.

    Is the autentication WinNT and users are added to an app role?

    If this is the case then the users do have access and in terms of security you will need to find if SQL 2005 has features that can assist in making the distinction between points of entry and locking down.

    Let us know

    Thanks

    Kevin

  • August 4, 2008 at 1:24 am

    #852535

    If you cant stop people installing query analyser, and you cant lock down the security then you could possibly write a logon trigger that uses eventdata to pick up the application name. If its QA then roll the logon back. Never tried it personally but it should be possible.

  • August 4, 2008 at 2:42 am

    #852546

    Hi - yes that sounds like a good option.

    Will this work for you?

    Let us know

    Thanks

    Kevin

  • August 4, 2008 at 3:43 am

    #852570

    I request you to please give me more details. If it is not a complex one, this would be fine. Please help me with how this can be implemented.

  • August 4, 2008 at 4:22 am

    #852584

    You mention above that the users connect to the application via an Application Role. If this is the case, then isn't the Application Role determining the user's access rights and not the individual user security assignments?

    Don't set any security permissions on the individual users. Just leave them at their default of membership in public. Let the Application Role determine their access to the data. This way they would have no access to the data outside of the application role.

    Next set a password on the application role that is known only to the application.

  • August 5, 2008 at 7:40 am

    #853177

    See BOL for example code for a login trigger. Check master.dbo.sysprocesses program_name column for their spid and you can know if they are using QA or not. If so, issue a message to them and rollback.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 6, 2008 at 11:02 am

    #854009

    Hope this helps (please note, I have NOT tried this, so you will have to do some testing)

    1. Using "Query Analyzer" OR "SQL Server Management Studio" connect to your SQL Server.

    Now run sp_who2 procedure, which shall produce output like the one listed below

    SPID Status Login HostName BlkBy DBName Command ProgramName

    ----------------------------------------------------------------------------------------------------------------------------

    53 RUNNABLE abc\anuser . master SELECT INTO Microsoft SQL Server Management Studio - Query

    Please watch the value in the column "ProgramName", it has the value "Microsoft SQL Server Management Studio", which implies the

    connection is using "Query Analyzer" OR "SQL Server Management Studio"

    2. SQL Server 2005 Logon Trigger - http://qa.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/

    You can use this feature & do the stuff that you were looking for


    paul

  • September 6, 2008 at 4:56 am

    #868126

    Ask the MIS Department to uninstall the SQL Client from the machine

  • September 6, 2008 at 12:23 pm

    #868172

    Hi all,

    I just remember something like... there is some session variable which we can check using trigger each time a table is modified so that we can avoid data being modified directly and to make sure that data can be modified only from application. I forgot which is that variable which we need to check... able to get something about this? This may work for this request...

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

Viewing 14 posts - 1 through 13 (of 13 total)

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