Block Users from using sql server query analyzer

Question

Block Users from using sql server query analyzer

some1indark

Old Hand

Points: 393
More actions
July 9, 2010 at 11:46 am

#223749

Hi All,
I am working on a windows app. we use windows authentication to connect to sql server 2005 so all active directory users can login using there credentials . But I want to block the users from directly connecting to database using any query analyzer. I know we can create a user (eg: "testuser") and list that user in the app.config so that your application connects to the database as "testuser" but i don't want to list the username & the password in the app.config as he can always look into the app.config and get connected through a query analyzer. (even though I can encrypt the user name and password in the app.config) but still that would not be too hard for a techie to decrypt it back.
Please let me know your thoughts.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Tara Kizer SSCommitted Points: 1789 More actions · Answer 1

If you are using strong encryption, then a techie can not decrypt it.

There is no way to prevent users from connecting via Query Analyzer or any similar query tool. What we do instead is only grant execute on stored procedures. We don't allow any direct table access as all data access is through stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog

Lowell SSC Guru Points: 323504 More actions · Answer 2

the answer is yes and no.

once I have a username and password, I'm good to connect with anything i can get my grubby fingers on...SSMS, a program i write, Access,Excel, Oracle's SQL Developer...even a vbs script...you name it.

the only way to prevent users from using a specific application to connect is to use a logon trigger...but that is based on the application name, which can be faked by any developer who has access to modifying the connection string.

here is an example from a previous thread, where someone wanted to prevent Access or Excel from connecting to the database. all you'd do is change the name to "Microsoft SQL Server Management Studio - Query " or "Query Analyzer" or any other app to exclude..excell, access, LinqPad....

you don't have to test just the application name...you could also make sure they were part of an admin group or some role as to whether they can connect with SSMS or not. for example you might want to allow anyone who is in a serverrole to connect anyway

note a logon trigger could prevent EVERYONE from connecting if you mess it up....

here's goes:

--Prevent access from Excel

CREATE TRIGGER logon_trigger_not_from_excel

ON ALL SERVER FOR LOGON

AS

BEGIN

IF APP_NAME() LIKE '%excel%'

OR APP_NAME() LIKE '%Query Analyzer%'

ROLLBACK

END

--Try to connect from Excel/whatever app...you'll get an error...not very descriptive, but an error preventing login.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95409 More actions · Answer 3

Lowell,

This is neat. I'm storing this in the "bag-of-tricks"!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara Kizer SSCommitted Points: 1789 More actions · Answer 4

Agreed, didn't realize you could do that!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog

Lowell SSC Guru Points: 323504 More actions · Answer 5

here's another example i posted that has additional logic, and check out the thread it came from for more info:

http://www.sqlservercentral.com/Forums/Topic927131-359-1.aspx

in this case, i'm trying to prevent SSMS and also unauthorized 'sa' usage.

it's got some exceptions, like SSMS is ok if the user is My domain login or sa, and also that yhose logins are connecting from a specific machine.

--Prevent access from SSMS

--drop TRIGGER logon_trigger_not_from_SSMS on all server

CREATE TRIGGER logon_trigger_not_from_SSMS

ON ALL SERVER FOR LOGON

AS

BEGIN

IF APP_NAME() LIKE '%Microsoft SQL Server%'

BEGIN

IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing

BEGIN

--only allowed from my host machine

IF host_name() !='STORMDEV'

BEGIN

RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)

ROLLBACK

END --host name check

END --suser_name check

ELSE

BEGIN

RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)

ROLLBACK

END

END --app name check

ELSE

BEGIN

RAISERROR('SSMS connections are restricted on this server.', 16, 1)

ROLLBACK

END

END --trigger

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

some1indark Old Hand Points: 393 More actions · Answer 6

Thanks for sharing the info.

Instead of implementing this at server level can we do this on an specific instance?

Lowell SSC Guru Points: 323504 More actions · Answer 7

this is a per-SQL-Server installation/instance trigger, not a per-machine trigger...

so if you have a server named "PROD", and it has a default SQL instance, and two named instances, ie "PROD\SQL2005" and "PROD\SQLEXPRESS"

you would need to create the logon trigger 3 times, to cover every sql instance on that one machine...once connected to PROD, the second time to PROD\SQL2005, and finally PROD\SQLEXPRESS.

does that help flesh out it's scope of a logon trigger for you?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

some1indark Old Hand Points: 393 More actions · Answer 8

Thanks a lot for the quick turn around.

I guess my question was. If I have 5 databases on my server & I want this trigger to only on one database.

since we are doing "ON ALL SERVER FOR LOGON" this does restrict all logins to that server. Instead I want to protect only my database and let the user login to other databases in the server.

Servername : prod1

databases : db1, db2, db3, db4, db5

I just want to restrict logins to db1 I am not concerned about the other databases.

Thanks

Lowell SSC Guru Points: 323504 More actions · Answer 9

ahh... no i don't believe so...the database is not available, or no definitive to the to the logon connection...you know i can connect to master, but query db1 using 3 part naming conventions, ro changing my connection to another database....

in this case, you cannot be selective and say "you can use SSMS, but only on DB2 and db3, and not on db1.

i don't even think you could switch to a database DDL trigger either...those types of triggers that would be testing for specific commands, Like CREATE TABLE and stuff.... , not application_name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ross McMicken SSCarpal Tunnel Points: 4395 More actions · Answer 10

In our environment, we restricted the use of tools like Query Analyzer to those folks who needed them to do thier jobs. Normal users can't install any of the SQL tools. Actually, none of us can install anything that hasn't been specifically authorized - there are all sorts of group policies and such that enforce the restrictions.

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 11

There is nothing specific at the database level which can be used to restrict such access. Within SQL Server (and only 2005 SP2 and up) you're limited to a LOGON trigger, as previously demonstrated. But there is nothing akin to a database-level trigger.

On a related note, if there is a hard requirement in your organization to ban certain apps in general from particular groups of users, but the users are capable of doing installs (because they are admins on their own systems), look at implementing a GPO to restrict the programs. That's beyond the scope of your average SQL Server DBA, but directory services and senior Windows sysadmin types should be able to get this done.

K. Brian Kelley
@kbriankelley