Wednesday, December 21, 2016

WS 2016 Server Core + SQL Server 2016 Installation. How To Enable Remote Management Of SQL

Update 21-21-2016: As it turned out, additional ports have to be opened as well. Therefore I’ve updated this posting accordingly. Please know that this posting came to be using different resources, so don’t think I invented the wheel myself. As such I’ve updated the section ‘Used resources’ as well.

Issue
Suppose you’ve rolled out a VM with Windows Server 2016 Core and deployed on that same VM SQL Server 2016 (with the command line setup.exe /UIMODE=EnableUIOnServerCore /Action=Install).

Another VM runs Windows Server 2016 with Desktop Experience  and is used as a Stepping Stone server, hosting all kinds of Consoles in order to manage the products/services hosted by many other VMs running the Core installation option.

On that server you start SQL Server Management Studio and want to connect to the previously installed SQL instance. However, all you get is this error message: ‘…Cannot connect to [SQL instance]. 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)…’
image

Cause
When you’ve configured the SQL instance correctly during installation so that the account you’re using has access permissions, SQL and the VM hosting it, require additional configuration in order to access it remotely by SQL Server Management Studio.

Without the additional configuration you can’t access the SQL instance remotely.

Solution
Follow these steps and when done correctly, you’ll be able to access the SQL instance remotely by using SQL Server Management Studio.

  1. Ascertain that the SQL Server Browser Service is running and set to start automatically
    Connect with Service Manager to the related VM and check out the SQL Server Browser Service. Correct when required so the service is running and set to start automatically.

  2. Enable remote connections on the instance of SQL Server
    Do this locally on the VM hosting the related SQL instance. Use SQLCMD.exe locally and execute the following statements against the Server Core instance:
    EXEC sys.sp_configure N'remote access', N'1'
    GO

    RECONFIGURE WITH OVERRIDE
    GO

  3. Enable TCP/IP on the Instance of SQL Server
    Do this locally on the VM hosting the related SQL instance. Start PowerShell when logged on.
    Import SQL PS Module (Import-Module SQLPS) and run this PS script (copy & paste works Smile):
    $smo = 'Microsoft.SqlServer.Management.Smo.' 
    $wmi = new-object ($smo + 'Wmi.ManagedComputer') 
    # Enable the TCP protocol on the default instance.  If the instance is named, replace MSSQLSERVER with the instance name in the following line. 
    $uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']" 
    $Tcp = $wmi.GetSmoObject($uri) 
    $Tcp.IsEnabled = $true 
    $Tcp.Alter() 
    $Tcp

  4. Create exceptions in Windows Firewall
    Do this locally on the VM hosting the related SQL instance. Start PowerShell when logged on.
    These two lines will allow remote access to the default SQL instance over TCP port 1433:
    netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

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

    These two lines will allow remote access from SQL Server Management Studio to the SQL instance over TCP Port 1434 (aka SQL Admin Connection):
    netsh firewall set portopening protocol = TCP port = 1434 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

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

    These two lines will allow SQL Broker traffic over TCP Port 4022:
    New-NetFirewallRule -DisplayName "Allow inbound SQL Broker Traffic (TCP Port 4022)" -Direction inbound –LocalPort 4022 -Protocol TCP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound SQL Broker Traffic (TCP Port 4022)" -Direction outbound –LocalPort 4022 -Protocol TCP -Action Allow

    These two lines will allow SQL-Transact traffic over TCP Port 135:
    New-NetFirewallRule -DisplayName "Allow inbound SQL-Transact Traffic  (TCP Port 135)" -Direction inbound –LocalPort 135 -Protocol TCP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound SQL-Transact Traffic  (TCP Port 135)" -Direction outbound –LocalPort 135 -Protocol TCP -Action Allow

    These two lines will allow SQL Browser traffic over TCP Port 2382:
    New-NetFirewallRule -DisplayName "Allow inbound SQL Browser TCP Traffic (TCP Port 2382)" -Direction inbound –LocalPort 2382 -Protocol TCP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound SQL Browser TCP Traffic (TCP Port 2382)" -Direction outbound –LocalPort 2382 -Protocol TCP -Action Allow

    These two lines will allow SQL Browser traffic over UDP Port 1434:
    New-NetFirewallRule -DisplayName "Allow inbound SQL Browser UDP Traffic (UDP Port 1434)" -Direction inbound –LocalPort 1434 -Protocol UDP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound SQL Browser UDP Traffic (UDP Port 1434)" -Direction outbound –LocalPort 1434 -Protocol UDP -Action Allow

    !!!Only when required!!!
    These two lines will allow web traffic over TCP Port 80 (e.g for SSRS instances):
    New-NetFirewallRule -DisplayName "Allow inbound HTTP Traffic (TCP Port 80)" -Direction inbound –LocalPort 80 -Protocol TCP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound HTTP Traffic (TCP Port 80)" -Direction outbound –LocalPort 80 -Protocol TCP -Action Allow

    !!!Only when required!!!
    These two lines will allow secure web traffic over TCP Port 443 (e.g for SSRS instances):
    New-NetFirewallRule -DisplayName "Allow inbound HTTPS Traffic (TCP Port 443)" -Direction inbound –LocalPort 80 -Protocol TCP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound HTTPS Traffic (TCP Port 443)" -Direction outbound –LocalPort 80 -Protocol TCP -Action Allow

    !!!Only when required!!!
    These two lines will allow SQL Analysis traffic over TCP Port 2383:
    New-NetFirewallRule -DisplayName "Allow inbound SQL Analysis Traffic (TCP Port 2383)" -Direction inbound –LocalPort 2383 -Protocol TCP -Action Allow

    New-NetFirewallRule -DisplayName "Allow outbound SQL Analysis Traffic (TCP Port 2383)" -Direction outbound –LocalPort 2383 -Protocol TCP -Action Allow

  5. Allow WMI traffic
    When installing SCOM 2016 for instance, WMI traffic has to be allowed. By default the Windows Firewall on the SQL box blocks it, stopping the installation of SCOM 2016. With this PS oneliner WMI traffic is allowed.
    netsh advfirewall firewall set rule group="Windows Management Instrumentation (WMI)" new enable=yes

No restart is required. Now all required SQL and WMI traffic to the SQL server is allowed.

Used resources

Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)