June 7, 2012 at 10:24 am
Hi,
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 n 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:
<Record id="12" type="RING_BUFFER_CONNECTIVITY" time="1509472">
<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>
</Stack>
</Record>
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.
Regards
Chandan
June 7, 2012 at 2:08 pm
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.]
This error message is misleading, this can happen even SQL server is configured with Mixed mode, when you are creating DSN ODBC with sql authentication and in the application when you refer this ODBC and DONOT provide PASSWORD in connection string.
Note that ODBC does not store password for security reason, you have to provide when you use sql login.
June 7, 2012 at 2:13 pm
Also, it looks like you're using a SQL Server 2000 client to connect to a 2008R2 server. That should probably be upgraded to the SQL Server 2008 Native Client. It may not have anything to do with the current issue, but situations of that type of mismatch have never worked well, in my experience.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply