Sporadically, the SSMA 5.1 Oracle sequence emulator causes login failure (Error: 18456, Severity: 14, State: 8) error messages to be written to the SQL Server ERRORLOG when used from an application using SQL authentication.
Steps to reproduce the problem:
After migrating an Oracle sequence to SQL Server 2008R2, where the SQL Server target schema is:
and the migrated sequence is
- Open SQL Server Management Studio and connect to that SQL Server using SQL authentication
2011-08-08 15:14:57.14 Logon Login failed for user 'SQLAuthUser'. [CLIENT: xx.x.x.xxxx]
The SSMA runtime’s retry logic insures that the error information logged in the ERRORLOG is spurious and can be safely ignored. No actual application error occurs, and the correct sequence number is returned in every case.
In the error message
18456, Severity: 14, State: 8,
a state value of 8 indicates a password mismatch. It results from an error in a deprecated SQL Server API used by SSMA’s sequence emulator.
- Authenticate using NT authentication rather than SQL authentication in applications that need to use the SSMA sequence emulator.
- SQL Server 11 supports a native sequence type. For Oracle sequences migrated to SQL 11, SSMA’s runtime sequence emulator is no longer needed.
Published by : Brian (MSFT), SQL Escalation Services, Microsoft