Disabling Query Analyzer access

Question

Disabling Query Analyzer access

Waseem Jaleel

SSCommitted

Points: 1706
More actions
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..

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

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

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 1

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 2

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.

Animal Magic SSChampion Points: 13964 More actions · Answer 3

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?

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 4

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

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 5

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

Animal Magic SSChampion Points: 13964 More actions · Answer 6

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.

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 7

Hi - yes that sounds like a good option.

Will this work for you?

Let us know

Thanks

Kevin

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 8

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.

bbr SSChasing Mays Points: 613 More actions · Answer 9

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

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

sqlemail@yahoo.com SSC Eights! Points: 919 More actions · Answer 11

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://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/

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

paul

mohammed jameel-448990 SSC Rookie Points: 32 More actions · Answer 12

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

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 13

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