We help IT Professionals succeed at work.

SQL Server 2008 disconnects windows logon clients but not sql logon clients

MakePelto
MakePelto used Ask the Experts™
on
SQL Server 2008 drops connections when clients use Windows logon but not when then use sql logon!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Can you use Profiler to monitor both Windows and SQL Logins which can give you some clue about the reason for disconnects..

Author

Commented:
No, I cannot.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Can you mention why you can't because I doubt there is no other way to identify it..

Author

Commented:
What is a Profiler?

My setup is :

Zywall 5 DHCP:
192.168.100.1


Windows 2008 server which has Sql server 2008::
Name: SERV2
Sql name: NKM
192.168.100.2
255.255.255.0
192.168.100.1

Workstation Windows XP:
192.168.100.xx
255.255.255.0
192.168.100.1
Sql login:  SERV2\NKM

Connection with Windows login to sql server last about 10-15 min!

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
oh, ok.. You are not aware of Profiler tool and this can help you get started with Profiler.

http://dotnetslackers.com/articles/sql/Introduction-to-SQL-Server-2008-Profiler.aspx

Using the above link, monitor your SQL Server 2008 and analyze your trace for Audit Logout events and check what event occurs before Audit Logout so that it causes disconnection of your Windows users.

Do Audit your SQL Logins as to why it is not disconnected..

Author

Commented:
Had a summer holiday, here is a trace file from profiler. Application stops at a line near the end where NTUserName is Kassa1, CPU has a value of 203, Reads is 6319 and Duration is 1910780. With a Windows logon the application works about 40 mins perfectly but then stops with a message of SQLServer disconnection. With SQLServer logon the application works indefinitly. I include the trace file trc renamed with txt extension.
Trace-stoptrc.txt
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Profiler trace shows Audit Login and Logout events arising and hope you have some application configured to use this database or server using a connection pool since all events are triggered in a single spid by using connection reusing..

And in the mean time, what is the max. no. of connections configured in your server..

Author

Commented:
Resolved the problem. It was related to DNS.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Glad  to see you resolved the issue..
Kindly request you to close this question accordingly..