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

Question

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.

Additional notes: table links work without client-side error--during development, testing, and end user usage. 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. — iokevins
Note: changed to reflect 2008 instead of 2005; our production instance is 2005, but the development environment is 2008. — iokevins

CodeMonkey1313 CodeMonkey1313 · Accepted Answer · 2010-12-04T20:18:56

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.

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

2 Answers