Block users to access DB using Enterprise Manager and Query Analyzer
-
OS: Windows 2003
SQL : MS SQL Server 2000, SP-3a
In our system, SQL client is installed in some users machine and they assigned to DB_datarader and DB_Datawriter roles also.
We need to protect these users to access Database using SQL server Enterprise Manager or SQL server Query Analyzer. They have to access the database only with Application software.
We are using 3rd Party application software. How I can use “application role” to these users, because we can not change the 3rd party application software.
Is any other way to protect the access of Database with Enterprise Manager and Query Analyzer
Regards
Mathew
-
One way would be to schedule a (sql agent) check - every 2-3 minutes let's say - for the program name the users are connected from. In case the program name it's "Enterprise manager" or "Query Analyzer" then issue a sp_kill for the spid in the query results. Below it's the sql 2005 version of finding the program name, host name and spid. You would need to translate it to sql 2000 (sorry, there's been more than one year since I haven't touched sql 2000):
select
sysl.loginname,sysl.dbname,sysp.spid,sysp.hostname,sysp.program_name
from
sys.sysprocesses sysp
right
outer join
master
..syslogins sysl
on
sysp
.sid=sysl.sid
where
sysp.program_name='Microsoft SQL Server Management Studio - Query'
Hope this helps.
-
Its the downside of NT authentication; you're granting access to the user rather than the app. The technique mentioned above will work, but certainly doesnt stop them and its easy to work around - just use MS Query, or write the queries using ADO and VBScript.
The 'right' way to stop is to put up a firewall that denies the users access on port 1433. That means apps have to be changed to go through an app server that can in turn connect to the db server. Not as hard as it sounds, but not an option for 3rd party apps in most cases. The alternative is to use SQL logins for your apps but not provide those logins to the users. That means at least seriously obfuscation, better case is to use an NT login to call a proc that does a look up and return the password - which obviously isnt without its own risks.
-
You may create INSTEAD OF triggers on crucial tables and/or add application validation into your stores procedures:
IF suser_sname() IN (-list of logins-)
AND APP_NAME() 'Your Application Name'
RETURN
Of course the users should not have right to modify procedures.
_____________
Code for TallyGenerator -
Hi All,
Thanks for your suggestion. Is any driect way in SQL Server to Block users from the server. Like disallow one specific IP from SQL Server.
Please advice
Regards
Mathew
-
Firewall.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply