MSDE connectivity problems on Windows XP/SP2
I wasted one whole day yesterday trying to resolve a connectivity problem to a MSDE database on Windows XP/SP2 platform. I came across this issue when I was trying to connect as Pocket PC application to a SQL Server database. When connecting to SQL Server from Pocket PC applications using .NETCF, the connection has to be made over TCP/IP, and the host computer running Windows XP/ SP2 with MSDE was blocking the connection. It was taking an unsually long time and was giving out an SQLException. Mind you on .NETCF, that was all there was on the error message, with no pointers as to what could be the cause. It took quite a bit of wndering around to get into the correct path.
I searched high and low, tried all sorts of settings with Windows Firewall, creating exceptions, adding ports, etc. but nothing seemed to work. Then I remembered something about having to install SQL Server SP3 to correct the open the ports and that is the path that resolved my issue. Actually, I downloaded the SP4 for MSDE and did a fresh installation.
Here I will list some of the things to look into, when there are connectivity problems with MSDE.
- MSDE is installed by default with TCP/IP disabled. You have to enable TCP/IP manually. You can find how you can do this here.
- MSDE is installed with Windows Authentication only, by default. How to change this is detailed here.
- The “sa” user has a blank password in default installation. How to change this behavior is detailed here.
- On Windows XP / SP 2 computers, the firewall closes all TCP and UDP ports thus forcing MSDE to operate in isolation, allowing access by only programs o local PC via named pipes and shared memory. How this can be overcome is listed here.
But, if you experiencing connection difficulties with MSDE over TCP/IP, the best action path to take is to install the SQL Server 2000 SP3 or above. I used the SP4 for MSDE and it solved all my TCP/IP connectivity problems. So I think it is the easiest and the simplest path to take. You can download the SP here.
I hope this info helps someone to solve these problems faster. I discovered the solutions the long way. I hope you’d take the short way.