Re: IIS, SQL 2000 & XP's Firewall

Only one instance of SQL Server can use TCP port 1434. If you start SQL
Server 2000 and SQL Server 2005 and SQL Server 2008, you will need 3
separate TCP ports open through the firewall. And your two named instances
are probably using dynamic ports, so they don't pick a port number until
they start. You may find it easier to use SQL Server Configuration Manager
and set the SQL Servers to fixed ports, and then open those numbers. See
http://msdn.microsoft.com/en-us/library/ms345327(SQL.90).aspx
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Barry Flynn" <Anonymouse> wrote in message
news:%23yD4Qkg7JHA.5828@xxxxxxxxxxxxxxxxxxxxxxx
Thanks very much Rick.
Openning those two ports has worked.

A final question.
Will anything change when I install SQL 2008 on the laptop?
When that happens:-
- 2000 is the "default install"
- 2005 is a named instance
- 2008 will be a named instance

Thanks

Barry Flynn

"Rick Byham, MSFT" <rickbyh@xxxxxxxxxxxxx> wrote in message
news:eONdCdc7JHA.728@xxxxxxxxxxxxxxxxxxxxxxx
Yes, 14343 is a typo.
Queries to the Data Engine must go to the port that SQL Server is
listening on. SQL Server listens on TCP port 1433 by default, but only
one instance can listen on any port. The SQL Server error log will
include the port number so you can check it. But the default instance
(your SQL Server 2000) is probably the one using TCP 1433.
The SQL Server 2005 Browser service listens on UDP port 1434 where is
uses datagrams (the D in UDP) to tell the client the port number. SQL
Server 2000 also used UDP port 1434 for the same purpose. But SQL Server
2005 knows how to provide SQL Server 2000 info, so as long as the Browser
is working, all's well.

Yes, you will have to open those as exceptions in the firewall. If you
don't see exceptions for them, they probably aren't open and you should
create the exception.
More info: How to: Configure a Windows Firewall for Database Engine
Access http://msdn.microsoft.com/en-us/library/ms175043.aspx
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Barry Flynn" <Anonymouse> wrote in message
news:uqBBlA76JHA.4116@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Rick

SQL 2000 is SP3A.
It is the default instance.
The SQL 2005 Browser service is running (and apps in other virtual pc's
can connect with the SQL 2005 named instance.)

You asked if UDP port 1434 is open.
My notes (on how to connect to SQL 2005) say to open port tcp 1433 and
udt 1434.
So I thought I would have done that. But I may have overlooked it.
Looking around the firewall screens, I can't see anything that
specifically tells me that those ports are open (I should add I'm not
entirely sure I know what I'm doing in those firewall screens.)

So it looks like I should specifically add those two ports as exceptions
in the firewall?

You referred to "port 14343".
Was that port number a typo?

Thanks Rick

Barry Flynn


"Rick Byham, MSFT" <rickbyh@xxxxxxxxxxxxx> wrote in message
news:OkyhLk36JHA.3592@xxxxxxxxxxxxxxxxxxxxxxx
Some ideas:
When you install SQL Server 2005, the SQL Server 2005 Browser service
takes over name resolution, seizing UDP port 1434. Your SQL Server 2000
has to be at least SP3 to understand that. So what's the SP level on SS
2000?

Is the SQL Server 2005 Browser Service started? And is UDP port 1434
open on the firewall?
And is the SQL Server 2000 the default instance or a named instance?.
I suspect the programs that work understand that the SQL Server 2000 is
the default instance and just connect to TCP port 14343. And the
SQLOLEDB that doesn't work is trying to query UDP port 1434 to find out
what port the SQL Server is listening on.
So make sure that the TCP port that the SQL Server 2000 is using is
open on the firewall (possibly 1433), and also open UDP port 1434.
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Barry Flynn" <Anonymouse> wrote in message
news:OkQGKNy6JHA.5008@xxxxxxxxxxxxxxxxxxxxxxx
Hi

Apologies if this is in the wrong forum.

I have SQL Server 2000 installed on my laptop.
SQL Server 2005 is also installed as a named instance, which I don't
think is relevant to my problem.
The laptop runs XP SP3.

I am running some apps from a Virtual PC which runs on the laptop.
The VPC runs XP SP2.
Both the laptop and the virtual PC are running XP's Firewall.

An asp page running on the VPC is unable to connect to SQL 2000,
unless the laptop's Firewall is turned off.
But. a VB6 screen using the same connection string can connect
regardless of whether the laptop firewall is on or off.
And another app (Microsoft Dynamics SL) can also connect to SQL 2000
regardless of whether the laptop's firewall is on or off.

I'm really curious why an asp page cannot connect to SQL 2000 while
the laptop firewall is on, but other apps don't have that problem.

pFirewall.log shows this
#Fields: date time action protocol src-ip dst-ip src-port dst-port
size tcpflags tcpsyn tcpack tcpwin icmptype icmpcode info path

2009-06-11 14:41:06 DROP TCP 192.168.15.97 192.168.15.65 1077 1433 48
S 15607536 0 65535 - - - RECEIVE

Global.asa contains this

Application("Provider") = "SQLOLEDB"
Application("DBName") = "TheDBName"
Application("ServerName") = "TheLaptop"
Application("SQLLoginID") = "TheUserId"
Application("Password") = "ThePassword"
Application("connStr") = "Provider=" & Application("Provider") & _
";Initial Catalog=" & Application("DBName") & _
";Data Source=" & Application("ServerName") & _
";User ID=" & Application("SQLLoginID") & _
";Password=" & Application("Password")

Thanks

Barry Flynn













.

Relevant Pages

  • Re: Connecting to an instance in a cluster
    ... "Geoff N. Hiten" wrote: ... you cannot reuse port numbers. ... Microsoft SQL Server MVP ... That is the port you have to open on the firewall. ...
    (microsoft.public.sqlserver.clustering)
  • Re: IIS, SQL 2000 & XPs Firewall
    ... You may find it easier to use SQL Server Configuration Manager ... 2008 will be a named instance ... Queries to the Data Engine must go to the port that SQL Server is ... you will have to open those as exceptions in the firewall. ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server 2008 remote connection problem
    ... Hitchhiker’s Guide to Visual Studio and SQL Server ... default instance of the SQL Server Database Engine listens on TCP port 1433", ... > I was trying to establish a remote connection to SQL Server 2008 from ... The server is running the XP firewall. ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL server connection problem
    ... no firewall is installed? ... Also i have check that port 1433 is not ... Will SQL server only connect to this ... > to configure the firewall to permit connections to SQL Server. ...
    (comp.databases.ms-sqlserver)
  • Port Assignments : Multiple Instances : SSPI context issue
    ... A SQL server has the initial instance installed as a named instance ... Internet app is using 1433 to talk to the named instance. ... the install utility for the app does not work against ... By default it too is assigned port 1433!! ...
    (microsoft.public.sqlserver.server)