3

We started getting Alerts:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

With a help of SQL job and holding table, we managed to find out that it's the SQL Server Service Account that tries to connect, approx every 4 hours e.g. 12:00 16:00 and so on, with a program name: Net SqlClient Data Provider. I've added the host_process_id column as per jadarnel27 suggestions, identified the PID, but there were no processes with the same PID in the task manager. Oddly enough, there are no entries in the log: "Dedicated admin connection support was established", just the following ones:

16:00:05 Login succeeded for user 'sql service user'. Connection made using Windows authentication. [CLIENT: ip address of Windows Failover Cluster Virtual Adapter]

16:00:05 Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

16:00:11 Dedicated administrator connection has been disconnected. This is an informational message only. No user action is required.

No clues in Windows Event Viewer logs or Windows Cluster logs. This is only happening on an active node of AlwaysOn Availability Group. No jobs/port scanning configured to run at those times, nor any other tasks. I have seen How do we find who used Dedicated Admin Connection but we know what account has used DAC, just don't understand why. How can we troubleshoot the issue further?

EDIT: I've found the cause of the problem, it was a powershell script that synchronises server objects for Availability Groups, it was set to run on port 1433 and I have no idea why it tried to connect to DAC, however simple reboot has solved the issue! Thanks ever so much to everyone who replied!

1

I recon that this is port/virus scanner that your security team might be using to find out vulnerabilities e.g. weak passwords. I had the same issue and eventually I figured out that it was the port scanner.

Look into windows event log as well.

  • Thanks Kin! No port scanning taken place at those times. – VitalicJJ Jan 7 at 17:31
1

As Kin mentioned in his answer, this appears to be some kind of virus scanner or port scanner that's running locally on the SQL Server box. Especially given these clues:

  • Comes from two different local IP addresses
    • The IP address of the Windows Failover Cluster Virtual Adapter
    • The classic "localhost" IP of 127.0.0.1
  • The login name is the same as the SQL Server service account

Whatever it is appears to be configured to run under the same Windows account as the SQL Server service, which is causing some confusion.

Given those pieces of info, what else can you do? Since you're logging dm_exec_sessions to a table to capture this info already, make sure you get the host_process_id column. This should be the biggest clue as to what process is actually making these connections. Then look through the task manager and find what process has that same PID.

  • Thanks jadarnel27! I've added host_process_id column as you suggested, identified the PID, but there was no processes with a same PID in the task manager. – VitalicJJ Jan 9 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.