General discussion

Locked

Access Front to SQL Server 2K back-end

By cswearingen ·
I have an application that I built in Access 97. There are several users who will be accessing the data via an Active Server Page application as well so recently we built an SQL 2000 server to house the data from this application (and others of course). The Access front end will connect to the SQL Server via and ODBC DSN connection.

The problem arises when users open the database. They are prompted for a login and password to access the SQL server. My app has a security that I coded intoit and I don't want the users to login twice.

How can I create a User account on the SQL server database so that users are not asked to login to the SQL Server when they launch the front-end. In otherwords I need a transparent login to the SQLServer.

We are running a Novell network so I can't just pass their network login information to the SQL Server. I tried creating a 'guest' user account but that still gets prompted for a login.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Front to SQL Server 2K back-end

by cswearingen In reply to Access Front to SQL Serve ...

Point value changed by question poster.

I doubled the points hoping to attract some attention to my dilema. I know there has to be at least one SQL Server guru out there.

Collapse -

Access Front to SQL Server 2K back-end

by cswearingen In reply to Access Front to SQL Serve ...

Point value changed by question poster.

Okay, is this enough points to get some attention? This can't be that hard of a question!

Collapse -

Access Front to SQL Server 2K back-end

by grybyx In reply to Access Front to SQL Serve ...

If I'm assuming correctly, your app has its own table for logins. When a user opens the app, it asks for their login information and compares it to records in a SQL Server table. This is instead of using Access's or SQL Server's security measures.
If this the case, then create a SQL Server login for the app, and give it a unique password that has nothing to do with the login name. For example, if the app's name is MyApp, then the SQL Server login would be something like "MYAPPUSER" and the password is "ou812". (This is similar to what I have done for several of my applications.) In the new login's properties, make sure that its permissions allow it to do whatever it needs to do with the database. A new login has no permissions by default, as far as I know.

Then, configure your DSN to use SQL Server authentication using that new login.

You might need to re-link the tables in Access (File > Get External Data). When you select the SQL Server tables into the Access front end, thedialog contains a check box to remember the password, which is the password of the SQL Server login that your DSN is using. Without checking this, Access will ask for the password the first time a user accesses a SQL Server table.

I hope this answers your question.

Collapse -

Access Front to SQL Server 2K back-end

by cswearingen In reply to Access Front to SQL Serve ...

Thanks grybyx. I haven't gotten a chance to fully test this out but it makes sense. We're still having router problems so I still can't connect to the SQL Server from my office. So I decided to just rate your answer.

Collapse -

Access Front to SQL Server 2K back-end

by cswearingen In reply to Access Front to SQL Serve ...

Thanks grybyx,

You were right on with your assumptions about my application's structure (with a user's table). I have some user's using the Access runtime and they wouldn't be able to manage user's via that interface (and I don't want to manage users for them either).

I'll try your solution and let you know if it worked in the manner that I needed. I'll post the points as soon as I have verified the solution.


Christopher

Collapse -

Access Front to SQL Server 2K back-end

by cswearingen In reply to Access Front to SQL Serve ...

This question was closed by the author

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums