March 12, 2003 at 9:00 pm
Greetings to all -
Let me preface this saying a few things: this post will be somewhat lengthy, I'm new to this, and I'm currently learing SQL in the "trial by fire mode". So don't slam me too hard. 🙂
There are a couple of posts in here regarding the "use trusted connection" mess when connecting/linking an Access database to an SQL server. Let me describe my problem.
I have two basic users for my machines - essentially "user" and "administrator". I have created a new DB on the SQL server, and designed an Access front end to query it. I also created a system DSN which links to this DB, and then linked to the SQL tables from within the Access database.
Everything works fine when I am logged on as administrator - not even a network hitch or anything. It's great.
However, when I log on as "user" I get all sorts of prompts left and right with that cursed "use trusted connection" box. Each time I have to uncheck that and enter authentication information. This is NO WAY to run a database front-end tool, obviously.
I have to check, but I believe the "user" account is setup as a login on the SQL server (both as an NT account and as an SQL account) and has full read access to the DB I query.
Can anyone help with this? We have dealt with this problem for some time on other "front end" Access DBs and can't seem to find any way around it.
When linking from Access to SQL like this, how does the authentication process work - ie) what user permissions and user accounts are really needed? How much of a role does the DSN settings play? Could I be getting some sort of account conflict?
I am at a loss on this - I feel like I've tried everything. And this wouldn't be such a problem if the users didn't need to use them.
Any help is greatly appreciated - and I apologize if this was mentioned elsewhere - I did see some mentions but nothing with real detail or any resolution.
Thanks! Bill
March 12, 2003 at 9:28 pm
We use Access for some stuff, have had no problems getting either type authentication to work. I think a good place to start is to make sure the user login really does have access to the db - try opening a connection using Query Analyzer. If you're linking tables, Access saves the settings so you don't have to keep re-entering (provided you check the save passwords box). DSN's are really just a way to abstract a connection string so it sits outside of the application, not really much to them.
Andy
March 13, 2003 at 9:39 am
If you are using 'use nt authentication' on your SQL Server for the user accounts, then on the second config screen of the ODBC connection you need to have the "With Windows NT authentication using the network login ID." radio button chosen. This in effect adds the "Trusted_Connection"="Yes" key/string to the registry for the ODBC setting and you will stop getting that annoying box.
If you attempt to use a DSNless connection then you must set the string to "ODBC;DRIVER={SQL SERVER};SERVER=servername;DATABASE=dbname;TRUSTED_CONNECTION=YES" to remove the annoying box.
Hope this helps, if not, restate, this is the main environment we have.
Michelle
Michelle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply