Connecting to SQL on a non default port

Hi Group,
As part of our efforts at best practices, we recently changed the default
port of 1433 for the first instance of SQL on our SQL server another port say
8888. There is a firewall between the SQL server and the clients. The
firewall rulebase was changed to allow traffic on port 8888 between the SQL
server and the clients.
However following the change the clients could not connect to the SQL
instance. I am not a database administrator, but my understanding of SQL is
that the clients would dynamically determine the changed port number using
the SQL browser (reporting) service and attempt a connection on the new port
8888.
Any ideas on what went wrong or is my understanding of how SQL connects
fundamentally wrong......any feedback is much apreciated.
Thanks
CMSQL Server uses SQL Server Resolution Protocol (SSRP) to resolve instance
name. In SQL Server 2000 SSRP is operated by SQL Server service (and I think
that if there is no instance working on port 1433 there is no automatic name
resolution). In SQL Server 2005 there is another service called SQL Server
Browser dedicated to respond to SSRP requests. It listens on UDP1434 (so you
must open this port on your firewall).
What you can try is to connect to SQL Server not by instance name but by
machine name and TCP port number: tcp:MACHINE_NAME,port. Example:
tcp:LONDON,8888
--
Regards
Pawel Potasinski
U¿ytkownik "CP" <CP@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:5A85887A-FE89-4245-837C-E01FDEF43BAB@.microsoft.com...
> Hi Group,
> As part of our efforts at best practices, we recently changed the default
> port of 1433 for the first instance of SQL on our SQL server another port
> say
> 8888. There is a firewall between the SQL server and the clients. The
> firewall rulebase was changed to allow traffic on port 8888 between the
> SQL
> server and the clients.
> However following the change the clients could not connect to the SQL
> instance. I am not a database administrator, but my understanding of SQL
> is
> that the clients would dynamically determine the changed port number using
> the SQL browser (reporting) service and attempt a connection on the new
> port
> 8888.
> Any ideas on what went wrong or is my understanding of how SQL connects
> fundamentally wrong......any feedback is much apreciated.
> Thanks
> CM
>|||Thanks for your response, Pawel.
We have 1434 opened on the firewall. Prior to this change we had (and still
do have) other instances of SQL on the same server. My understanding is that
the SQL Server Browser, was determinining the port numbers of these other
instances. So when the port no of default instance was changed, the
reporting service would determine the new port no as well.
This was tested in QA successfully albeit without a firewall in between. Do
you still think it would be worthwhile to connect using a machine name? We
are back to QA now, without a firewall in between.
Thanks again.
CM
"Pawel Potasinski" wrote:
> SQL Server uses SQL Server Resolution Protocol (SSRP) to resolve instance
> name. In SQL Server 2000 SSRP is operated by SQL Server service (and I think
> that if there is no instance working on port 1433 there is no automatic name
> resolution). In SQL Server 2005 there is another service called SQL Server
> Browser dedicated to respond to SSRP requests. It listens on UDP1434 (so you
> must open this port on your firewall).
> What you can try is to connect to SQL Server not by instance name but by
> machine name and TCP port number: tcp:MACHINE_NAME,port. Example:
> tcp:LONDON,8888
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "CP" <CP@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:5A85887A-FE89-4245-837C-E01FDEF43BAB@.microsoft.com...
> > Hi Group,
> >
> > As part of our efforts at best practices, we recently changed the default
> > port of 1433 for the first instance of SQL on our SQL server another port
> > say
> > 8888. There is a firewall between the SQL server and the clients. The
> > firewall rulebase was changed to allow traffic on port 8888 between the
> > SQL
> > server and the clients.
> >
> > However following the change the clients could not connect to the SQL
> > instance. I am not a database administrator, but my understanding of SQL
> > is
> > that the clients would dynamically determine the changed port number using
> > the SQL browser (reporting) service and attempt a connection on the new
> > port
> > 8888.
> >
> > Any ideas on what went wrong or is my understanding of how SQL connects
> > fundamentally wrong......any feedback is much apreciated.
> >
> > Thanks
> >
> > CM
> >
> >
>
>