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.
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)
Connections via the Dedicated Administrative Connection (DAC) result in the same errors. SQLCMD reveals the following error:
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.