Connect to SQL Server using I/P address in SSMS

  • Hi All,

    I have 2 instances of SQL Server installed on my desktop Instance1 name being default and Instance 2 name being Katmai. I am able to connect to default instance using i/p address but coming to the Instance 2, I'm not able to connect to it even if I give i/p address with port number associated with Instance2. can any suggest me how to resolve this issue, please.

    Thanks,

    Ravi

  • Hi Ravi,

    You have not mentioned the what version of SQL Server Installed on Instance1 and Instance2.

    Could you please clarify them. also provide the Screenshot of your errors.

    Hope you have checked the SQL Service running state and make sure that if your Named instance is SQL 2005 or above please start the SQL Server Browser service and try connect your SQL Named instance.

  • RaviShankar1234 (10/13/2010)


    I'm not able to connect to it even if I give i/p address with port number associated with Instance2

    what format are you using to specify the ip and port in SSMS. Also where did you look to get the current port number?

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I'm using SQL Server 2008 server and coming to finding out the port number I'm using SQL Server Configuration Manager.

    For default instance when I try to connect using I/p address it connects well with the windows authentication. but I have a Second instance for this instance if I try to connect using I/p address and port number I'm not able to connect.

    Thanks,

    Ravi

  • Yes and how are you specifying the IP and port number, what are you typing into the connection dialog box?

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • In SSMS, Connect to Server pane I'm giving the details as 192.X.X.X,PortNumber this is what Exactly I'm giving in the Server name with Windows authentication. For Default instance when I try with I/P address it successfully connects.

    I tried to give like this too but its not working either 192.X.X.X:PortNumber

    Thanks,

    Ravi

  • Hmm looks correct, that's the format I use

    192.X.X.X,port

    Have you set a static tcp port for the named instance or is it still set to dynamic?

    Also have you checked the event and SQL server logs for any potential problems when attempting connection?

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Ravi,

    What Operating System are you using on your desktop?

    Is the Windows firewall preventing you from accessing this named instance?

    Check Windows & SQL logs for errors.

  • Hi,

    1. Enable TCP/IP Protocol on both Instances

    2. 1st Instance is working on 1433 port

    3. for 2nd Instance Configure New port xxxx like 9125 (http://msdn.microsoft.com/en-us/library/ms177440.aspx)

    4. Reset/restart 2nd Instance

    5. connect 2nd instance by specifying IP and new port port like xxx.xxx.xxx.xxx, 9125

    I hope you will connect.

    http://programming.top54u.com/post/Configuring-TCP-IP-Port-for-SQL-Server-2005.aspx

    Ram
    MSSQL DBA

  • make sure the SQL Server Browser service is running.

  • Geoff A (10/15/2010)


    make sure the SQL Server Browser service is running.

    This is not required when specifying the ip address and port!

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

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