MDB linked to SQL Server tables w/MDW security

[EPD Hater]

I am in need of some assistance regarding SQL Server ODBC links and security. This is for a project at work...

1) I have an MDB that has linked tables through ODBC to a SQL Server 2000 database. This is how I chose to write this multi-user application (instead of the ADP method).

2) My IT department created a user ID that the application will use to connect to the database. They chose this method instead of having to create many user accounts with varying permissions. I have programmed permissions within the application.

3) After linking the tables through ODBC, it defaults to my Windows User ID for the connection. Because of this, I have used a connection string with the application's User ID/Password and perform a RefreshLink on all linked table definitions on startup to ensure valid connection.

This is successful, but my IT department is seeing that another user is attempting access to the SQL Server database--the username in the LDB/MDW file! This is what I don't understand. If I open the database without going through workgroup security, the word "Admin" is passed and the authentication fails. If my users would login through the MDW file, their user IDs would be passed and again, authentication will fail as they are not valid users (only the application ID would be valid).

What is really going on here? How can I prevent the "Microsoft Access User ID" from being passed through the ODBC link?

Here is a sample of the connection string I'm using:

Driver={SQL Server};Server=ServerName;PersistSecurityInfo=Yes;AnsiNPW=No;Database=DBName;Uid=Username;Pwd=Password;

 

[Sum Guy]

It sounds to me as though your IT department has messed up the security system you installed.
First of all, the "Admin" user should have been deleted once the security was completed.

I hope you made a copy of the original database without security. You should sit down with your IT department and hammer out a security system that both you and they can live with. Then redo the back end of the database appending the existing data into the new back end.

 

[EPD Hater]

I dont think a new back-end database is needed. I created the workgroup file with the valid users that will be using my database. When you say "the admin user must be deleted", I can't do this as "Admin" is required in the access security file. (This is where, in Access, you would go to Tools, Security >, then User and Group Accounts...)