MSDE and Sql Server 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

A Lake - 10 Jul 2008 08:52 GMT

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?

Reply to this Message

Andrea Montanari - 10 Jul 2008 11:39 GMT

hi,

> 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
[quoted text clipped - 16 lines]
> 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..

Signature

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

Reply to this Message

A Lake - 10 Jul 2008 12:07 GMT

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.

> hi,
> > I have a Windows 2003 server which has an instance of Sql Server 2005
[quoted text clipped - 39 lines]
> so the second stage is verifying no firewall blocks are active on the used
> port..

Reply to this Message

A Lake - 11 Jul 2008 11:16 GMT

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!

> Hi,
>
[quoted text clipped - 49 lines]
> > so the second stage is verifying no firewall blocks are active on the used
> > port..

Reply to this Message

Chris.Cheney - 11 Jul 2008 14:05 GMT

=?Utf-8?B?QSBMYWtl?= <ALake@discussions.microsoft.com> wrote in
news:93A44FA5-7F64-45B1-91DA-938702D5F04D@microsoft.com:

> 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)?

Reply to this Message

A Lake - 11 Jul 2008 18:56 GMT

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.

> =?Utf-8?B?QSBMYWtl?= <ALake@discussions.microsoft.com> wrote in
> news:93A44FA5-7F64-45B1-91DA-938702D5F04D@microsoft.com:
[quoted text clipped - 8 lines]
> If MSDE is listening on that port, can you access MSDE locally via loopback
> (127.0.0.1)?

Reply to this Message

Chris.Cheney - 12 Jul 2008 11:51 GMT

=?Utf-8?B?QSBMYWtl?= <ALake@discussions.microsoft.com> wrote in
news:4F8AB845-1E4E-4FCE-B021-BAD63DEBC854@microsoft.com:

> 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.

> The application that uses the MSDE works on the local server, but
> clients fail to connect.
[quoted text clipped - 11 lines]
>> If MSDE is listening on that port, can you access MSDE locally via
>> loopback (127.0.0.1)?

Reply to this Message

Andrea Montanari - 12 Jul 2008 15:51 GMT

hi,

> netstat -a shows listening on Port 1217 (changed ffrom default 1433)
> but clients cannot connect.
[quoted text clipped - 3 lines]
> 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..

Signature

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

Reply to this Message

A Lake - 15 Jul 2008 12:52 GMT

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?

> 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
[quoted text clipped - 15 lines]
> server itself. I have noticed that runing netstat shows that port 1433 isn't
> open?

Reply to this Message

Chris.Cheney - 15 Jul 2008 17:29 GMT

=?Utf-8?B?QSBMYWtl?= <ALake@discussions.microsoft.com> wrote in
news:E9CF7CB1-7880-46DB-B0E8-152B686AC032@microsoft.com:

> Done some testing:
>
[quoted text clipped - 32 lines]
>> 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.

Reply to this Message

Richard Mueller [MVP] - 16 Jul 2008 02:26 GMT

>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
[quoted text clipped - 20 lines]
> 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.

Signature

Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Reply to this Message

A Lake - 16 Jul 2008 20:00 GMT

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.

> >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
[quoted text clipped - 29 lines]
> if SQL Server 2005 prevents MSDE from networking. SQL Server 2005 tools do
> not see the MSDE instance.

Reply to this Message