Hi Friends,
I wish all of you are doing safe and my sincere prayers to all of them who lost their lives during this battle with the on-going Pandemic. I am not able to focus on my work as many of my families got affected and I am trying my best to come out of this.
This post is more of a questionnaire rather than a solution. I got a mail last week saying the application team are getting time out Errors when they are trying to connect to Secondary replicas for reporting purpose.
As we know my initial checks are with Always ON Routing and couldn't find any issues in that.
Later I started checking from my end and surprisingly I didn't find any issues with SQL Server Management Studio from the Jump Server.
Then I tested the same with SQLCMD and I got the below Error.
I am really not sure what went wrong as the same is working from SSMS then I tried from UDL by selecting SQL Server Native Client as the driver and I got the below error
As mentioned I am not getting the error messages from SSMS however from SQLCMD or UDL file it it not working. I don't have Azure Data Studio to test this out unfortunately.
Login time out Expired
Unable to complete login process due to delay in opening the server connection
Later I tested the same from my Monitoring server & Reporting server instead of the jump server and from both the servers the results are fine.
I understood now there lies some problem from the Jump server but how to prove as said in the title this is really weird as there are no issues from SSMS.
So I started thinking bit further and made use of -l parameter to my SQLCMD usage
This time I am able to connect to the Listener as you can see from the above image if I don't use that I got the error
I thought of doing the same for my UDL test connection too
As you can see we have an option to set the connection time out property as well as Application Intent there. And the connection went fine only if I made use of "MICROSOFT OLEDB PROVIDER FOR SQL SERVER" but not with "SQL SERVER NATIVE CLIENT 11.0".
Now the questions left with me are
1) How come SSMS is working fine?
2) Why there are no issues from Monitoring server and Reporting server but only from Jump server?
3) why only with OLEDB provider but not with Native client?
To debunk this I tried further and got the below solutions but I am not sure if these are the right answers.
1)How come from SSMS?
Because SSMS uses .Net SqlClient Data Provider below is the image which confirms the same. Also I made use of below powershell script to see if I am getting the proper server names with & with out Application Intent.
[string] $Server= "ListenerName"
[string] $Database = "DBNAME"
[string] $SqlQuery= $("SELECT @@servername")
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | out-file "C:\jump\powershell.txt"
2) Why there are no issues from Monitoring server and Reporting server but only from Jump server?
For this I ran tracert and from the jump server and from the monitoring server below is the output . The picture says it all. Form the jump server as expected I got multiple hops and some times time outs as well.
3) why only with OLEDB provider but not with Native client?
Well I don't have an answer for this but as per the below link there are no updates being shipped with SQL Server Native Client.
https://docs.microsoft.com/en-us/sql/connect/connect-history?view=sql-server-ver15
Conclusion: To Mitigate the issue I made use of -l parameter and the issue got resolved to certain extent and this is how I worked to answer my self to the questions pose while connecting to DB Engine from different tools.
Hope you Learned something new and Happy Reading. Stay Safe.
Comments