Sql-server – Configuring SQL Server 2016 to allow remote connections

sql serversql-server-2016windows-server

I have SQL Server 2016 installed on Windows Server 2016 Core. Locally, it seems to be working. I can connect using SQLCMD.exe and do some basic selects and what not.

From remote, I cannot connect. Using SQLCMD.exe on a remote machine:

sqlcmd -S boldiq_db3
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes 
    Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout 
    expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related
    or instance-specific error has occurred while establishing a connection 
    to SQL Server. Server is not found or not accessible. Check if instance 
    name is correct and if SQL Server is configured to allow remote 
    connections. For more information see SQL Server Books Online..

And from SQL Server Management Studio:

In the installation INI I installed from, I have:

TCPENABLED=1

So, I believe TCP is enabled. It might be a Firewall issue, but I've run:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

Which appears to have completed successfully. Are there any other steps to diagnose where the problem is happening? Thanks!

Best Answer

Figured it out! Since the server is running on a remote VMWare ESXi instance, the subnets are completely different. I changed:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

To:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = any profile = DOMAIN

And everything immediately started working. Ugh, firewalls.

Related Solutions

Sql-server – How to allow remote connection in SQL Server 2008 Express

Go into the SQL Server Configuration Manager (SSCM) and under Service Protocols enable TCP/IP for your SQL Server Express instance (most likely called SQLEXPRESS).

Find SQL Server Version/Edition Without SSMS Installed

This can be done through WMI (accessed through PowerShell in my below example). All I'm doing is looking at a property ("SKUNAME") of the SQL Server service, via the SqlServiceAdvancedProperty class. Note, there are a few environment-specific variables that would need to be set accordingly at the top of the code.

$ComputerName = "YourComputerName"
$ServiceName = 'YourEngineServiceName'
$PropertyName = "SKUNAME"

# retrieve the most current version of the ComputerManagement namespace
#
$ComputerManagementNamespace =
    (Get-WmiObject -ComputerName $ComputerName -Namespace "root\microsoft\sqlserver" -Class "__NAMESPACE" |
        Where-Object {$_.Name -like "ComputerManagement*"} |
        Select-Object Name |
        Sort-Object Name -Descending |
        Select-Object -First 1).Name
 
if ($ComputerManagementNamespace -eq $null) {
    Write-Error "ComputerManagement namespace not found"
}
else {
    $ComputerManagementNamespace = "root\microsoft\sqlserver\" + $ComputerManagementNamespace
} 

# get the property and its value
#
Get-WmiObject -ComputerName $ComputerName -Namespace $ComputerManagementNamespace -Class "SqlServiceAdvancedProperty" |
    Where-Object {
        $_.ServiceName -eq $ServiceName -and
        $_.PropertyName -eq $PropertyName
    } |
    Select-Object @{Name = "ComputerName"; Expression = { $ComputerName }},
        ServiceName,
        @{Name = "PropertyValue"; Expression = {
            if ($_.PropertyValueType -eq 0) {
                $_.PropertyStrValue
            }
            else {
                $_.PropertyNumValue
            }
        }}

Likewise, this same information can be found directly in the SQL Server Configuration Manager tool. Once you open it up, right-click on your SQL Server service and go into Properties. Then click on the Advanced tab and look at the Stock Keeping Unit Name key. There will you find what edition you are using.

Related Question