up vote 0 down vote favorite
share [g+] share [fb]

Errors

Database administrators report Microsoft SQL Server 2008 server-side error "Invalid Login" (error 18456, Severity: 14, State: 5).

Error examples from the server log:

Dec 1 2010 10:12AM - Login failed for user '{Active Directory Name #1}'. Reason: Could not find a login matching the name provided. [CLIENT: {IP Address #1}]
Dec 1 2010 10:44AM - Login failed for user '{Active Directory Name #2}'. Reason: Could not find a login matching the name provided. [CLIENT: {IP Address #2}]
Dec 1 2010 2:03PM - Login failed for user '{Active Directory Name #3}'. Reason: Could not find a login matching the name provided. [CLIENT: {IP Address #3}]
Dec 1 2010 4:18PM - Login failed for user 'Admin'. Reason: Could not find a login matching the name provided. [CLIENT: {IP Address #1}]

The {Active Directory Name} is the same as their login name, without the domain. For example, full name would be {domain}\{Active Directory Name}.

The error for user "Admin" comes from the same IP address as {Active Directory Name #1}, a user developing Microsoft Access Visual Basic for Applications (VBA) code; I suspect this stems from a need to configure his minimal use of VBA with a proper Windows Authentication connection string, even though he solely accesses data via an ODBC DSN link.

Environment

Microsoft Access 2003 (frontend) database containing ODBC File DSN links to tables in a read-only Microsoft SQL Server 2008 (backend) database.

I have administrator rights to the frontend database. I have read-only security rights to the backend database, which resides on a hosted server at an external data center. DBA's have configured the backend database for Windows Authentication.

End users login to their PCs with Active Directory accounts, open the frontend database, then use Microsoft Access Query Designer to generate reports using the table links to the backend database. The frontend database does not use Microsoft Access Jet Security (to my knowledge--there is no login prompt).

The frontend database reports no (visible) errors and produces expected results.

ODBC file DSN contents

[ODBC]
DRIVER=SQL Server
Trusted_Connection=Yes
StatsLogFile={path}
StatsLog_On=Yes
DATABASE={dbname}
APP=Microsoft Data Access Components
Description={general description}
SERVER={server name}

Why would the File DSN links work, without error, but generate a server-side Invalid Login error? Thank you.

link|improve this question
Clearly the Windows authentication to SQL Server on which you are depending is not working otherwise there would be no 'Login failed for user '{Active Directory Name #3}' reported. Have you observed that the table links actually work? If users 1,2,and 3 are actually users in the target database, that can be confirmed by the DBA executing SELECT * from sys.database_principals where name ='Domain\Username' against the target database. 0 rows in the result set means the users aren't there. – jl. Dec 4 '10 at 22:50
Thanks jl; table links work without client-side error--during development, testing, and end user usage. No problems. Users 1, 2, and 3 are members of an Active Directory group created as a user on the backend database and granted role db_datareader. The same Active Directory group has been created as a Login to the server itself and granted roles db_datareader and public; the login is correctly mapped to the user on the backend database. – schultkl Dec 4 '10 at 23:34
feedback

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown
discard

Browse other questions tagged or ask your own question.