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.
