How to prevent external access to SQL 2000 data ( eg.from excel)

[rachelgeoghegan]

I am having a problem which has been discussed here already although the answers given do not solve the issue.

The problem relates to preventing external access to sql server 2000 data from other external applications, for eg. MS Excel, MS Access. When you take the 'Get External Data'/new database query option in excel you are allowed to gain access to data in tables that you do not have permisssion to view.

For example I have created a user defined database role which only allows access to a no. of crucial stored proceedures & read only access to 1 table in the database - the idea being that anyone who uses the software application which accesses the database must be a member of that role otherwise they can't even log in.

This works perfectly when users are trying to access the data from Enterprise Manager or the software application (VB6).

This problem also applies to the import facility in MS Access - you can import data from tables in the database that the user doesn't even have read-only access to. I'm sure it applies to any external applications which use ODBC...

I have even denied access to these tables (by adding the user to the denydatareader role as well as the user defined login role as mentioned above) and still the same.

There has to be a workaround to this as it is a huge breach of security, I think I'll just go and import the SQL payroll table into ms excel!!! Can you imagine?

Any comments/ ideas would be most appreciated.

A frustrated Irish developer!!!....

 

[JamesLean]

When you do Get External Data... in Excel, you have to specify a data source and connection information (ie, the SQL Server name and whether you want to use a trusted connection or SQL login). Whatever login they use here should have the necessary permissions granted/denied in the database and then they can only see the data they are allowed to see. Is this not what is happening?

--James

 

[rachelgeoghegan]

No unfortunately this is not what is happening - I am specifying a data source in excel which connects through an sql server login with read only access to 1 table - however I am not prevented from extracting data from any sql table into excel which causes me a huge security problem as you can imagine.

Is this not the case on everyone elses sql databases?

Thanks for your interest,

Rachel

 

[barbola]

Are you using an 'sa' password? We do this alot but you need to know the password to access the tables, then you get asked to save the password with the table link.

Thanks!
Barb E.

 

[ArtieChoke]

One thing to be aware of - users could be members of the public (or another) group if you're using integrated or windows security. I don't know how roles affect this, but they might be getting other permissions based on NT group membership. Make sure the ODBC connection is not trusted (which is the default). But unless you can make this happen on any user's machine, you need to set up the proper security for the users.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[nigelrivett]

If the user is an admin on the server (probably the domain) then he will automatically be made sa when he connects and be able to do anything.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.