asked
Tracing Failed Login
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!
In the Profiler trace, did you capture the ClientProcessID? This will then match up to a process in Windows Task Manager and will at least tell you where the client connection is coming from
Thanks for the response Kev I did a new trace this morning to check the ClientProcessID, and it confirmed what I was seeing. The connection is coming from the SQL Service itself. I hate to say I'm stumped (so I won't), but I'm running out of ideas to track this down :/
Which version of SQL Server are you on? If 2008 or better you might be able to use extended events and their event linking capabilities.
Thanks for your response Grant! It is a 2008 R2 server. I'll look at setting up something in extended events and see if I can capture more information. In the mean time, the vendor for the software has gotten back to me to tell me that the issue is related to SQL Server Migration Assistant. I've asked him to get me the reference from Microsoft so I can get more details.
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.*
18 People are following this question.
