Sql-server – SQL Server 2008 R2 Remote Connection issue

Here's a powershell script I wrote for checking connectivity to SQL servers as you are using the default port (1433) all you need to do is pass in the server name. If the connection fails the exception should give you a clue as to why.

e.g. CheckSql.ps1 server\instance

CheckSql.ps1

param([Parameter(Mandatory=$true,ValueFromPipeline=$true)][string]$serverInstance);

$connectionString = "Data Source={0};Integrated Security=true;Application Name=CheckSQL.ps1" -f $serverInstance;
$sqlConn = new-object("Data.SqlClient.SQLConnection") $connectionString;

Write-Host $connectionString

try {
    $sqlConn.Open();
    $sqlConn.Close();
    Write-Host "Connected OK"-foregroundcolor white -backgroundcolor green
}
catch { 
    Write-Host $_ -foregroundcolor white -backgroundcolor red
}
finally { 
    $sqlConn.Dispose(); 
}

You need to start the SQL Browser service to enable connections to named instances.

Explanation here:

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.