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.

J


  1. ar_pad

    Hi,
    I read ur points. Quite good.
    Actually i tried this before few months. spent more than a week. but failed. so i just used SQL 2000.
    Now it’s a time to change from SQL 2000 to MSDE.
    So again trying my best. but still I couldn’t make the connection. if i test the connection from Server Explorer -> database connection, connection succeeded. but when i try to establish it via pgm(VB.NET), failed to open the connection.
    As u said,I enabled TCP/IP.
    Firewall – off
    anti virus – unloaded.

    getting error like “Server not found – “CMP1\MSDE1”

    Can u guide me to solve this issue?
    Thank You!

  2. Hi,

    Seems you have not specified the IP address. eg: “Server not found – “CMP1\MSDE1″. Specify the IP address instead of CMP1. That should solve it.

    Cheers!

  3. ar_pad

    Hi,
    Thanks for ur reply.
    Actually i tried with IP too. Still not connected.

    My laptop is a new one. doesn’t have IP. At present i’m using IP of a loop back adapter.SQL 2000 worked when i mention the name of the computer.

    One more thing what i want to clarify is,I found that Both default(SQL 2000) and MSDE are using the same port. Is it correct?

    Let me show my connection string.
    myconnection = “Server = xx.x.x.x;Data Source = CMP1\MSDE1;Database = Example;User id =sa; Password=xyz;”

    Is it ok? if not, can u share ur connection string with me.
    Thank You.

  4. Knith

    Thanks for your reply…

    Its very helpful for me…I have the same problem and take a long time to resolve this problem. I take a short time when I found your topic.

    Thank you so much…GREAT!!!

    Knith

  5. XiquiN

    Thank you very very much. Our company lost a few days and money because of this problem.

    Your article is perfect and help us really much. Superb…

  6. osman

    i have same problem a few months. this article very good but not solved the problem.when i install sql server 2000 mobile connection ok but when i install msde 2000 the problem occures (“server not found”) what will i do?help me please.how can i connect msde from mobile. i don’t want to install sql server 2000 personal to other computers😦

  7. Thanks alot. Your artical really helped me out.

  8. Nice post . Very helpful. Saved me a lot times

  9. Mirza H. - Bosnia

    I found that SQL Server Browser windows service, after MSDE (SQL Serv Expr) is instaled, sometimes is disabled. I spend days looking for regular options to enable network loging, network services etc.etc.and everything was fine, but network loging simply did not worked.
    When I enabled and started this service miracle happend…
    Before this afcourse you have to check with Configuration Manager is TCP/IP Clieant protocol enabled, with Management Studio is Sql Server authentication mode selected, and those standard things. After those checks everything worked for me at Vista and XP.

    Regards.




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



%d bloggers like this: