Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to configure SQL Server 2012 Core for connections from a remote machine Expand / Collapse
Posted Saturday, February 23, 2013 8:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:15 AM
Points: 399, Visits: 819
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

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              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?

Post #1423389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse