October 3, 2008 at 1:23 pm
I have a rather vexing problem. I've inherited a SQLServer database that uses Access as a front end and I'm having problems with connectivity and adding users and I was hoping that someone would be able to help. I have a fair amount of experience with Access, but I've never used SQLServer before now, and I've never used Access in a multiuser environment.
Here's my problem: when I started my position a couple of months ago one of the accounts on my computer was able to access the database. However, I was not able to connect to the database using my own account on the same machine. I tried to make a connection from the SQLServer database to Access using ODBC with windows authentication but was not successful. I got a message saying "Login failed for user (null); not associated with a trusted SQLServer connection". I then tried to connect using SQLServer authentication and the connection worked. However, when I tried to login to the database, I got a message saying that login had failed for user XXXX (the name of the developer, removed to protect their identity). When I look into the code of the database, I see the developer's name (UID=XXXX) and password after code involved with ADO statements. I don't know why the database won't let me in, other than the password he left in the code is wrong, because SQLServer is set for mixed mode authentication.
This wasn't much of a problem because I could just use the other account on my computer to enter data, but then someone else in my workgroup was unable to connect to the database (he hadn't used it in a while). Even though I can make the ODBC connection using SQLServer authentication, I would prefer Windows authentication for security purposes. Is there a way to add another account on my computer (or a new account on another computer) as a trusted SQLServer connection?
Thanks for your help!
October 5, 2008 at 6:24 am
Windows authentication requires both the SQL Server and the connecting client to be either in the same domain or in trusted domains. If you're getting "Login failed for user (null)" that would likely mean this isn't the case. If it is the case, check to see if either system is having problems validating against the domain, a domain admin can check the system event log for NETLOGON errors on the domain controllers which specify either computer to see this.
If you are in a workgroup setting, or if you are in two different domains and there isn't a trust, there is a way to get Windows authentication to work, but that involves ensuring a local user account exists on the server where SQL Server is running (a domain account won't do) which matches your username/password identically.
As for the Access problems, it sounds like the developer hard-coded the username/password into Access. This is not an unusual thing to see. It may be in the connection to the linked tables (because Access can store the password). Did the developer create a SQL Server login on the SQL Server matching his/her credentials?
K. Brian Kelley
@kbriankelley
October 6, 2008 at 10:04 am
Based on what the other members of the workgroup have said, part of your reply makes sense. The database developer told other people that their password and user name to the database had to be the same as it was on their computer as it was on the server. One of our people had health problems earlier in the year and the password on his computer was changed so that other people could gain access to what he was working on. He didn't start working with the database again until this fall, at which point he couldn't access the database. Unfortunately, when we tried to rectify the situation by changing his password back, it still didn't give him access. I've also tried the same thing, changing my user names and passwords on the server, my computer, and the database to the same thing, but I still haven't had any luck created new connections to the database.
October 7, 2008 at 1:53 am
In my experience the error message appears either on WinXP Home or equivalent Vista clients, or XP Pro with "simple file sharing" selected. The point is probably which user credentials are used for login, either regular username/password or guest account.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply