I have a SQL Clustered instance in a hyper-v, virtualized environment. Every server (5 of them) except for one server cannot connect to the default SQL Cluster Instance via TCP/IP on port 1433 using just the name of server (i.e. SQLDEVCLUSTER) in the connection string. I keep getting the following error. It can't be the SQL Cluster since all other servers are connecting to it just fine and everything works normal. Not having a lot of luck with connections this month.
Steps I have taken to determine issue:
- Test with telnet to the server using the IP:Port - successful
- Test with packet Sender, send packets to the IP:Port - successful
- Run powershell script to connect to SQL Cluster Instance using just the Servername - fails
- Run powershell script to connect to SQL Cluster Instance using Servername:Port - successful
- Turned off firewall completely
- All TCP\IP protocols are enabled in SQL Config
- Verified the port in SQL Config
- Started, stopped, disabled, enabled SQL Browser several times
- Verified the port is listening using netstat -an and netstat -ano
At first I thought something with the cluster was off, but 4 machines have no issues. So I have to surmise that it's that one application server. If I can't figure it out, I am blowing the server away and just building new. Any thoughts? Just wondering if there is a setting on the app server VM that I missed.
Error associated with connection failure when Powershell fails most it has to do with not being able to obtain the data for the query I posted that script below:
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that
the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)"
At line:16 char:2
+ $SqlAdapter.Fill($DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Expand|Select|Wrap|Line Numbers
- $SqlServer = “SQLDEVCLUSTER”
- $SqlCatalog = “MASTER”
- #########-----Check connection and grab some data-------------------
- $SqlQuery = “Select top 1 object_id from sys.objects"
- $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
- $SqlConnection.ConnectionString = “Server = $SqlServer; Database = $SqlCatalog; 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]
- $RunDateLong = Get-Date
- $RunDateShort = $RunDateLong.ToShortDateString()
- $results = $DataSet.Tables | format-table -autosize | out-string
- $body1 =”$results”