Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Block Users from using sql server query analyzer Expand / Collapse
Author
Message
Posted Friday, July 9, 2010 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 3:36 PM
Points: 3, Visits: 17
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
Post #950142
Posted Friday, July 9, 2010 11:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:13 PM
Points: 101, Visits: 299
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)
Subscribe to my blog
Post #950146
Posted Friday, July 9, 2010 12:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #950154
Posted Friday, July 9, 2010 8:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
Lowell,

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


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #950287
Posted Friday, July 9, 2010 9:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:13 PM
Points: 101, Visits: 299
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)
Subscribe to my blog
Post #950303
Posted Saturday, July 10, 2010 12:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #950314
Posted Monday, July 12, 2010 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 3:36 PM
Points: 3, Visits: 17
Thanks for sharing the info.
Instead of implementing this at server level can we do this on an specific instance?

Post #950946
Posted Monday, July 12, 2010 12:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #950958
Posted Monday, July 12, 2010 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 3:36 PM
Points: 3, Visits: 17
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


Post #950979
Posted Monday, July 12, 2010 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #950983
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse