Probs in connection

This is the first time we are working with named instances on sql 2k. Every time you create a new instance, do you need to manually go in & specify the port number as 1433 on the client side?
Our clients are not able to connect to this server. They get a server does not exist or access denied message. However if In the "Client Configuration" set up under "Connection Parameters", we CLEAR the "dynamically determine port" check box in order to set the port manually and type "1433" in the port number box, it seems to work & the clients can connect.
Is this an issue with instances or are we doing something wrong?

Thanks!

you are doing something wrong.

what is the default and named instance names?

Client should connect to default instance like SERVER1 and
to the named instance server1\instance1

They are connecting as server1\instance1.

Adding to the confusion, some people are able to connect without making this change while others are not!

I guess I know why. because they do not have the latest MDAC on their machines.

Install MDAC 2.7 on their machines or sql server 2000 client on their machines

They all have SQL 2000 client on their machines....!

oh & I dont know about MDAC - what is it for?

Thanks!

when you install sql client it updates the MDAC.

Did you try removing the server\instance name from the client network utility?

MDAC is Microsoft data access components, including ole db provider and odbc. By the way, named instance uses dynamic port number by default.

Quote:

---

Originally posted by MAK
when you install sql client it updates the MDAC.

Did you try removing the server\instance name from the client network utility?

---

U mean remove the alias created for that instance in client metwork utility? no i havent tried that.

Try and let us know

ok, i removed the alias & she could'nt connect again.

Look at sql server log to find out which port number is used for server1\instance1, then create proper alias on client pc.

Quote:

---

Originally posted by rmiao
MDAC is Microsoft data access components, including ole db provider and odbc. By the way, named instance uses dynamic port number by default.

---

Looks like for us we need to be changing that on every client. Uncheck the dynamic port box & hardcode the 1433 port in it!

Quote:

---

Originally posted by rmiao
Look at sql server log to find out which port number is used for server1\instance1, then create proper alias on client pc.

---

I checked again & the port number is 1433. But somehow the client is not able to pick it up dynamically – we have to hardcode it in the tcp/ip properties :o(

It'll not pick 1433 as dynamic port number since dynamic port number is any number other than 1433 nor 1434. If you have multiple instances on the server, only one of them can be listening on port 1433, usually default instance.

I have another sql 2000 server with a port 1433 & I added an alias with dynamic port number & I got register the server fine. So dynamic port does include 1433 also I guess… ??

Ya, you are right. Is the problem on all client machines or just some of them?

actually it is on client machines which are not in this building (where the server is located)!!
everyone in this building can connect...:confused:

What's network link between buildings? Blocked any port or traffic on the network?

I will check with my network people… & see if any port is blocked.

Is there any way to check what port the client is opening (when set as dynamically determine port) ? Then we can check to see if that particular port is blocked…
Again, since it is dynamically determined – cant the port number be any number over 1024 & it can keep changing too?

The port 1434 has to be open when client talk to sql server via dynamic port number.

yes, thats what i read in BOL too...
Default instances of SQL Server use TCP port 1433 by default. Named instances, however, dynamically assign an unused TCP port number the first time the instance is started. The named instance can also dynamically change it's TCP port address on a subsequent startup if the original TCP port number is being used by another application. SQL Server only dynamically changes to an unused TCP port if the port it is currently listening on was dynamically selected. That is, if the port was statically selected (manually), SQL Server will display an error and continue to listen on other ports. It is unlikely another application would attempt to use 1433 since that port is registered as a well-known address for SQL Server.

When using a named instance of SQL Server with a firewall, use the Server Network Utility to configure the named instance to listen on a specific TCP port. You must pick a TCP port that is not used by another application running on the same computer or cluster. For a list of well-known ports registered for use by various applications, see http://www.iana.org.

Have the network administrator configure the firewall to forward the IP address and TCP port the instance of SQL Server is listening on (using either 1433 for a default instance, or the TCP port you configured a named instance to listen on). Also configure the firewall to forward requests for UDP port 1434 on the same IP address. SQL Server 2000 uses UDP port 1434 to establish communications links from applications.

For example, consider a computer running one default instance and two named instances of SQL Server. The computer is configured such that the network addresses that the three instances listen on all have the same IP address. The default instance would listen on TCP port 1433, one named instance could be assigned TCP port 1434, and the other named instance TCP port 1954. You would then configure the firewall to forward network requests for UDP port 1434 and TCP ports 1433, 1434, and 1954 on that IP address.

No, don't use port 1434 since it's management port used by sql2k.

you mean not to have a named instance on port 1434 right?
but 1434 should be open on the server side right?

but then again i read this article
http://www.sqlmag.com/Articles/Index...ticleID=39447&

& i got all confused again.

Also,Microsoft posted this article in May:
http://www.microsoft.com/sql/techinf...n/port1434.asp
And this one in July:
http://www.microsoft.com/technet/tre...n/MS02-039.asp
so what do i interpret of this ? is still a risk to keep port 1434 open (after the patch available from MS)?

That's right, have to open port 1434 on your network if you use dynamic port for named instance.

okies... thanks a bunch for all your help ! :)

ok port 1434 is now open for those clients.. but they still cannot connect :o(

Then which port the instance is listening on?

Sorry for the late reply…
In the Server Network Utility, default port is set to 1433…should this be changed?

That fine, but ensure only one instance uses that number. Each instance on the server should use different port number.

Yes only 1 instance is using that port….

Does client connect to sql instance with same port? Is the port closed somewhere on your network?

If I create an alias on the client using port 1433, then they can connect fine. But if I leave it to dynamically determine ports it is not able to connect. I have asked the network people to monitor the traffic between the client & server to determine which ports are being hit on & whether any of them is blocked… thanks rmiao!

Have to open port 1434 if use dynamic port.

It's kind of a late post. If you have problems with connecting to a named instance of SQL Server, try to specify port in the connection string. E.g. server = "server1\instance1,1433"

Sergey

http://xrint.com