none
Connecting to SQL Server Default Instance on a Non Standard Port Using WCF-SQL Adapter RRS feed

  • Question

  • Hi,

    I am trying to connect to a SQL server database using WCF-SQL adapter. The IT team has decided to not open the 1433 port to SQL server. I have been able to connect to the database using the standard SQL adapter, but connecting via the WCF-SQL adapter has been unsuccessfull. Any help on this would be much appreciated.

    Basically, i wanted to know if there is a way to specify the port number in the WCF-SQl adapter connection string. If not, is there any other way to make this work?

     

    Thanks

    Reddy

    Monday, May 10, 2010 2:46 PM

Answers

  • With the WCF SQL adapter it is not possible to specify a custom port number in the URI. You should either have the default instance running in the standard port (1433) or it should be a named instance, if you want to use a non-standard port.

    • Marked as answer by ReddyK Monday, May 10, 2010 7:37 PM
    • Unmarked as answer by ReddyK Monday, May 10, 2010 7:56 PM
    • Marked as answer by ReddyK Monday, May 10, 2010 8:45 PM
    Monday, May 10, 2010 7:25 PM

All replies

  • You can specify a different port in the add generated item wizard for the WCF-SQL adapter, give it after the server using this syntax:

    serverAddress:Port

    So for example, 10.1.1.101:1433 for a local server.

    This syntax may also be used in deployment for the service URI.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, May 10, 2010 5:26 PM
    Moderator
  • Hi Ben,

    Thanks for your reply. What I meant was that in the Receive port configuration where can i put the port?

    If I use the following syntax like you suggest, I get back a connection exception:

    mssql://myserver:1512//mydatabasename?InboundId=GetSomeData

     

    thanks

    Monday, May 10, 2010 7:21 PM
  • With the WCF SQL adapter it is not possible to specify a custom port number in the URI. You should either have the default instance running in the standard port (1433) or it should be a named instance, if you want to use a non-standard port.

    • Marked as answer by ReddyK Monday, May 10, 2010 7:37 PM
    • Unmarked as answer by ReddyK Monday, May 10, 2010 7:56 PM
    • Marked as answer by ReddyK Monday, May 10, 2010 8:45 PM
    Monday, May 10, 2010 7:25 PM
  • Thanks for the info.

    It seems like this is a bug or limitation. If I can specify a port number in a valid SQL server connection string, why cant I specify it in a WCF connection string?

    Is this something that will be fixed in the future?

     

    thanks

    Reddy

    Monday, May 10, 2010 7:37 PM
  • Thanks for the info.

    I am still confused. How can it figure which port to go against?

    Monday, May 10, 2010 8:07 PM
  • I changed my default port in SQL Management tools by right-clicking on TCP/IP and going to properties, then specifying 1434 rather than the default 1433. I then restarted SQL. Then in the add generated items wizard I specified 127.0.0.1:1434, and it connects fine.

    On a receive location it is the same format. The WCF-Custom property pages includes this format guide as a label on the form: http://host[:port]/path/service.svc

    It is possible the underlying connectivity used by the adapter still uses 1433 even if you configure it to use a non standard port. I know on another thread someone tried to use a non standard named pipe for connecting to SQL and it uses the default one only as well. This may be what Jeevitha is speaking to.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, May 10, 2010 9:08 PM
    Moderator
  • Hi Ben,

    I tested the generated items wizard locally and it works as you suggest, but i am suspect of this test. I cant really connect to the server since the firewall rules do not allow my machine to connect to the sql server machine, so I can only try out the port configuration on the test server.

    The  WCF-Custom poperty pages does give the format,  http://host[:port]/path/service.svc, but i think the SQL Adapter is doing something different under the hood.

    Would have helped if I had the source for it... but that is another matter :)

    Thanks for your help though!

    Reddy

    • Proposed as answer by Soujanya Kumar Thursday, August 12, 2010 2:07 PM
    Monday, May 10, 2010 10:16 PM
  • Try setting up the registry

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
    "Server\\instance"="DBMSSOCN,X.X.X.X,1435"


    SKS
    Thursday, August 12, 2010 2:07 PM