SQL Server – Unable to Login Using SSMS and SQL Agent Failing

connectivitysql serversql-server-2008-r2

I have SQL Server 2008 R2 ENT insalled on Windows server 2008 R2 ENT. I am unable to login to SQL Server locally after the installation. Getting below error:

Connection handshake failed. An OS call failed: (80090331)
0x80090331(The client and server cannot communicate, because they do
not possess a common algorithm.). State 56.

The error led me to this article and I updated my DB engine to 10.50.6542.0. But still unable to login. What should I do in order to allow my SSMS to connect locally. Also the second issue is SQL agent service is not starting. It fails with below error:

Agent could not be started ( unable to connect to server (local),
SQLserveragent cannot start).

How to fix this as well? Below is my environment details:

  • Windows server 2008 R2 Ent. SP1
  • SQL Server 2008 R2 Ent (10.50.6542.0)
  • .Net version 4.6.01055

SCHANNEL Status:

SSL 2.0: Client: DisabledByDefault = 1 Enabled = 0 Server:
DisabledByDefault = 1 Enabled = 0

SSL 3.0: Client: DisabledByDefault = 1 Enabled = 0 Server:
DisabledByDefault = 1 Enabled = 0

TLS 1.0: Client: DisabledByDefault = 0 Enabled = 1 Server:
DisabledByDefault = 0 Enabled = 1

TLS 1.1: Client: DisabledByDefault = 0 Enabled = 1 Server:
DisabledByDefault = 0 Enabled = 1

TLS 1.2: Client: DisabledByDefault = 0 Enabled = 1 Server:
DisabledByDefault = 0 Enabled = 1

Please let me know if you need any further details.


Update:

I made some progress on it. I installed ADO.NET – SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2) referring link https://support.microsoft.com/en-us/kb/3135244. I am now able to connect SQL server using SSMS locally and also able to start SQL agent service. Yipee.

But now the new issue is appearing. I can't connect the server remotely using windows authentication. I get below errors in windows eventviewer logs when I try to connect it. Also, no errors in SQL server error logs.

Log Name: System Source: Schannel Event ID: 36888
Task Category: None Level: Error Keywords: User:
SYSTEM Description: The following fatal alert was generated: 40. The
internal error state is 1205. Log Name: System Source:
Schannel Event ID: 36874 Task Category: None Level:
Error Keywords: User: SYSTEM Description: An TLS 1.0
connection request was received from a remote client application, but
none of the cipher suites supported by the client application are
supported by the server. The SSL connection request has failed.

enter image description here

Best Answer

Fix for my this question was to apply

These fixed the local login and SQL agent service issues. Then the new issue surfaced i.e. unable to SSMS this server from other servers.

To fix this I had to enable ‘KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA’ in registry. After enabling it remote connection started working fine.