Accesing data from many applications.

Hi, I have a server running sql server 2000 sp3. In this sql server we
use windows authentication to allow users to use the applications that
were developed in house.
At this moment we have some users that are using excel to get data from
databases but we dont want they do that, because we cant control the
data they are getting.
We want to restrict that all users can get data only using the
applications that are developed in house.
Do you know if in sql server I can restrict wich applications can be
connected to sql server and wich not'
I need to continue using windows authentication.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***On Feb 24, 11:28 am, MariaGuzman <mar...@.devdex.com> wrote:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we don=B4t want they do that, because we can=B4t control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
> I need to continue using windows authentication.
> Thanks a lot for your help.
> *** Sent via Developersdexhttp://www.codecomments.com***
If a user has permission to connect they can.
See SQL Server 2005 Books Online topic:
Application Roles|||MariaGuzman (marisa@.devdex.com) writes:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we dont want they do that, because we cant control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
I assume here that your applications are not using stored procedures,
but generate SQL statements in the client. Because if the applications
are using stored procedures (with no dynamic SQL in them), the answer
would be trivial. Just revoke all direct table access. It's sufficient
that users have EXEC permissions on the stored procedure.
If your applications are not using stored procedures, it's of course
a major task to rewrite them. Using application roles as Steve suggested
is a possible solution. It still requires the application to be rewritten,
because once the user has logged in, the application must issue
"sp_setapprole". Note also that if you schemes that requires you
to know the name of the current database user, this will break with
application role.
Note also that application role with two-tier solution is not real
security. The password for the application must be hidden somewhere,
but whereever you hide it, a skilled user will be able to find it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx