Jay Beta (3/22/2013)
Use powershell instead.
Thanks. That helped, but I am still unable to connect. Before I made the recommended changes, based on the link in the prior post (http://blogs.msdn.com/b/sql_protocols/archive/2008/08/29/configuring-sql-protocols-through-windows-powershell.aspx), this was the output from PortQry.exe on a remote machine:
.\PortQry.exe -e 1434 -p UDP -n server_name
Querying target system called:
server_name
Attempting to resolve name to IP address...
Name resolved to 192.168.1.140
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
Server's response:
ServerName server_name
InstanceName instance_name
IsClustered No
Version 11.0.3000.0
tcp 49188 <-- The default TCP dynamic port
5 - 2 1
==== End of SQL Server query response ====
UDP port 1434 is LISTENING
Here are the TCP changes that I made:
# Display machine information
$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .
$MachineObject
+++ Begin screen output +++
ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services : {MSSQL$instance_name, SQLAgent$instance_name, SQLBrowser}
ClientProtocols : {np, sm, tcp}
ServerInstances : {instance_name}
ServerAliases : {}
Urn : ManagedComputer[@Name='server_name']
Name : server_name
Properties : {}
UserData :
State : Existing
--- End screen output ---
# Display which protocols are enabled; be aware that you need to update the instance name to make it valid in the next command
$ProtocolUri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='instance_name']/ServerProtocol"
$tcp = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Tcp']")
# Display the properties of the "IPAll" IP Address:
$MachineObject.getsmoobject($tcp.urn.value + "/IPAddress[@Name='IPAll']")
+++ Begin screen output +++
Parent : Microsoft.SqlServer.Management.Smo.Wmi.ServerProtocol
IPAddressProperties : {TcpDynamicPorts, TcpPort}
IPAddress : 0.0.0.0
Urn : ManagedComputer[@Name='server_name']/ServerInstance[@Name='instance_name']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']
Name : IPAll
Properties : {}
UserData :
State : Existing
--- End screen output ---
# Set Server Server to listen on TCP port 1433
$MachineObject.getsmoobject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"
$tcp.alter()
$MachineObject.getsmoobject($tcp.urn.value + "/IPAddress[@Name='IPAll']")
+++ Begin screen output +++
Parent : Microsoft.SqlServer.Management.Smo.Wmi.ServerProtocol
IPAddressProperties : {TcpDynamicPorts, TcpPort}
IPAddress : 0.0.0.0
Urn : ManagedComputer[@Name='server_name']/ServerInstance[@Name='instance_name']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']
Name : IPAll
Properties : {}
UserData :
State : Existing
--- End screen output ---
The PortQry.exe output on a remote machine looks correct now, but I am still unable to connect from the remote machine:
PS C:\cron\dba> .\PortQry.exe -e 1434 -p UDP -n server_name
Querying target system called:
server_name
Attempting to resolve name to IP address...
Name resolved to 192.168.1.140
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
Server's response:
ServerName server_name
InstanceName instance_name
IsClustered No
Version 11.0.3000.0
tcp 1433 <-- This is the TCP port that I am trying to use
5 - 2 1
==== End of SQL Server query response ====
UDP port 1434 is LISTENING
Here is the connection failure message:
sqlcmd.exe -S server_name\instance_name -U sa -P sql_password -Q "select getdate()"
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
.
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..