none
escaping port number in URI string RRS feed

  • Question

  • hi,

    I have a SQL 2000 database that am trying to access with the sqlbinding of WCF-Custom adapter. The service is running on a custom port and so am able to connect to the database from SSMS, by specifying the server name in format- SERVERIP,PORT. But if i specify the same in Consume Adapter Service plugin, it says, hostname could not be parsed. If i try to enter the URI directly in Configure a URI box (with , escaped, i,e: mssql://SERVERIP%2CPORT//DBNAME?
    it says, You have entered an invalid URI.

    the doc says that i can use escaping in URI for special chars though.
    http://msdn.microsoft.com/en-us/library/dd788089(BTS.10).aspx

    Any help?
    Ali

    Monday, February 22, 2010 1:38 PM

Answers

  • When a SQL client connects to a named instance, it queries the SQL management service to get the port number and it connects to that particular port. (Resource link)

    Unfortunately, the wcf-sql adapter doesn't support port numbers in the connection Uri and even if you specify any port, it is ignored and it tries to connect to the default port.

    Thanks
    Jeevitha
    Friday, February 26, 2010 6:15 PM
  • guys,

    just to give you an update- the connectivity problem is solved :)

    we just checked with our 'remote DB guys', if SQL was on a named instance.. and apparently it was. Since with named instance, we dont need a port number really, i got connected.

    Still a problem for those, who will try to connect on default instance running on non-default portnumber though!

    thanks all
    Ali
    Friday, February 26, 2010 4:15 PM

All replies

  • Did you try mssql://SERVERIP:PORT//DBNAME? It should work

    Thanks
    Jeevitha
    Monday, February 22, 2010 2:12 PM
  • Thanks Jeevitha, but it did not work. Returned with the error:

    Connecting to the LOB system has failed.
    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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).
    Monday, February 22, 2010 2:34 PM
  • Hi,

    Maybe it is not in the URI. You could do the following if it applies to your situation:

    1. check the SQL Server Errorlog to make sure SQL Server is now listening on TCP/IP and confirm which port it is listening on.
    2. check if firewall is not blocking the port you are using.
    3. go to your other machine and run the client network configuration tool (cliconfg.exe)  Make sure TCP/IP is enabled, click properties and make sure the port number is the same one as SQL Server is listening on.

    Regardsm

    Steef-Jan Wiggers
    (http://soa-thoughts.blogspot.com/)


    BizTalk Server
    Monday, February 22, 2010 4:43 PM
    Moderator
  • I was able to connect using a uri like this:

    mssql://1.1.10.201:1433/myinstance/mydb?

    The default port for SQL is 1433. If I tried to escape the colon with %3A or what you specified above I get an error that the it cannot parse the host name.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, February 22, 2010 10:25 PM
    Moderator
  • thnks guys for the tips..

    for the port number, i tried few things with my local machine/ db, if i give any number after :, it connects anyway- guess it uses named pipes, shared memory etc. for which it just ignores the port number.

    Since my original error was pointing to named pipes (i suspect this is the protocol it uses to connect to the Remote server (which is behind some firewalls etc. and i dont have access to its SQL logs btw). so, i did some tests with named pipe as the only protocol for my local SQL Server and Client connectivity protcols (used SQL Server Config Manager) to set things up. It seems like the URI mssql://1.1.10.201//mydb? works if the server is listening on default pipe (sql\query), but if its set to query1 for exmaple, it gives out the same error as above. I'll check with the Support if the remote SQL is listening on a non-default pipe. Meanwhile do you know if/how we can specify pipename in the URI? mssql:// ????

    thanks
    Ali
    Wednesday, February 24, 2010 3:18 PM
  • No, I do not think you can connect over named pipes for 2 reasons.

    First, I tried it based on this article (http://msdn.microsoft.com/en-us/library/ms189307.aspx), and I get an invalid URI error.

    Second, I think the way the WCF adapters in the adapter pack work is based on TCP - you would need to have a different WCF binding to have it work over named pipes and there is not that concept with the WCF adapters. 

    To do what you are asking for you would need to build a custom WCF binding, but I am not sure if the add generated schema wizard can handle named pipes either.

    The next best approach would probably be to use a .NET reference to call over the custom named pipe.

    Thanks, 
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, February 24, 2010 7:11 PM
    Moderator
  • hello,

    I ran the Network Monitor locally to sniff the traffic and see whats going on.

    It does attempt to connect it via TCP, but the destination port is always 1433!

    Interestingly, if my remote database is on a named instance, then it connects to it even if dont specify the portnumber in the URI. Destination port (not 1433) appears just fine in the netowork monitor logs then

    Some insight from MS is needed here..

    cheers

    Thursday, February 25, 2010 3:02 PM
  • guys,

    just to give you an update- the connectivity problem is solved :)

    we just checked with our 'remote DB guys', if SQL was on a named instance.. and apparently it was. Since with named instance, we dont need a port number really, i got connected.

    Still a problem for those, who will try to connect on default instance running on non-default portnumber though!

    thanks all
    Ali
    Friday, February 26, 2010 4:15 PM
  • Very interesting! So a named instance always resolves somehow.

    Your issues have opened up a new area to think about compatibility of the WCF-SQL adapter. There is a definite gap regarding how to connect to SQL over named pipes because the generate schemas wizard does not support this addressing as far as I can tell.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, February 26, 2010 5:26 PM
    Moderator
  • When a SQL client connects to a named instance, it queries the SQL management service to get the port number and it connects to that particular port. (Resource link)

    Unfortunately, the wcf-sql adapter doesn't support port numbers in the connection Uri and even if you specify any port, it is ignored and it tries to connect to the default port.

    Thanks
    Jeevitha
    Friday, February 26, 2010 6:15 PM
  • Hey, I heard from a colleague that the following URI syntax can do alternate ports for WCF-SQL:

    mssql://<server name>/<instance name>,<port>/DatabaseName?

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, June 25, 2010 10:03 PM
    Moderator
  • Hi,

    Maybe it is not in the URI. You could do the following if it applies to your situation:

    1. check the SQL Server Errorlog to make sure SQL Server is now listening on TCP/IP and confirm which port it is listening on.
    2. check if firewall is not blocking the port you are using.
    3. go to your other machine and run the client network configuration tool (cliconfg.exe)  Make sure TCP/IP is enabled, click properties and make sure the port number is the same one as SQL Server is listening on.

    Regardsm

    Steef-Jan Wiggers
    (http://soa-thoughts.blogspot.com/)


    BizTalk Server

    Here is also the same case, I followed it but it does not work, why? Could you give more detailed description?
    Sunday, August 22, 2010 2:45 AM