Unable to connect from SQL Server Management studio 2016 to SQL Server 2008 R2 database Server

Question

Post reply

Unable to connect from SQL Server Management studio 2016 to SQL Server 2008 R2 database Server

shamshad.ali

Hall of Fame

Points: 3850
More actions
June 25, 2018 at 3:13 am

#355792

I am unable to connect to a database server running :
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
from SSMS 2016 and getting following error:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

I tried to check failed login (configured on server to log failed logins), there is nothing appearing in log.
I checked ports and checked telnet to see if ports are blocked, SQL server configuration tool, for TCP etc. everything is OK, this database server is connected to other clients without any issue, I just tried with SSMS 2016 and getting error. The login I tried with SQL and windows both having same error. Kindly help to solve this issue.
Regards,
Shamshad Ali

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,
check you TLS Setting on the client and the sql server.
This could the reason for the error.
Kind regards,
Andreas

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

Your issue is TLS related, not so much version or login specific, review all the known issues and fixes with TLS connections here.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 3

shamshad.ali

Hall of Fame

Points: 3850

June 25, 2018 at 3:29 am

#1995104

how to check TLS settings on client and server ?

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 4

On client and server both have following ;

Force Protocol Encryption - OFF
Trust Server Certificate - OFF

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 5

Do I need to run any update on the database server running SQL Server 2008 R2?

You will need to apply the necessary client component fixes on the server that hosts the SQL Server instance (eg. MS ODBC Driver, SQL Server Native Client) to ensure that the client components installed on the server also support TLS 1.2.

Installing any update is not allowed on db server running 2008 R2 because it is 3rd party configured and we only want to setup connecting this server for reporting (service using 2016 version) purpose. Is there any workaround for this?
I also see following link

https://support.microsoft.com/en-us/help/3099845/hotfix-rollup-3099845-for-the-net-framework-4-5-2-4-5-1-and-4-5

This hotfix enables Transport Layer Security (TLS) 1.1/1.2 for SQL Server connectivity in System.Data.dll.

This is risky and can't do becase several third party applications are using database running on this DB servrer.
Please help.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

shamshad.ali - Monday, June 25, 2018 3:42 AM
Do I need to run any update on the database server running SQL Server 2008 R2?
You will need to apply the necessary client component fixes on the server that hosts the SQL Server instance (eg. MS ODBC Driver, SQL Server Native Client) to ensure that the client components installed on the server also support TLS 1.2.

Installing any update is not allowed on db server running 2008 R2 because it is 3rd party configured and we only want to setup connecting this server for reporting (service using 2016 version) purpose. Is there any workaround for this?
I also see following link
https://support.microsoft.com/en-us/help/3099845/hotfix-rollup-3099845-for-the-net-framework-4-5-2-4-5-1-and-4-5
This hotfix enables Transport Layer Security (TLS) 1.1/1.2 for SQL Server connectivity in System.Data.dll.
This is risky and can't do becase several third party applications are using database running on this DB servrer.
Please help.

If no changes are allowed due to it being a vendor's system, you should contact that vendor.

Sue