Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Accessing Named instance of sql server remotely through Java Code using dynamic TCP/IP port

Pretheve
2014-06-06
2014-06-16
  • Pretheve
    Pretheve
    2014-06-06

    Is it possible to access named instance of sql server remotely through Java
    Code using TCP/IP.So far I am able to access default instance remotely but not
    named instance.Named instance now I can access only locally through Java
    Code.

     
  • Yes, it is possibly and actually pretty easy. The concept of a "named" instance does not really matter to the driver. Each Instance of MSSQL has its own TCP port. Those are eighter configured manually or determined at the first start. With the configuration manager (Protocols / TCP/IP / IP-Address "IPx" - look for the activated ip and read its port or dynamic port) you can see what ports are used. So the simplest thing is, you look up the port for your named instance you care about and put it into the jtds URL - then you dont need the instanceName parameter.

    The "namedInstance=" parameter uses a different approach. There is an additional service called the SQL Server Browser. This service is running on UDP 1434 (ms-sql-m) and answering requests for name with the actual dynamic port. So in order to make this work, you need to start this service, allow UDP access to this service, specify instanceName in JTDS URL (and I think you need to remove the port from the url).

    BTW: this browser does not answer for the default/unnamed instance. So if that one has a different port configured, you can only use it with a explicitely specified.

    http://jtds.sourceforge.net/faq.html#instanceGetInfo

    But as I said it is best to use the dynamic port directly.

    BTW: I guess this is a user question.

     
  • Pretheve
    Pretheve
    2014-06-09

    apologize for posting here. Thanks for your reply. We are really looking for the named instance approach because we are using the driver inside our monitoring solution with the dynamic port configuration. If suppose the server or the service is restarted then the port might change and our monitoring solution stops working.

    We have the SQL Server Browser service up and running on the same system where MS SQL database instance is installed. We try to connect to a named instance from a remote system using the url "jdbc:jtds:sqlserver://hostname/master;instance=MSSQLSUP". But we are getting connected to the default instance MSSQLSERVER instead of the instance MSSQLSUP that we are looking for.

    If we run our DB client locally on the system where the SQL Browser and SQL DB instance are running then it works fine.

    So, do we need to install the SQL Browser Service on the remote system from where we are trying to connect?

    Thanks in advance for your help

     
    Last edit: Pretheve 2014-06-09
    • You dont need to worry about the port to change. The MSSQL Server will remeber the "randomly chosen" port in the registry and after a restart it will use the same. If you dont want the port number to show up as "dynamic", you can always enter the dynamic number in the static port number field.

      The SQL Server Browser is normally running locally on the Server side. If it does not work remotely I guess you eighter have no open UDP port between the two machines.

      If you stop the browser service and start it on the command line, you can see what it collects and when it is questioned:

      "c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" -c

      You will see "CLNT_BCAST_EX | CLNT_UCAST_EX from 127.0.0.1[65174]" if the browser gehts a query from jtds to 1334 UDP.

      I think you should really start with secifying a port, if this works you can make sure that there is only one port configured on all the interfaces and finally if that does not work, you need to tcp-dump the UDP packages, they should be readable.

       
      • Pretheve
        Pretheve
        2014-06-16

        Thanks for your prompt reply. It helped us..

         
  • Pretheve
    Pretheve
    2014-06-11

    please help me