I am getting this error in Enterprise Manager:
TCP Provider: An existing connection was forcibly closed by the remote host.
But, my web app can still connect just fine to the db, so it's still inthe same place with the same login/password. And my registrationin EM was working just fine (for years) until yesterday I got thiserror. I've tried deleting the registration and re-creating itbut the same thing. What could have gone wrong to cause this tohappen?
Thanks for any pointers, I have no idea where to start looking on this one, the host says they haven't changed anything...
Well, in case this bites anybody else on the butt, here's what it was:My login didn't have permission for my DEFAULT database on theserver. Apparently EM checks my default db before checking the dbI'm actually trying to connect to! So making my db also mydefault db fixed it.
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Wednesday, March 7, 2012
EM connects to server without knowing port
I'm confused. When I create an ODBC connection to a remote SQL server 2000
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David WalkerHi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
>|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David WalkerHi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
>|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
EM connects to server without knowing port
I'm confused. When I create an ODBC connection to a remote SQL server 2000
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David Walker
Hi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker
|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>
|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker
|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David
|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David Walker
Hi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker
|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>
|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker
|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David
|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
Sunday, February 26, 2012
Eliminating the expensive remote join
I've got a table (dexIndex) with 9 million rows in it. It's got an
identity column (index_id) and a column that's full-text indexed
(insureds_name) and a column that I want to filter on (agency_number).
When I query like this:
select * from decIndex where agency_number = '0349323' and
contains(insureds_name,'"SMITH"')
The execution plan involves a remote scan (98%) and a clustered index
seek (0%) which lead into a hash match/inner join (2%). The problem is
that the remote scan is returning 51,000 rows even though only 1 comes
out of the hash match/inner join. Thus, the query takes over a minute
on a built-to-the-hilt server.
Is there a way to have the FT engine only look through or return a
subset of the FT index, like only those rows which correspond to
agency_number = '0349323'?
I'd like to avoid adding agency_number to the FT index.
Thanks!
Regretably there is no way of doing this. One of the strategies of getting
around this problem is to partition your table according to the other
elements of you where clause. Another option is to build full text indexes
on indexed views in SQL 2005. Again the view would be of a section of your
table which would match your where conditions.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:%233kbwJZJFHA.588@.TK2MSFTNGP15.phx.gbl...
> I've got a table (dexIndex) with 9 million rows in it. It's got an
> identity column (index_id) and a column that's full-text indexed
> (insureds_name) and a column that I want to filter on (agency_number).
> When I query like this:
> select * from decIndex where agency_number = '0349323' and
> contains(insureds_name,'"SMITH"')
> The execution plan involves a remote scan (98%) and a clustered index seek
> (0%) which lead into a hash match/inner join (2%). The problem is that
> the remote scan is returning 51,000 rows even though only 1 comes out of
> the hash match/inner join. Thus, the query takes over a minute on a
> built-to-the-hilt server.
> Is there a way to have the FT engine only look through or return a subset
> of the FT index, like only those rows which correspond to agency_number =
> '0349323'?
> I'd like to avoid adding agency_number to the FT index.
> Thanks!
|||Hilary Cotter wrote:
> Regretably there is no way of doing this. One of the strategies of getting
> around this problem is to partition your table according to the other
> elements of you where clause. Another option is to build full text indexes
> on indexed views in SQL 2005. Again the view would be of a section of your
> table which would match your where conditions.
I ended up clustering the index that corresponded to the unique column
for the full-text index and that sped it up a great deal.
Thanks,
Ryan
identity column (index_id) and a column that's full-text indexed
(insureds_name) and a column that I want to filter on (agency_number).
When I query like this:
select * from decIndex where agency_number = '0349323' and
contains(insureds_name,'"SMITH"')
The execution plan involves a remote scan (98%) and a clustered index
seek (0%) which lead into a hash match/inner join (2%). The problem is
that the remote scan is returning 51,000 rows even though only 1 comes
out of the hash match/inner join. Thus, the query takes over a minute
on a built-to-the-hilt server.
Is there a way to have the FT engine only look through or return a
subset of the FT index, like only those rows which correspond to
agency_number = '0349323'?
I'd like to avoid adding agency_number to the FT index.
Thanks!
Regretably there is no way of doing this. One of the strategies of getting
around this problem is to partition your table according to the other
elements of you where clause. Another option is to build full text indexes
on indexed views in SQL 2005. Again the view would be of a section of your
table which would match your where conditions.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:%233kbwJZJFHA.588@.TK2MSFTNGP15.phx.gbl...
> I've got a table (dexIndex) with 9 million rows in it. It's got an
> identity column (index_id) and a column that's full-text indexed
> (insureds_name) and a column that I want to filter on (agency_number).
> When I query like this:
> select * from decIndex where agency_number = '0349323' and
> contains(insureds_name,'"SMITH"')
> The execution plan involves a remote scan (98%) and a clustered index seek
> (0%) which lead into a hash match/inner join (2%). The problem is that
> the remote scan is returning 51,000 rows even though only 1 comes out of
> the hash match/inner join. Thus, the query takes over a minute on a
> built-to-the-hilt server.
> Is there a way to have the FT engine only look through or return a subset
> of the FT index, like only those rows which correspond to agency_number =
> '0349323'?
> I'd like to avoid adding agency_number to the FT index.
> Thanks!
|||Hilary Cotter wrote:
> Regretably there is no way of doing this. One of the strategies of getting
> around this problem is to partition your table according to the other
> elements of you where clause. Another option is to build full text indexes
> on indexed views in SQL 2005. Again the view would be of a section of your
> table which would match your where conditions.
I ended up clustering the index that corresponded to the unique column
for the full-text index and that sped it up a great deal.
Thanks,
Ryan
Subscribe to:
Posts (Atom)