SQL Server 2008 服务器端“无效的用户 ID” （错误 18456，严重性：14，状态：5）
错误
数据库管理员报告 Microsoft SQL Server 2008 服务器端错误“无效登录”（错误 18456，严重性：14，状态：5）。
服务器日志中的错误示例：
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}]
{Active Directory 名称} 与其登录名相同，不带域。例如，全名将为 {domain}\{Active Directory Name}。
用户“Admin”的错误来自与 {Active Directory Name #1}（开发 Microsoft Access Visual Basic for Applications (VBA) 代码的用户）相同的 IP 地址；我怀疑这是因为需要使用适当的 Windows 身份验证连接字符串来配置他对 VBA 的最小使用，即使他仅通过 ODBC DSN 链接访问数据。
环境
Microsoft Access 2003（前端）数据库，包含指向只读 Microsoft SQL Server 2008（后端）数据库中表的 ODBC 文件 DSN 链接。
我拥有前端数据库的管理员权限。我对后端数据库拥有只读安全权限，该数据库驻留在外部数据中心的托管服务器上。 DBA 已为 Windows 身份验证配置了后端数据库。
最终用户使用 Active Directory 帐户登录其 PC，打开前端数据库，然后使用 Microsoft Access 查询设计器使用后端数据库的表链接生成报告。前端数据库不使用 Microsoft Access Jet Security（据我所知，没有登录提示）。
前端数据库没有报告（可见）错误并产生预期结果。
ODBC 文件 DSN 内容
[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}
为什么文件 DSN 链接可以正常工作，没有错误，但会生成服务器端无效的消息登录错误？谢谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最终用户是否有可能看到缓存的数据？ SQL Server 是否设置为允许远程连接？ AD 帐户是否设置为适当数据库上的登录名和授权用户？当您通过 ODBC 管理器测试 ODBC 连接时，连接是否成功？成功的连接测试是否会生成错误？后端数据库和前端应用是否在同一个域？如果没有，是否有域信任设置？ （如果不是，您可能需要使用 SQL 登录而不是 AD）
这些都是我通常会尝试解决此类问题的所有类型的事情。
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.
该问题的根源似乎是未记录的 (?) Microsoft Access 对 ODBC 连接字符串的 255 个字符限制。
每个 Microsoft Access ODBC 链接表都是使用包含行“Trusted_Connection=Yes”的 DSN 文件创建的。
据推测，这会告诉 Microsoft Access 使用 Windows 身份验证。
然而，在仔细检查其中一个 ODBC 链接表时，我注意到文本“Trusted_Connection=Yes”超出了文本的前 255 个字符。我可以通过使用 VBA 立即窗口并运行命令
print CurrentDb.TableDefs("{table}").Connect
看到它在那里，但这只打印 271 个字符，而不是完整的字符串。不过，最后 10 个字符是：
Trusted_Co
使用前 255 个字符中包含 Trusted_Connection=Yes 行的 DSN 文件重新链接表解决了该问题。
谢谢。
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.