I'm dealing with a weird issue on a couple of my SQL boxes, both related the the same application. The issue I'm seeing is random login failures for one of my SQL Logins (non-domain). The error I see is straight forward, it's a failed password error. The issue is that it is coming from the SQL Server itself. I've captured the failed login a few times via scripts against dm_exec_connections as well as in Profiler, and both are saying the application name is Microsoft SQL Server. So it appears to me as though my SQL Server is attempting to connect to itself, via TCP, using one of the logins on the server and failing due to a bad password. Today, it seems to be happening on my development server every 15 minutes. That lead me to checking my jobs to see if there is anything there causing it. I do have one job that runs every 15 minutes, but it does not run at the same time as the error, as it runs under the SQL Agent service account. I've also run the job manually, as well as running the stored procedure the job runs manually. Both times, the failed login did not occur. I've run a profiler trace to capture failed logins, to get the exact time of the failed login. I've combined that with Process Monitor (sysinternals tool) to see what is going on with the server at the time of the failure. All I've found there is that there is indeed an attempt to connect to the server, from the server itself, from SQL Server. Which just confirms that the server is attempting to connect to itself using this login, and failing. I've checked the windows tasks for anything that could be causing it, and there is nothing unusual there. So my question is, does anyone have any further advice on trying to find the source of this failed login? It isn't impacting anything, as it does not happen often enough to lock out the account, it just fills my SQL logs with false positives. And it bothers me. Thanks!
I'm posting the answer to my own question, just in case someone else is seeing this issue. This issue appears to be specific to using SQL Server 2008 R2 SP2 along with SQL Server Migration Assistant for Oracle 5.2. The bug comes up due to the vendor's software requesting the next number in a sequence, using SSMA's functionality. This sequence call uses the API function srv_pfield(), which in turn causes the bug. The workaround is to switch from SQL Authentication to a Windows login. Official response from Microsoft is below. *In the case where the client application uses SQL authentication, SSMA’s runtime still uses the API function srv_pfield() (see:
http://msdn.microsoft.com/en-us/library/ms164690.aspx) to run the sequence emulator under a specific SQL user context. The 18456 errors in the ERRORLOG are caused by the ODS API sporadically returning an invalid password token. Because the SSMA runtime retries until it gets a valid connection, the ERRORLOG entries for the failed login are spurious. There is no scenario that will result in an actual application error. Unfortunately, the srv_pfield() API has been deprecated (and the SRV_PWD function dropped from the documentation) and I will not be able to get a fix for the on the SQL Server side. Over the longer term, I am working to get the SSMA team to convert the impersonation functionality to use the SQLCLR. However, this is a significant undertaking, and we will not be able to offer any kind of a short-term fix. The only known workaround at this point is for applications that use the SSMA runtime functions, such as the sequence emulator, to use NT authentication. The problematic codepath is never hit. Looking far forward, SQL 11 offers a native sequence type, so the emulator will not be needed after migration to this platform.*