April 7, 2022 at 3:52 pm
SQL 2019 instance is on a VM up in Azure (OS is Win 2019 Datacenter 64 bit). I setup a linked server with the Microsoft OLE DB Provider for SQL Server back to an on premises SQL 2017 server running Win 2016 standard. When running a simple SELECT using the linked server, the query will connect, retrieve a few rows from the result set, and then terminate, providing these messages in SSMS:
OLE DB provider "MSOLEDBSQL" for linked server "XXXAPP1" returned message "Protocol error in TDS stream".
OLE DB provider "MSOLEDBSQL" for linked server "XXXAPP1" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "MSOLEDBSQL" for linked server "XXXAPP1" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "MSOLEDBSQL" for linked server "XXXAPP1" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Other times the query will complete correctly, but over 90% of the time it doesn't. Could this be a TLS or Certificate problem? How can I capture this behavior in a trace--from the client or the on prem server? I'm seeing similar errors from other Azure VMs trying to connect to this server, so it seems it may not be a problem on the 2019 machine but the 2017 one?
Thank you.
Thanks in advance for any guidance.
bigcraiginjax
April 8, 2022 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 18, 2022 at 5:12 pm
Since you are seeing problems from other VM's, I would suspect it is NOT the 2019 box but the 2017 box. BUT you can't rule out that your Azure just isn't set up correctly.
For troubleshooting this, my first step would be to reach out to your networking and VM teams to get some analytics in place to see what is happening to cause the error. If that is on you, then I would expect that you would have tools already that you can use to monitor what is going on. Having the tools (such as wireshark) is one thing - knowing how to interpret the results is a completely different beast. Your question is a BIG one and is likely to not have many forum users jumping on and helping you as this sounds like consultant work.
Now, as for if this could be a TLS or cert problem? Yep - I could see that. I could also see it being a firewall problem, an intrusion detection system problem, a configuration problem, etc.
My first step would be to review ALL logs (SQL, server, client, etc) to see what the logs say. ONE of those logs should have enough detail for you to take some action to correct the problem. Since the problem seems to be on the 2019 machine BUT could be on the 2017 machine, I would review all logs on both of those machines as well as any interfaces between those 2 machines and go from there. It'll take time and effort to review the logs and the errors that come up at the time, but that is the steps I would take.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 18, 2022 at 8:21 pm
Mr. Brian Gale,
Thank you so much for your well thought out and detailed response. I'll be sharing these ideas/suggestions back with our network/VM environment folks and see what we can monitor and learn more about. If we find a solution, I'll post back in. This is truly a frustrating one for our teams.
bigcraiginjax
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply