none
When Windows Firewall is ON, SQL Server does not get connected thru TCP

    Question

  • I am using SQL Server 2005 Express + SP1 on a Windows Small Business Server(SBS) box.  The SBS is connected to a client thru LAN.

    Following are what I gave as IP address and DNS on the server:

    IP: 192.168.16.2, subnet mask : 255.255.255.0, Preferred DNS server: 192.168.16.2, Default gateway and Alternate DNS Server blank

    On the client, I have,

    IP: 192.168.16.4, subnet mask : 255.255.255.0, Preferred DNS server, Default gateway and Alternate DNS Server blank

    I can ping and connect to either of the machines.

    If I do a sqlcmd -S "tcp:servername\INSTANCE,port", I get the following error message:

    HResult 0x80090304, Level 16, State 1

    SQL Network Interfaces: The Local Security Authority cannot be contacted

     

    Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context

    If I do a sqlcmd -S "tcp:192.168.16.2\INSTANCE,port", it connects to SQL Server.

    I have the Windows Firewall ON.  If I Off the firewall, I do not have any problem at all.

    I included File and Printer Sharing, sqlsvr.exe in the Exception list of the Windows Firewall.

    Any help to solve the issue is appreciated.

    Thanks.

     
    Thursday, July 27, 2006 5:23 PM

Answers

  • you then have problems in name resolution (it can be a Netbios or a DNS name), and you can eventually solve it with a static entry in your WINS and/or DNS about the machine you have problem to "resolve"...
    let your network admin check the DNS server and that each client has been correctly set (both with static params or through a DHCP) to be checked and verified against the correct DNS..

    regards

    Monday, July 31, 2006 9:19 PM

All replies

  • hi,

    my question is not related with firewall and security....

    >If I do a sqlcmd -S "tcp:192.168.16.2\INSTANCE,port", it connects to SQL Server.

    do you have the SQLBrowser running?

    named instances do not default on TCP/IP 1433 port assigned by Iana to Microsoft for SQL Server, or default port can be even manually modified for default instances..

    the standard behavior of named instances is to use dynamically assigned ports... at very first instance start up, a free port will be assigned and saved to the Windows registry... at next start up, SQL Server tries to use the very same port, but it can be "redirected" to another one if the old port is busy..

    from the client  part, loosely speaking, this dynamic behavior is managed by SQLBrower, a service listening on UDP1434 (you are required to define an exception for this service as well).. say our instance is listening on Doom's server port, 666 ...

    the remote client opens an outgoing connection over a random tcp port (usally over 1024) to the "server".. again, the server is not listening on tcp 1433, but is litening from every client calls port on this dynamic port , thus a direct connection would just timeout.... the SQLBrowser, listening on udp 1434 intecepts this incoming call, resolves the port number negotiating with the specified instance and redirect the remote client to the dinamic port as defined by its network endpoint.., thus the  particular "tree way tcp handshaking"  is resolved...  the Accept is answered via a vehiculation on the dynamic port...

    later comunications are no longer vehiculated via SQLBrowser support but directly resolved at MDAC stack level with the required port...

    MDAC 2.6 or later is required in order to support redirection at network protocol level, which is not a problem anymore as SQLExpress requires MDAC 2.8 level...

    on the other hand, you can specify a fixed "static port", using the SQL Server Configuration Manager, and in this case, the SQLBrowser service is no longer required, but you are required to specify the port at connection string level ("tcp:192.168.16.2\INSTANCE,port")..

    regards

    Thursday, July 27, 2006 8:43 PM
  • Thank you Andrea. 

    I have SQL Browser running.  From your information, I added a new port for udp1434 in the exception list.  I also added sqlbrowser.exe

    in the exception list.  Even then, tcp:servername\INSTANCE,port does not work, whereas tcp:192.168.16.2\INSTANCE,port works.  Is there anything else that I can try?

    Thursday, July 27, 2006 9:22 PM
  • hi,

    do you have troubles resolving the netbios computer name "servername" or can you access it both via "servername" and 192.168.16.2 ?

    regards

    Thursday, July 27, 2006 10:13 PM
  • I have only problem with the servername, but not the IP address.  The IP address works fine.
    Monday, July 31, 2006 7:27 PM
  • you then have problems in name resolution (it can be a Netbios or a DNS name), and you can eventually solve it with a static entry in your WINS and/or DNS about the machine you have problem to "resolve"...
    let your network admin check the DNS server and that each client has been correctly set (both with static params or through a DHCP) to be checked and verified against the correct DNS..

    regards

    Monday, July 31, 2006 9:19 PM
  •  

    Another option is to add an entry into the hosts filles on your machine.

    Monday, July 31, 2006 10:42 PM