locked
Problems with connection on sql express 2005 named instance over the internet RRS feed

  • Question

  • Hi,

    Can You help me with connection to sql server 2005 named instance.

    I have to redirect connection from public IP to local Ip of server with port 1433.

    Local tcp/ip connection is  1xx.1xx.xx.xx\sqlexpress. or 'computername\sqlexpress'

    On router I can only put  tcp/ip address without sqlexpress. 1xx.1xx.xx.xx.

    Can You sugest me where I am wrong. What should I try do do.

    Thanks

    Zeljko

    Thursday, June 20, 2013 11:48 AM

Answers

  • If you use instance name to connect to, then first a UDP broadcast ist done to the local SQL Server Browser-services to resolve the instance name to the ip port; may this don't work over your router. That's why I suggested to use a fix port.

    On local you could test it with the loopback device:   tcp:127.0.0.1, 1433


    Olaf Helper

    Blog Xing

    • Marked as answer by zadzich Thursday, June 20, 2013 12:13 PM
    Thursday, June 20, 2013 12:12 PM

All replies

  • Hello Zeljko,

    Instead of machine + instance name, you could use ip address + port for the SQL Server name, like:

    tcp:1xx.1xx.xx.xx, 1433

    And ensure that the ip port is unblocked in firewall / router.


    Olaf Helper

    Blog Xing


    • Edited by Olaf HelperMVP Thursday, June 20, 2013 11:57 AM additional
    Thursday, June 20, 2013 11:56 AM
  • Thanks ,

    On local network I can connect to sql server, but over the internet I can not connect to it.

    When I try local on that pc with connection string I can connect, but over the internet I can not.

    local connection is tcp:1xx.1xx.xx.xx\sqlexpress.

    local connection on tcp:1xx.1xx.xx.xx, 1433 not working - on same pc.

    Thursday, June 20, 2013 12:05 PM
  • If you use instance name to connect to, then first a UDP broadcast ist done to the local SQL Server Browser-services to resolve the instance name to the ip port; may this don't work over your router. That's why I suggested to use a fix port.

    On local you could test it with the loopback device:   tcp:127.0.0.1, 1433


    Olaf Helper

    Blog Xing

    • Marked as answer by zadzich Thursday, June 20, 2013 12:13 PM
    Thursday, June 20, 2013 12:12 PM
  • Thanks mr. Olaf

    Today I have no access to this pc. Tomorow I'll try with TeamViewer to connect to it. But the same scenario is on my PC.

    I have installed sql 2000, and sql2005 express with instance name.

    when I in server name (conn string) write xxx.xxx.xxx.xx I receive error . I suppose that 2000 works on one port and sql 2005 with instance on some other.

    computer name info2

    computer local address 192.168.100.45

    There is results with my sql manager connection tool

    connect to: info2   ok!       - sql manager connect me to sql 2000

    connect to: 192.168.100.45   ok! - sql manager connect me to sql 2000

    connect to: 192.168.100.45, 1433   ok! - sql manager connect me to sql 2000

    connect to: 192.168.100.45, 1434  not ok!

    connect to: 192.168.100.45, 1435  not ok!

    connect to: 192.168.100.45\sqlexpress05 ok! sql manager connect me to sql 2005

    connect to: INFO2\sqlexpress05 ok! sql manager connect me to sql 2005

    sql server browser (2005 is running and set to booth tcp and named)

    If I have to redirect public IP to my local 192.168.100.45 it wont work . How can I determine on which port is working my sql 2005?

    I have found in computer management - sql server configuration manager - sql server 2005 network configuratio - protocols for sqlexpress05

    on tcp/ip tab - properties - almost last property - dynamic port


    when i try 192.168.100.45,49184 - IT WORKS-

    THANSK , It seems that computer I try to use also use dynamic port. (named instance of sql server). I'll try tomorow with that pc.

    Thansk

    Thursday, June 20, 2013 12:45 PM