how to keep users out of isql/w? 
Author Message
 how to keep users out of isql/w?
In version 6.5 of MSSQL how do I keep users from logging into the
database using client utilities such as ISQL/w and Enterprise Manager? I
want them to be able to get in via APIs written for them but I don't
want them to be able to access the database directly using the client
utility tools. Anybody know of a way to do this?

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!

Fri, 20 Aug 2004 07:32:05 GMT
 how to keep users out of isql/w?


Q.  How can I restrict access to my SQL Server so that it only allows certain
programs to connect?  (I don't want my clients using Access to hack data

A.  SQL Server 6.5 and below have no built-in tools/facilities to do this, nor
does it allow a stored-procedure to be run on client connection that could be
used to do this.  Of course if all your data is protected correctly with
SQL/Integrated security and all auditing/business rules are held with
constraints/triggers then you won't need to worry as it doesn't matter what
tool people use to connect.  However, in a real-world application, these
pre-requisites are unlikely to be there.

SQL Server 7.0 has the idea of application ROLES, as well as user roles.  If
you have SQL 7.0 then use application roles.

If you are still on 6.5 or below then you can try one of the below - but they
are all kludges: -

1.  Write your own ODS Gateway and point the clients at that instead of the SQL
Server - the ODS Gateway will then do the checking.  However, there is nothing
stopping clients figuring out the correct SQL client-config entries to point
straight at the SQL Server.  There are examples of ODS code in the SQL
Programmers Toolkit - available for free download from the Microsoft web site.  

2.  Write a constantly running/scheduled stored-procedure that checks the
relevant column in sysprocesses (program_name), and then issues a KILL command
for any processes that should not be running.  This way allows people to
connect and possibly make changes before they are spotted and killed.

3.  Change servers to use standard security. When the apps connect they request
a trusted connection and get dumped into a new database - the only one their
trusted account can connect to. This database contains a table holding a lookup
between the users NT account and an alternative login name, password
(encrypted) and perhaps server and database name to use. The only runnable
object is a stored procedure that returns the user's rows from this table.  The
front-end calls the stored procedure, decrypts the password and re-connects to
the server.  One downside to this sort of approach is that the users "real"
name and password could potentially be cracked with an ODBC trace (several
versions of the ODBC SDK tools allowed passwords to be displayed) or network

4.  Another option is to place a CHECK constraint on the sensitive tables which
validates the application name. Something like :-

   CHECK (APP_NAME() = 'Name of your VB app')


v1.02 2000.03.26
Applies to SQL Server versions  : All
FAQ Categories                  : Server Administration and Tools, Application
Design and Programming
Related FAQ articles            : n/a
Related Microsoft Kb articles   : n/a
Other related information       : n/a
Authors                         : Neil Pike

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 ( in lib 7)

Fri, 20 Aug 2004 16:10:38 GMT
 [ 2 post ] 

 Relevant Pages 

1. how to keep users out of isql/w?

2. Keeping users out ...

3. keeping users from seeing DTS packages

4. Keeping users in the script

5. Keeping other users out while COPYing...

6. How? Keep user on the form

7. keeping users off the server

8. Looking for a way to keep user from deleting a record

9. users have to keep logging on

10. Keeping other users out while COPYing...

11. Keeping users out of an installation?

Powered by phpBB® Forum Software