Opening a sql database using access

Question

Opening a sql database using access

ozzy_rkr

SSC Enthusiast

Points: 138
More actions
November 22, 2010 at 6:12 am

#231796

I've deployed a .Net application for a client, the application uses a SQL server 2005 database and the scenario goes as such
1- the user used an odbc connection to connect to database on the Remote SQL server
2- the application runs fine and the user has read and write permissions on the database
3- the user opens a new access database and select an external data, by linking to the data source by creating a linked table
4- the user select the available ODBC machine data source
5- the user is able to open the database tables and edits the values in the database directly
this is a major security threat, and we need to disallow the user from connecting to the database using odbc, but to use the database from the application functionality. How can this be done
Thank you,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

liebesiech SSCrazy Points: 2610 More actions · Answer 1

Well, I have no real experience in this but in general I would create a SQL Server login using SQL Server authentication and use this information in your connection string. A direct connection to the database shouldn't be possible any more if access is restricted to this login only. :Whistling:

I'm sure there are much better and professional ways to prevent the situation you described.

This link might give some better ideas:

http://msdn.microsoft.com/en-us/library/ff648340.aspx

Here a great site for connection strings:

http://www.connectionstrings.com/sql-server-2005

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

Ozzy,

You need to create & set three startup properties for the Access database:

StartupShowDBWindow = False

AllowSpecialKeys = False

AllowBypassKey = False

this will prevent the user from accessing the database window & editing the tables. The user will only be able to do what the application provides for them to do.

You might want to test this with a copy of the database.

LutzM SSC Guru Points: 107049 More actions · Answer 3

WILLIAM MITCHELL (11/24/2010)
Ozzy,
You need to create & set three startup properties for the Access database:
StartupShowDBWindow = False
AllowSpecialKeys = False
AllowBypassKey = False
this will prevent the user from accessing the database window & editing the tables. The user will only be able to do what the application provides for them to do.
You might want to test this with a copy of the database.

I guess the issue is not really related to an ACCEES application.

The more interesting issue is the existence of a ODBC connection to a database where the user most probably know SQL login and password in order to connect to the database via the .NET app Ozzy (ore somebody else) developed (which is intended, obviously). But it also would allow the user to use an empty/new MS ACCESS database together with the ODBC connection and the known credentials to bypass the app.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

You make a good point. If you use a DSN and/or Windows authentication, that provides an easy way for the user to get in.

We have the Access 2003 runtime installed on Windows terminal server, and the application uses a DSN-less connection with SQL Server authentication to connect to the SQL 2005 database. The runtime keeps the user out of the database window so they can't see the linked tables directly. When the app opens it looks at the host name; if it is not the terminal server or dev machine, it immediately closes. It also checks the Access version, and closes if the version is newer than 2003.

liebesiech SSCrazy Points: 2610 More actions · Answer 5

Check this article: http://support.microsoft.com/kb/892490/EN-US/

It explains how to use a DSN-less connection to a SQL Server for linked tables in Access. With this approach you can prevent that users have to know the details of an ODBC connection.

Maybe Ozzy could once in a while indicate whether he is still interested in the topic or not...:doze:

Steven Deetz SSC Veteran Points: 224 More actions · Answer 6

I have developed with Access 2003 using both ODBC and ADO to connect to SQL Server 2000 and 2005. What I like about ADO is that you can have no tables from SQL Server showing as objects in the Access database window. The other thing that I like about ADO is pulling only the data the user requests down into Access tables. I like to think of the Access tables as temporary data storage tables that the user can report, edit, add, or delete. I also think the use of ADO, in most instances, is quite a bit faster than ODBC simply because there are fewer layers. On the downside, this requires more coding on the developer's part but with some smartly developed Public Procedures you find yourself reusing them over and over. 🙂

Pete23Mid SSC Enthusiast Points: 172 More actions · Answer 7

Another way is to only use stored procedures on the SQL Server side. That way MS Access can not 'see' any tables. And one can put any security he wants onto stored procedures. As well you can allocate different privileges, like read, write and create, to different user accounts. If you give only read privileges and cancel write and create privileges, you have a control over what user can and can not do.

green smoke reviews