Post reply

assigning a TCP/IP port number to the SQL Server Database Engine

  • July 20, 2010 at 2:00 am

    #235512

    I have a doubt. Generally we configure a particular static port for listening to the server through TCP\IP. It suddenly started throwing error for client connection saying

    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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

    Then we assigned the same port in Dynamic TCP port under IPALL category. Though it didn't listen to the port assigned but the client connections was made. How it works.

    I read that for configuring dynamic port to a named instances we need to define 0. But in my case I din't define 0 but some other number. How it works with the case we define other port than 0.

    I tried to go through several blogs and book online but couldn't find much information. Can anyone help with this?

  • July 26, 2010 at 3:25 pm

    #1198463

    Hi,

    Are you using SQL Server Developer edition by any chance?

    Have you confirmed that the SQL Server Browser service is running?

    The SQL Server log should give you some clues, take a look in there immediately following starting the instance. You should see something along the lines of "Server is listening on....".

    This should help.

    Which port are you trying to manually assign to SQL Server? Could it be in use by something else?

    Have you looked within NETSTAT on the command line to check your port usage?

  • April 5, 2011 at 4:37 am

    #1307364

    Hi,

    From the subject TCP port, i would like to automate change for default instance name tcp port and disable dynamic ports for security reason.

    Actually, change manually the options is working fine.

    But my goal is to automate it in powershell v1 on Windows 2003 R2 SP2 for SQL 2005 Server SP4 and .NET 3.5 SP1.

    Here is my script using SMO : (i'm system engineer, not dba...)

    $smo = 'Microsoft.SqlServer.Management.Smo.'

    $wmi = new-object ($smo + 'Wmi.ManagedComputer').

    # List the object properties, including the instance names.

    $Wmi

    # Enable the TCP protocol on the default instance.

    $uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name=`'$instancename`']/ServerProtocol[@Name='Tcp']"

    $Tcp = $wmi.GetSmoObject($uri)

    $TcpPort = $Tcp.IPaddresses[2].IPaddressproperties[1]

    $TcpPort.value = "1433"

    But $tcpPort can't accept Alter() method.

    Under powershell, i can read the value but under SQL Server Configuration Manager, the value is blank.

    I did restart the service SQL Server, but anyway, it doesn't work.

    If i do netstat -aon, not 1433 listening.

    May be I can"t do it or i'm wrong somewhere, i hope too...

    I will use regedit if i have no solution, but it should work...

    Thanks for your reply.

    Best regards,

    Vincent

Viewing 3 posts - 1 through 2 (of 2 total)

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