‘Login Failed’ ERRORLOG message from SSMA 5.1 for Oracle’s sequence emulator

Problem:

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: TARGET_DATABASE and the migrated sequence is TEST_SEQ

- Open SQL Server Management Studio and connect to that SQL Server using SQL authentication

- Repeatedly execute the following batch:

DECLARE @nextval numeric(38, 0);
EXECUTE @nextval = sysdb.ssma_oracle.db_get_next_sequence_value N'TARGET_DATABASE', N'dbo', N'TEST_SEQ';
select @nextval

- After multiple executions of this batch, you may note an entry similar to the following in the SQL Server ERRORLOG file:

2011-08-08 15:14:57.14 Logon Error: 18456, Severity: 14, State: 8.

2011-08-08 15:14:57.14 Logon Login failed for user 'SQLAuthUser'. [CLIENT: xx.x.x.xxxx]

Additional Information

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.

 

Workarounds:

- 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