dsn-less connection to SQL Server

[beckyh]

I have a module of code, in my Access 2000 db, that changes the connect string of the linked SQL Server database to be DSN-less. One problem though, some users are prompted for a user id and password, while others are not. Any reason why that might be? Here's the code

Function ChangeConnectString()
'This sample function changes the connect string of the linked SQL Server
'Events database to be DSN-less.
Dim db As Database
Dim t As TableDef


Set db = CurrentDb
For Each t In db.TableDefs
If t.Attributes And dbAttachedODBC
Then
t.connect = "driver={SQL Server};server=Transql;" & _
"database=Events;uid=events;pwd=events"
t.RefreshLink
End If

Next
db.Close
End Function

 

[NoCoolHandle]

What type of client do you have? 9x or NtX
-- Because 9x uses passthrough authentication it connects differently to NT type kernals..

Do you force network authentication on the SQL box (just to connect to it - not SQL Server)?

Rob

PS I saw this a few years ago, but just can't seem to remember the cause... the only thing that is creaping up in my memory is that access may be using the DSN to a linked table before you run your code.. but very foggy terrain.

 

[beckyh]

We have all kinds of OS's here. We are using 95, 2000, NT4.0. But mostly this problem occurs on 95 and 2000 boxes. Answer to second question - I've tried it both ways and neither works, both being network authentication and providing a UserID and password.

 

[SQLBill]

Are all the users on the same network as the database server? I have found that in Mixed Mode, if you are on the same network you don't need to supply your login/password info. But if you are on a different network (or subnet), you login/password can be required.

-SQLBill

 

[beckyh]

i'm not sure i understand you're question. the access frontend and the sql tables reside on different servers, but i'm not sure that was your question.

 

[SQLBill]

Check the IP addresses.

For example: SQL Server xxx.xxx.1.15
1 User's system: xxx.xxx.2.101
2 User's system: xxx.xxx.1.27


User 2 will be able to access the database via trusted login since they are on the same subnet. User 1 will need to enter the login and password as they are on a different (non-trusted) subnet.

-SQLBill

 

[beckyh]

OK now I understand your question. But why would user 2 need to provide ID and password, when I pass that in the string? It's part of the code and yet they are still prompted for a password.

Another strange twist, when the user gets the login box to SQL Server, when they type in a password, it won't work. They still cannot get into the table.

 

[SQLBill]

As I have come to understand it, the DSN connection is to the Operating System first. So, I believe the login/password you are providing authenticates the connection to the computer/server itself, then it requires another one for the SQL Server database connection.

Check Enterprise Manager, Security, Logins. Make sure they actually have permissions to access the database. Reset the password to make sure the one in SQL is the same one they are using.

-SQLBill

 

[TheFrog]

really the inconsistency occurr. the trust connection is default for some windows. Configure your sql server security SQl server and Windows, and change the password of the your users in SQL Server. The passwords will have different between Windows and SQL server.