Security problem between SQL Server 2000 and Access XP

Question

Security problem between SQL Server 2000 and Access XP

pacopico

SSC Enthusiast

Points: 188
More actions
October 25, 2004 at 12:55 am

#65698

Hi all,
I have an application using MS Access XP as a front-end and SQL 2000 as a back-end. I am attaching the tables in my SQL database to the Access XP database using ODBC connections. The problem I am facing is the following:
I created a user in SQL and gave him db_owner permissions to my SQL database and at the same time I denied his access to certain tables in this SQL database.
In SQL entreprise manager, when I login with this user Name and Pswrd, I can access all the tables in this database except the tables for which he has no permission to access. So far, my security is working perfectly.
In my Access XP database, if I open the database in running mode the security is considered perfectly and the user is not allowed to open any program using the tables in question. (Note: I am refreshing by program the attachment of the SQL tables using this user name and pswrd.)
However, If the user goes and open the .mdb file of my Access XP database and then open any of those tables that he is not allowed to access the program opens the tables and let him see the data inside as if no security exists.
I tried everything I know and could not find any solution. Therefore, i will appreciate any possible help from you.
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

When you manually create a link from Access to an ODBC data source, at the "Link Tables" dialog there is an option "Save password" make sure that is not checked. That way, if they open the mdb it will prompt them to log in to the data source if they open a linked ODBC table. Also, your refresh code should not save the password in connection string.

pacopico SSC Enthusiast Points: 188 More actions · Answer 2

Hi

First I want to thank you Mr Mitchel for replying to my posted problem. I think you are right regarding the saving of the password inside the attachement. But, my problem is that my application have a major database (ie: main.mdb file) and a periferal databases (sales.mdb files). In the major database we refer to sales.mdb database inside the "References" section and when I open the application Main.mdb inside this database I refresh the attachements of the tables inside Sales.mdb as follow:

sConnect = GetSQLConnectString(DataPath) '''nb: sconnect => ODBC;DRIVER={SQL Server};SERVER=MainServer; DATABASE=Test;UID=Usr1; PWD=Test;Trusted_Connection=No

Set dbLoc = OpenDatabase(ProgPath) 'ProgPath c:\ApplicFolder\Sales.mdb

If Not rsLinks.EOF Then 'rslink is a recordset that reads all tables that should be linked in TSales.mdb

rsLinks.MoveLast

iCount = rsLinks.RecordCount

rsLinks.MoveFirst

End If

Set tdfLoc = dbLoc.TableDefs

For i = 0 To iCount - 1

sTable = rsLinks!TableName

If Left(sTable, 4) = "dbo." Then sTable = Mid(sTable, 5)

Set tblLoc = tdfLoc(sTable)

If tblLoc.Connect <> "" And tblLoc.Connect <> sConnect Then

dbLoc.TableDefs.Delete sTable 'delete table if exists

Set tblLoc = dbLoc.CreateTableDef(sTable)

With tblLoc

.Connect = sConnect 'Connection path

.SourceTableName = sTable 'name of table to connect

End With

tdfLoc.Append tblLoc

End If

next i

As you can see I am attaching using "sconnect" and I am saving the user name and password in the connection. If I remove the password from the "sconnect" path and set it to blank then the system when it refreshes each table it will prompt me to enter the password. I do not know if i wrote something wrong in the code but that's the only way i know to refresh the linked tables in a remote database.

I do not know if I was clear in my explanation but I appreciate if you can help me on this issue.

Thanks in advance

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

Instead of deleting and then re-creating the TableDef, you could just change the connection string and then refresh the link, like this:

With tblLoc

.Connect = sConnect 'Connection path

.RefreshLink

End With

pacopico SSC Enthusiast Points: 188 More actions · Answer 4

Hi Mr Mitchell,

I tried what you have told me but I got the following error: "Invalid operation." on ".RefreshLink". However, I added to my code the following:

.Attributes = dbAttachSavePWD

it becomes like this:

With tblLoc

.Connect = sConnect

.SourceTableName = sTable

.Attributes = dbAttachSavePWD

End With

It worked the security was considered and the user was not able to open the tables he was not supposed to open.

I want to thank you very much for your help.

regards