February 23, 2013 at 8:32 pm
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