2

Recently, and on more than one occasion now, we have attempted to connect from one of our SQL Server instances to another using SSMS only to be greeted with the following error:

A severe error occurred on the current command. The results, if any, should be discarded.

enter image description here

The following error occurs when we attempt to connect using SSMS locally:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

enter image description here

Connections via the Dedicated Administrative Connection (DAC) result in the same errors. SQLCMD reveals the following error:

enter image description here

SQL Server Configuration Manager reports that both the SQL Server service and the SQL Server Agent service are running.

The SQL Server error log stops at the same time as entries in the Event Viewer begin to be logged, reporting connection issues for SQL Server Agent jobs.

SQL Server Scheduled Job 'sp_WhoIsActive' (0x868EBDEC06F71344AC717E0C6A333D13) - Status: Failed - Invoked on: 2018-09-18 10:38:00 - Message: The job failed. Unable to determine if the owner (sa) of job sp_WhoIsActive has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).

All of our SQL Server services have been configured to run under Group Managed Service Accounts (gMSA's).

The only solution so far has been to restart the servers, but clearly this is not a satisfactory solution, especially since the issue has now occurred more than once.

The only thing we have to go on is that there are numerous occurrences for the following error message in the SQL Server Error log before the connection issues occur.

2018-09-17 13:31:19.20 Logon Login failed for user '[domain][gMSA account for SQL Agent]$'. Reason: Failed to open the explicitly specified database '[database_name]'. [CLIENT: [local machine]] 2018-09-17 13:32:03.63 Logon Error: 18456, Severity: 14, State: 38.

Is it possible that the maximum number of connections has been exceeded as a result of these login issues? If so, why is it still not possible to connect via the DAC?

Is anyone please able to advise?

Further details:

  • Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64) Feb 24 2018 13:53:17 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
  • SQL Server Management Studio v17.8.1
  • Maximum number of user connections is set to unlimited (0).
  • Shared Memory, TCP/IP and Named Pipes are all enabled.
|improve this question
1

When applying any changes to the SQL Server services it is advisable to do this via the SQL Server xxxx Configuration Manager to guarantee that any related permissions (files / directories / databases / instances) and policies are set correctly.

Fixing Service Accounts (Permissions and All)

We will reset all the permissions for files, directories and registry settings incl. any policies that may exist according to the requirements of the service accounts by following these quick steps.

Change the Service Accounts to Local Accounts

  1. Open up the SQL Server 2016 Configuration Manager and navigate to the SQL Server Services branch.

  2. In the right pane of the Configuration Manager right-click on the SQL Server service and select Properties.

  3. In the Properties of SQL Server (InstanceName) window check that you are in the Log On tab.

  4. Change the selector to Built-in account and select one of the three options:

    • Local System
    • Local Service (preferred; least privileged)
    • Network Service

  5. Press Apply and when I dialog pops up notifying you that "...any change you have made will not take effect until you reboot" click on OK.

  6. Stop the SQL Server Service (InstanceName)

  7. Start the SQL Server Service (IntanceName)

  8. Double-check the SQL Server ERRORLOG for any issues.

  9. Repeat steps 2 to 8 for the SQL Server Agent (InstanceName) service.

Change the Service Accounts back to Group Managed Service Accounts

  1. Open up the SQL Server 2016 Configuration Manager and navigate to the SQL Server Services branch.

  2. In the right pane of the Configuration Manager right-click on the SQL Server service and select Properties.

  3. In the Properties of SQL Server (InstanceName) window check that you are in the Log On tab.

  4. Change the selector to This account and enter the details of your gMSA for the SQL Server (InstanceName) service.

  5. Press Apply and when a dialog pops up notifying you that "...any change you have made will not take effect until you reboot" click on OK.

  6. Stop the SQL Server Service (InstanceName)

  7. Start the SQL Server Service (IntanceName)

  8. Double-check the SQL Server ERRORLOG for any issues.

  9. Repeat steps 2 to 8 for the SQL Server Agent (InstanceName) service.

The services should now have been re-configured according to the requirements of the individual services.

|improve this answer
  • Thank you, I can confirm that SQL Server Configuration Manager has been used for changing the startup accounts under which the SQL Server services run under. – James D Sep 18 '18 at 13:37
0

We strongly suspect that this issue may have been related to the numerous connection errors highlighted in the SQL Server Error log (see original post above) which preceded the issue. The maximum number of connections may have been exceeded.

We have since restarted the database server and have resolved the connection issues. Will revisit this post if this proves not to be the issue!

|improve this answer

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.