The problem I am facing is a very complex one and inspite of trying to gather a root cause of the problem, I am standing at the same place after 2 months with just bits and pieces of information.Here is a scenario:
There is a windows 2003 server which uses an system DSN ODBC connection. I looked into the driver properties and it is as follows:
Name Version File
SQL Server 2000.86.3959.00 SQLSRV32.DLL
Now, this system DSN has been given configured with TCP\IP in Network Libraries and 'determine port dynamically' is checked.
Now, lets come to the database destination. It is hosted on Windows 2008 having SQL 2008 R2 RTM version 64-bit.
Now, I will give you a an overview about the events that happen and whatever troubleshooting I could perform:
I get an email saying 'blah blah' failed and the only message their application gets is 'cannot connect to database'
I go the SQL Server logs and find the following information:
Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.0.0.xx
Error: 18456, Severity: 14, State: 58.]
A quick search shows that this error may come when an SQL Server is configured with windows authentication but its not true. We have mixed mode and connection issue is intermittent.
This SQL Server is configured to run on a local system account but since we use only SQL Server accounts to connect to this, there should not be any Kerberos errors.
When I run a profiler trace and see only 'existing connections', i see a lot of them coming from my client server displaying the sql user but
NO hostname is shown. Textdata field shows TCP\IP information along with some arithabort and ansi-null settings.
Now, I tried looking into ring connectivity buffer by using following:
SELECTCAST(record AS XML) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
One sample output is:
<ConnectivityTraceRecord>
<RecordType>Error</RecordType>
<RecordSource>Tds</RecordSource>
<Spid>118</Spid>
<SniConnectionId>5124905D-D1EC-460E-AD78-201050B78C67</SniConnectionId>
<OSError>0</OSError>
<SniConsumerError>18452</SniConsumerError>
<SniProvider>7</SniProvider>
<State>1</State>
<RemoteHost>10.0.0.21</RemoteHost>
<RemotePort>5008</RemotePort>
<LocalHost>10.1.0.38</LocalHost>
<LocalPort>1433</LocalPort>
<RecordTime>6/6/2012 21:14:57.527</RecordTime>
<TdsBuffersInformation>
<TdsInputBufferError>0</TdsInputBufferError>
<TdsOutputBufferError>0</TdsOutputBufferError>
<TdsInputBufferBytes>120</TdsInputBufferBytes>
</TdsBuffersInformation>
<TdsDisconnectFlags>
<PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled>
<DisconnectDueToReadError>0</DisconnectDueToReadError>
<NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream>
<ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin>
<SessionIsKilled>0</SessionIsKilled>
<NormalDisconnect>0</NormalDisconnect>
</TdsDisconnectFlags>
</ConnectivityTraceRecord>
<Stack>
<frame id="0">0X000000000174C34B</frame>
<frame id="1">0X0000000001748FDD</frame>
<frame id="2">0X0000000002461001</frame>
<frame id="3">0X0000000000C47E98</frame>
<frame id="4">0X00000000008015AD</frame>
<frame id="5">0X0000000000801492</frame>
<frame id="6">0X00000000003CBBD8</frame>
<frame id="7">0X00000000003CB8BA</frame>
<frame id="8">0X00000000003CB6FF</frame>
<frame id="9">0X00000000008E8FB6</frame>
<frame id="10">0X00000000008E9175</frame>
<frame id="11">0X00000000008E9839</frame>
<frame id="12">0X00000000008E9502</frame>
<frame id="13">0X0000000074E437D7</frame>
<frame id="14">0X0000000074E43894</frame>
<frame id="15">0X00000000775A652D</frame>
Somehow all the errors show error number 18452 whereas I never found this error in my SQL logs where I see only 18456.
I am just stuck on a dead end because this connection issue appears intermittently. Sorry for a long question but I hope if you read this, you can make out that I tried a lot at my end before giving up.
