SQL 2008 r2 conn => SQL 2016


Is there any other than the following is/are required to connect 2 servers,  sql 2008 r2 to 2016? :
> TCPIP check
> latest native client 13 on 2008 r2 box  check
> same network check
> account and services check
> allowing remote access / login   check
> sql browser on   check
> agent running check
> local and/or AD accounts match and check
> sql admin account   check

still getting the error 10054,   some message on connection is established but the host has refused.. or something like that.
Any ideas guys?


John EsraeloDatabase / SQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I assume you are talking about a linked server connection from 2008r2 to 2016.
If on the machine running SQL 2008r2, can you access 2016 via e.g. sqlcmd or SSMS?
John EsraeloDatabase / SQL DeveloperAuthor Commented:
My apologies,   I was in a rush to submit and neglected a piece.
I am using 2008 r2 management studio and trying to connect to a 2016 sql server default instance.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
With Object Explorer or a query window? I don't see why a query window should not work. Object Explorer might have some issues (but should not).
Having said that, 10054 is a network protocol error. You might want to look into 2016 log if you can see an active rejection of the connection. I would also try to connect with sqlcmd for test.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

John EsraeloDatabase / SQL DeveloperAuthor Commented:
I will try sqlcmd and let you know how that goes.  Not sure if I can log onto the 2016 box but I will try finding out more info on that too.
be back shortly
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I got the following by using the sqlcmd
C:\Users\jesraelo>sqlcmd -S sqlsvr06
HResult 0x1A, Level 16, State 1
Client unable to establish connection because an error was encountered during ha
ndshakes before login. Common causes include client attempting to connect to an
unsupported version of SQL Server, server too busy to accept new connections or
a resource limitation (memory or maximum allowed connections) on the server.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Client unable to establ
ish connection.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Client unable to establ
ish connection due to prelogin failure.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Well, Native Client 10 definitely allows connecting with MSSQL 2016 (Just tested with 2008 sqlcmd). A 2016 sqlcmd will have the same issue, I suppose.

To narrow down the culprit, try connecting from the 2016 machine to itself, and from a different computer, all using sqlcmd.

If you cannot see anything in the SQL Server 2016 error log, and are not able to test as above, the only way I know to get details is by capturing the network traffic on one of both servers, and analyze the TCP traffic. However, that requires advanced network knowledge.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I am on it

be back
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Hi  Qlemo,

It was networking blockage for certain 2 servers .
we should be good to go at this point.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Remote Access

From novice to tech pro — start learning today.