Error using Windows Authentication in Access

I currently have an Access database connecting to an MSDE back-end running
on the server. The server is running Windows Server 2000. The rest of the
workstations are running Windows XP Home Edition. MSDE is installed in
Mixed mode, and is configured to accept network connections.
When I open Access, I get an error message that looks something like this:
Connection Failed:
Login Failed for user (null). Reason: Not associated with a Trusted
Connection.
I think the problem is that we don't have domains, since we are running XP
Home rather than XP Pro. I can log in just fine in SQL Server
Authentication, but every time Access comes up it gives me this error.
Is there a way to make it so that Access will default to SQL Server
Authentication rather than Windows Authentication? (I am using .mdb if it
matters).
Any help would be appreciated.
Thanks,
Chris
Windows Authentication won=B4t work accross computers without a domain
concept (Which XP Home has no idea of)
http://groups.google.de/group/micros...ity/browse_fr=
m/thread/2732c74677dd6a77
"Is there a way to make it so that Access will default to SQL Server
Authentication rather than Windows Authentication? (I am using .mdb if
it
matters). "
You can either use SQL OR Windows Auth.:
http://msdn.microsoft.com/library/de...n-us/adminsql=
/ad_security_47u6.asp
HTH, Jens Suessmeyer.
|||Jens, thanks for the response. I know that I can use Windows Auth. or SQL
Server Auth. I have the server set to SQL Server Authentication by default,
and it works fine. The problem is that when I try to open a form in Access
that has a table on the MSDE back-end as its data source, I get an error
(the same one I mentioned in my original post).
If I understand correctly, when MSDE is set to Mixed mode, it will first
attempt to log you in using your Windows Authentication, and failing that,
will then allow you to log in using SQL Server Authentication.
This seems to be what is happening in Access. The system first tries to log
me in using Windows Auth., which of course won't work because we don't have
domains, so it generates an error. Then, it allows me to log in using SQL
Server Authentication, which works just fine. So, the problem isn't that I
can't login to the database, it's just that I get an error message every
time I try to open the form.
I have been trying to use a connection string to prompt the user for their
Username and Password on startup.
The code for the connection string looks like this:
Dim oConn As New ADODB.Connection
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=OFFICE-DC;Database=authorDB Linked
Tables 2003 NoSwitchboard
SQL;TABLE=dbo.EntireSpreadsheet;Trusted_Connection =no;"
What this code does is to bring up the SQL Server Login dialog box, which
allows the user to enter their username and password. If they enter their
information incorrectly, they get an error message. If they enter it
correctly, the dialog box disappears.
The problem is that, even if the user enters their username and password
correctly, they will STILL get an error message when they go to open the
form. The only idea I have to explain this is that perhaps the connection
string is not saving their Username and Password.
In any case, I'm really not trying to use Windows Authentication, however
I'm fairly certain that what is generating the error is that Access IS
trying to use Windows Authentication, despite the fact that we don't have
domains.
If anyone has any ideas about how to get around this error (login failed for
user (null) ) I would love to hear them.
Thanks,
Chris
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1129707071.110729.195090@.g14g2000cwa.googlegr oups.com...
Windows Authentication wont work accross computers without a domain
concept (Which XP Home has no idea of)
http://groups.google.de/group/micros...32c74677dd6a77
"Is there a way to make it so that Access will default to SQL Server
Authentication rather than Windows Authentication? (I am using .mdb if
it
matters). "
You can either use SQL OR Windows Auth.:
http://msdn.microsoft.com/library/de...urity_47u6.asp
HTH, Jens Suessmeyer.
|||How were these tables added to the access database, are these linked
tables ? Were the linked tables generated on a system which used
integrated authentication ? If so ,look in the system tables of access
and see whatr kind of authentication is used in here. Guess you are
trying to open the tables with two different methods,y our own one
(those with the dialog box) uses SQL Server authentication and this
with the linked tqables tries to use Windows Auth.
Jens Suessmeyer.