How to configure SQL Server 2012 Core for connections from a remote machine

  • Can anyone tell me how to configure SQL Server 2012 Core for connections from a remote machine?

    Local connections using sqlcmd.exe on the Core machine work fine using: sqlcmd.exe -S MYSERVERNAME\MYINSTANCENAME -E -Q "select @@version"

    -------------------------------------------------------------------------------------------------------------

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

    Oct 19 2012 13:38:57

    Copyright (c) Microsoft Corporation

    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    (1 rows affected)

    Based on a link I found, I enabled TCP/IP on the Core machine with the following code:

    $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='MYINSTANCENAME']/ServerProtocol[@Name='Tcp']"

    $Tcp = $wmi.GetSmoObject($uri)

    $Tcp.IsEnabled = $true

    $Tcp.Alter()

    $Tcp

    I presume that this code is hanging for some reason because the output "State" is always set to "Creating." (Please see the last line in this output.)

    Parent : Microsoft.SqlServer.Management.Smo.Wmi.ServerInstance

    DisplayName : TCP/IP

    HasMultiIPAddresses : True

    IsEnabled : True

    IPAddresses : {IP1, IP2, IP3, IP4...}

    ProtocolProperties : {Enabled, KeepAlive, ListenOnAllIPs}

    Urn : ManagedComputer[@Name='MYSERVERNAME']/ServerInstance[@Name='MYINSTANCENAME']/ServerProtocol[@Name='Tcp']

    Name : Tcp

    Properties : {Name=DisplayName/Type=System.String/Writable=False/Value=TCP/IP, Name=HasMultiIPAddresses/Type=System.Boolean/Writable=False/Value=True, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True}

    UserData :

    State : Creating

    Output from SQLDiag on the Core machine indicates that SQL Server is Listening on dynamic port 49189.

    Here is the output from netstat -ano| findstr 49189:

    TCP 127.0.0.1:49189 0.0.0.0:0 LISTENING 1308

    TCP [::1]:49189 [::]:0 LISTENING 1308

    I think the problem is that the remote machine is trying to connect on port 1433 (which is what I want so that I can emulate my work environment), but the Core machine is listing on dynamic port 49189.

    How can I change the Core machine, which does not have the SQL Server Configuration Manager installed, to listen on static port 1433?

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply