MSDE and Sql Server 2005

[A Lake]

I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was installed by
Backup Exec.

On this server is another application that installated an MSDE database
(showing as version 8).

I can see both databases OK on the server using Management Studio Express,
and I can see the Server 2005 database from other PCs. However I cannot see
the MSDE database from anywhere else.

I've checked the configuration and using the surface configuration tool (but
this doesn't show the MSDE database) I've checked the configuration
svrnetcn.exe tool and the port is correctly set to 1433. The database is set
to allow remote connections over named pipes and tcpip.

So I'm stumped - nothing can connect to this database unless its on the
server itself. I have noticed that runing netstat shows that port 1433 isn't
open?

[Andrea Montanari]

hi,

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was
installed by Backup Exec.
On this server is another application that installated an MSDE
database (showing as version 8).
I can see both databases OK on the server using Management Studio
Express, and I can see the Server 2005 database from other PCs.
However I cannot see the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration
tool (but this doesn't show the MSDE database) I've checked the
configuration svrnetcn.exe tool and the port is correctly set to
1433. The database is set to allow remote connections over named
pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on
the server itself. I have noticed that runing netstat shows that port
1433 isn't open?

as long as, via svrnetcn.exe, the MSDE instance has been set to allow
connections over the (specified) TCP/IP port (and the instance has been
restarted after the eventual change in the settings), no further
requirements are involved at the SQL Server level.. you can even check the
instance Log for ntelib usage, which are reported similarly to
2008-07-10 12:33:41.31 spid3 Server name is 'servername'.
2008-07-10 12:33:41.31 server Using 'SSNETLIB.DLL' version '8.0.2039'.
....
2008-07-10 12:33:41.42 server SQL server listening on xxx.xxx.xxx.xxx:
1433.
2008-07-10 12:33:41.42 server SQL server listening on 127.0.0.1: 1433.
...
2008-07-10 12:33:42.00 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2008-07-10 12:33:42.00 server SQL Server is ready for client connections.

here TCP/IP 1433 port is indicated as used and that the instance allows
connections over TCP, NP and shared memory..

so the second stage is verifying no firewall blocks are active on the used
port..

[A Lake]

Hi,

The logs do show that the MSDE instance is opening on the port specified,
with no errors (I've tried 1433 and 1217)

On the clients, I've allowed these ports through the firewall but still no
luck! There is no firewall running on the server.

I'm going to try SP4 for MSDE 2000 to see if that helps.

Post by Andrea Montanari
hi,

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was
installed by Backup Exec.
On this server is another application that installated an MSDE
database (showing as version 8).
I can see both databases OK on the server using Management Studio
Express, and I can see the Server 2005 database from other PCs.
However I cannot see the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration
tool (but this doesn't show the MSDE database) I've checked the
configuration svrnetcn.exe tool and the port is correctly set to
1433. The database is set to allow remote connections over named
pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on
the server itself. I have noticed that runing netstat shows that port
1433 isn't open?

as long as, via svrnetcn.exe, the MSDE instance has been set to allow
connections over the (specified) TCP/IP port (and the instance has been
restarted after the eventual change in the settings), no further
requirements are involved at the SQL Server level.. you can even check the
instance Log for ntelib usage, which are reported similarly to
2008-07-10 12:33:41.31 spid3 Server name is 'servername'.
2008-07-10 12:33:41.31 server Using 'SSNETLIB.DLL' version '8.0.2039'.
.....
1433.
2008-07-10 12:33:41.42 server SQL server listening on 127.0.0.1: 1433.
....
2008-07-10 12:33:42.00 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2008-07-10 12:33:42.00 server SQL Server is ready for client connections.
here TCP/IP 1433 port is indicated as used and that the instance allows
connections over TCP, NP and shared memory..
so the second stage is verifying no firewall blocks are active on the used
port..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply

[A Lake]

Well, I've applied SP4, checked the port is open and clients still cannot
connect even with their firewalls disabled.

Looks like this server will not act as a server!

Post by A Lake
Hi,
The logs do show that the MSDE instance is opening on the port specified,
with no errors (I've tried 1433 and 1217)
On the clients, I've allowed these ports through the firewall but still no
luck! There is no firewall running on the server.
I'm going to try SP4 for MSDE 2000 to see if that helps.

Post by Andrea Montanari
hi,

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was
installed by Backup Exec.
On this server is another application that installated an MSDE
database (showing as version 8).
I can see both databases OK on the server using Management Studio
Express, and I can see the Server 2005 database from other PCs.
However I cannot see the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration
tool (but this doesn't show the MSDE database) I've checked the
configuration svrnetcn.exe tool and the port is correctly set to
1433. The database is set to allow remote connections over named
pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on
the server itself. I have noticed that runing netstat shows that port
1433 isn't open?

as long as, via svrnetcn.exe, the MSDE instance has been set to allow
connections over the (specified) TCP/IP port (and the instance has been
restarted after the eventual change in the settings), no further
requirements are involved at the SQL Server level.. you can even check the
instance Log for ntelib usage, which are reported similarly to
2008-07-10 12:33:41.31 spid3 Server name is 'servername'.
2008-07-10 12:33:41.31 server Using 'SSNETLIB.DLL' version '8.0.2039'.
.....
1433.
2008-07-10 12:33:41.42 server SQL server listening on 127.0.0.1: 1433.
....
2008-07-10 12:33:42.00 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2008-07-10 12:33:42.00 server SQL Server is ready for client connections.
here TCP/IP 1433 port is indicated as used and that the instance allows
connections over TCP, NP and shared memory..
so the second stage is verifying no firewall blocks are active on the used
port..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply

[Chris.Cheney]

Post by A Lake
Well, I've applied SP4, checked the port is open and clients still
cannot connect even with their firewalls disabled.
Looks like this server will not act as a server!

Is there _still_ nothing listening (netstat -a) on the relevant port?

If MSDE is listening on that port, can you access MSDE locally via loopback
(127.0.0.1)?

[A Lake]

netstat -a shows listening on Port 1217 (changed ffrom default 1433) but
clients cannot connect.

I can connect locally using the management express tool.

The application that uses the MSDE works on the local server, but clients
fail to connect.

Post by Chris.Cheney

Post by A Lake
Well, I've applied SP4, checked the port is open and clients still
cannot connect even with their firewalls disabled.
Looks like this server will not act as a server!

Is there _still_ nothing listening (netstat -a) on the relevant port?
If MSDE is listening on that port, can you access MSDE locally via loopback
(127.0.0.1)?

[Chris.Cheney]

Post by A Lake
netstat -a shows listening on Port 1217 (changed ffrom default 1433)
but clients cannot connect.
I can connect locally using the management express tool.

Which I suspect is not using TCP/IP to connect.

a) Try to connect to 127.0.0.1:1217 (i.e. via TCP/IP) using one of the
ISQL/OSQL/SQLCMD utilities on the server.

b) If (a) works, try to connect the same way but use the assigned IP
address instead of the loopback address.

c) If (b) works, repeat (b) but from a different computer (but preferably
on the same subnet as the server).

d) If (c) works, repeat (b) but from one of the client computers.

Post by A Lake
The application that uses the MSDE works on the local server, but
clients fail to connect.

Post by Chris.Cheney

Post by A Lake
Well, I've applied SP4, checked the port is open and clients still
cannot connect even with their firewalls disabled.
Looks like this server will not act as a server!

Is there _still_ nothing listening (netstat -a) on the relevant port?
If MSDE is listening on that port, can you access MSDE locally via
loopback (127.0.0.1)?

[Andrea Montanari]

hi,

Post by A Lake
netstat -a shows listening on Port 1217 (changed ffrom default 1433)
but clients cannot connect.
I can connect locally using the management express tool.
The application that uses the MSDE works on the local server, but
clients fail to connect.

local connections are usually performed via shared memory and not net
protocols..
as you specified a static port, are the remote client aware of it (meaning,
you have to define an ALIAS in the cliconfg.exe protocols dialog for that
instance listening on port other than 1433 or include in the connection
string the used port like "...; Data Source=servername,1217; .....")..
or, you have to rely on dynamic port assignement and enable the SQL Server
Resolution Service (AKA the SQLBrowser in SQL Server 2005) to resolve the
port indirection at connection time.. the SQLBrowser will be listening on
UDP 1434 for incoming connection requests and will intercept them.. it will
resolve the port number querying the local registry for the current used
port and return that value to the incoming connections redirecting them to
the port the instance is listening on..
obviously, a firewall exception must be defined at the port level (which can
change at any instance bootstrap but usually doesn't) or, if the firewall
allows it, at the program (binary exe) level..

[A Lake]

Done some testing:

I can connect locally with osql, but only if I specify the instance name i.e
SERVER\DATABASE. if I specify the IP and port I get the same error. (I tried
using both 1433 and other ports as well)

osql cannot connect from any clients.

When I use port 1433 netstat -a doesn't show this port as listening, but
when I change to another port (i.e 1217) that does show as listening but
still with no connectivity.

Is the SQL Server Express 2005 blocking the MSDE SQL Server somehow? I
didn't have this problem on the previous server which only ran MSDE databases?

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was installed by
Backup Exec.
On this server is another application that installated an MSDE database
(showing as version 8).
I can see both databases OK on the server using Management Studio Express,
and I can see the Server 2005 database from other PCs. However I cannot see
the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration tool (but
this doesn't show the MSDE database) I've checked the configuration
svrnetcn.exe tool and the port is correctly set to 1433. The database is set
to allow remote connections over named pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on the
server itself. I have noticed that runing netstat shows that port 1433 isn't
open?

[Chris.Cheney]

Post by A Lake
I can connect locally with osql, but only if I specify the instance
name i.e SERVER\DATABASE. if I specify the IP and port I get the same
error. (I tried using both 1433 and other ports as well)
osql cannot connect from any clients.
When I use port 1433 netstat -a doesn't show this port as listening,
but when I change to another port (i.e 1217) that does show as
listening but still with no connectivity.
Is the SQL Server Express 2005 blocking the MSDE SQL Server somehow? I
didn't have this problem on the previous server which only ran MSDE databases?

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was
installed by Backup Exec.
On this server is another application that installated an MSDE
database (showing as version 8).
I can see both databases OK on the server using Management Studio
Express, and I can see the Server 2005 database from other PCs.
However I cannot see the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration
tool (but this doesn't show the MSDE database) I've checked the
configuration svrnetcn.exe tool and the port is correctly set to
1433. The database is set to allow remote connections over named
pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on
the server itself. I have noticed that runing netstat shows that port
1433 isn't open?

http://support.microsoft.com/kb/265808 is not directly relevant but you may
find the techniques suggested to be helpful.

[Richard Mueller [MVP]]

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was installed by
Backup Exec.
On this server is another application that installated an MSDE database
(showing as version 8).
I can see both databases OK on the server using Management Studio Express,
and I can see the Server 2005 database from other PCs. However I cannot see
the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration tool (but
this doesn't show the MSDE database) I've checked the configuration
svrnetcn.exe tool and the port is correctly set to 1433. The database is set
to allow remote connections over named pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on the
server itself. I have noticed that runing netstat shows that port 1433 isn't
open?

I think I have a customer with the same problem. If MSDE (named instance)
installed on a server with no other SQL Server, the customer can connect to
the database from either the server or a client workstation. If MSDE is
installed on a server with older version of SQL Server (before 2005), same
thing. However, if MSDE installed on W2k3 Server with SQL Server 2005,
customer can connect from the server, but not from a client (XP). It acts as
if SQL Server 2005 prevents MSDE from networking. SQL Server 2005 tools do
not see the MSDE instance.

[A Lake]

Thats exactly the problem I have. Earlier MSDE databases don't appear in the
2005 server management tools either.

I can't remove the 2005 SQL server as its part of Backup Exec, so I can only
try and install it on another server,or maybe create a virtual server.

Post by Richard Mueller [MVP]

Post by A Lake
I have a Windows 2003 server which has an instance of Sql Server 2005
(express I assume, showing as version 9) running on it that was installed by
Backup Exec.
On this server is another application that installated an MSDE database
(showing as version 8).
I can see both databases OK on the server using Management Studio Express,
and I can see the Server 2005 database from other PCs. However I cannot see
the MSDE database from anywhere else.
I've checked the configuration and using the surface configuration tool (but
this doesn't show the MSDE database) I've checked the configuration
svrnetcn.exe tool and the port is correctly set to 1433. The database is set
to allow remote connections over named pipes and tcpip.
So I'm stumped - nothing can connect to this database unless its on the
server itself. I have noticed that runing netstat shows that port 1433 isn't
open?

I think I have a customer with the same problem. If MSDE (named instance)
installed on a server with no other SQL Server, the customer can connect to
the database from either the server or a client workstation. If MSDE is
installed on a server with older version of SQL Server (before 2005), same
thing. However, if MSDE installed on W2k3 Server with SQL Server 2005,
customer can connect from the server, but not from a client (XP). It acts as
if SQL Server 2005 prevents MSDE from networking. SQL Server 2005 tools do
not see the MSDE instance.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--