SQL Server 2008 Server-Side "Invalid userid" (Error 18456, Severity: 14, State: 5)

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
Descri开发者_StackOverflow社区ption={general description}
SERVER={server name}

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

Is there any chance that the end users are seeing cached data? Is SQL Server set up to allow remote connections? Are the AD accounts set up as logins as well as entitled users on the appropriate database? When you test the ODBC connection through the ODBC manager do you get a successful connection? Does a successful connection test generate the error? Is the back-end database and the front-end application on the same domain? If not, is there domain trust set-up? (If not you may need to use SQL Logins rather and AD)

Those are all the types of things I would typically run through to try and troubleshoot this type of issue.

The source of the issue seems to be an undocumented (?), Microsoft Access 255-character limit on the ODBC connection string.

Each Microsoft Access ODBC-linked table was created with a DSN file containing line “Trusted_Connection=Yes”.

Presumably, this tells Microsoft Access to use Windows Authentication.

However, while double-checking one of the ODBC-linked tables, I noticed text “Trusted_Connection=Yes” falls outside the first 255 characters of text. I can see it is there by using the VBA Immediate Window and running command

print CurrentDb.TableDefs("{table}").Connect

but this only prints 271 characters, not the full string. The final 10 characters, however, are:

Trusted_Co

Re-linking the tables with a DSN file containing Trusted_Connection=Yes line in the first 255 characters solved the issue.

Thank you.