ODBC connection (Access 2003 SQL Server 2005)

Topic

New Reply

Hi everybody:

Please help me understand how to simplify security. I have written a secured Access client app with linked tables to SQL Server using ODBC. I wrote a file DSN, located on the network, that the ODBC tables use to link to SQL Server. The file DSN contains my userid and password. Everything works fine on my PC. Now, two other users want to use my application, so I would like to set them up with the Access client on their PCs. (We are networked.) However, when a user tries to open a combo box on the form, she gets a nasty SQL Server login dialog box. Then, when she logs in, she gets a nasty error message that the table can’t be found or has the wrong name or she doesn’t have permissions to view the data.

I’ve tried recreating the DSN, but it seems to want a workstation ID, which of course would be specific to my PC, so wouldn’t work for her. She does have rights to the SQL Server and to the database on the server, and the server is set up to use either Windows or SQL Server authentication.

I just want these users to be able to login to Windows XP, login to my Access app using Access security, and then be able to access all the tables, views, etc. from the forms. Please help me to understand how this can be done in as simple a way as possible. I would like to use a file DSN that would permit all users to access the SQL Server tables. Could somebody tell me what the DSN should say?

Thank you, in advance, for your help.

Reply | Quote

Replies

Kathryn,

I assume you have security concerns that are forcing you down this route. If your clients are on a secure network, have you considered the simpler option of using a SQL Server login?

Regards,

Reply | Quote

Thank you for responding.

I would like to make the whole situation as transparent as possible for the user so she doesn’t have to login to SQL Server, just to my Access application.
My Access application is secured and will shut down if the admin user tries to open it. Since none of the folks around here are particularly computer savvy, that is sufficient security. What I’m trying to do is to get the DSN written in such a way that when the Access client is installed on different client PCs and different users log in, SQL server won’t choke and ask for a login, either for authentication or for table/view permissions, but will just use my authentication and permissions from the file DSN and linked tables (which are linked using the same file DSN and ODBC).

Hope this clarifies the situation.

Reply | Quote

Sorry, missed that point.

Do you have exactly the same DSN names on both machines?

Reply | Quote

There is only one DSN file. It is on the network.

Reply | Quote

MS Fan is probably more on your wavelength than I am, so I’ll duck out here. Just to say that you can save the SQL Server password when you link to the SQL Server tables via the ODBC connection, so users are never prompted and do not need to know the password. The ODBC connection must be set up on each PC, of course. Another option, but perhaps not suitable for you.

A happy new year to you and all Loungers!

Reply | Quote

I’ve re-linked the back-end tables using Trusted Connection, which according to the “help” means that SQL Server will use integrated login security and a password isn’t required. However, it only works on my PC, where I can logon to my app user her Access account and then click the “use trusted connection” check box and all the data is available. But when I move the client to her PC and login using her Access account, the same error messages appear. (But I can logon to the client using my login on her PC, and everything works fine.)

I don’t see anything in the Linked Table Manager Options to save a password, but apparently, that isn’t needed except for SQL Server authentication.

I’m almost getting desperate enough to give her my password!

Please help if you can.

Thank you,

Reply | Quote

Hi Kathryn,
I think Jules actually suggested the simplest solution – and the one that we use with nearly all Access FE to SQL BE applications. Create a System DSN on each user’s workstation, and set it to use Windows Integrated security. Before that of course, you need define each user in SQL Server Security and give them the appropriate permissions to use the database. That way you can take advantage of some of the SQL Server facilities to track who creates records, and if you want to invest the time to create a trigger, you can also track who last edited a record. Unfortunately the Linked Table Manager doesn’t seem to work with File DSNs or DNS-less connections. Let me know if you need further details on either of the steps.

Reply | Quote

I am not 100% sure but I had done following on few computers and it worked every time.

Find in C:oracleora81networkADMIN file called sqlnet.ora and replace it on other machine with yours. Rename up old one.
Also Tnsnames.ora file in same location.

Good luck.

Reply | Quote

Thanks for the tip, but I’m not using Oracle, but SQL Server.

Reply | Quote