Installing and configuring MSDE (SQLServer Desktop Edition)

Friday 14 October 2005, 14:36:00 | software dev

How to install and configure SQL Server 2000 on Windows XP Home/Professional

First: You cannot install the real SQLServer itself because you need Windows Server edition for that. But you can install MSDE (the freely available 'desktop engine' or 'developer edition' as I call it) and then the SQLServer client tools to be able to use the SQL Enterprise Manager GUI tool to administer your databases (on XP Home too, you don't need XP Pro for this).

This article describes the steps needed to set up a nicely working SQLServer environment based on MSDE.

Download MSDE 2000-A setup from Microsoft.

If you click the setup tool it will show a warning "a strong SA password is required", and it will abort. You need to run it from the command line.

Let's assume "secret42" will be our SA (system admin) password. So run the setup tool from a command prompt like this:

X:\> setup SAPWD="secret42" DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL

It tells the setup to use our chosen SA password, and to enable network connections (by default they are disabled, and we certainly want to be able to access the server from other machines don’t we!), and use mixed mode authentication (windows and sql-server). There are a few more options that can be useful, read chapter 3.4.3 MSDE 2000 Setup Parameters in the readme file that comes with the MSDE setup, if you want to change the target directory for instance.

MSDE will install into C:\Program Files\Microsoft SQL Server by default. Also in your Start menu -> Startup a shortcut to the Service Manager (sqlmangr.exe) will be added, this is the little tool with the status bar icon to stop/start the servers.

Open the Server Manager and start the "SQL Server" service.

Check if the server is listening on external connections (sometimes this is not enabled!) by looking in the ERRORLOG file. That file can usually be found in C:\Program Files\Microsoft SQL Server\MSSQL\LOG. It should contain a few lines that should be similar to the following:

2005-10-14 12:36:23.53 server    SQL server listening on 192.168.1.51: 1433.
2005-10-14 12:36:23.53 server    SQL server listening on 127.0.0.1: 1433.
2005-10-14 12:36:23.53 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2005-10-14 12:36:23.53 server    SQL Server is ready for client connections

If not, you have to enable TCP/IP connections with the svrnetcn.exe tool found in C:\Program Files\Microsoft SQL Server\80\Tools\Binn :

  1. Start svrnetcn.exe
  2. Add TCP/IP to the enabled protocols
  3. Select properties of TCP/IP
  4. Put 0 into port number and apply settings.
  5. Restart SQL Server from within the Service Manager
  6. Back in svrnetcn tool: Put 1433 (the default) back into the TCP/IP port number and save settings
  7. Restart SQL Server again from within the Service Manager.

Now you should really see the above lines in the ERRORLOG file. You can also check if the server is listening on TCP/IP by telnetting to port 1433. You should get a connection.

If somehow you are still unable to connect remotely from other tools (such as a Java program using JDBC drivers) it could be that the authentication mode is wrong. Check in the Enterprise Manager (see below) that the authentication mode is set to "Windows and SQL server". This can be found in Security tab of the database registration's properties. Note that you have to specify the SA user account and password if you want to connect from other tools such as JDBC. You can also create new user accounts if appropriate, of course.

Enterprise Manager

Well MSDE is up and running but it only has the command line osql.exe tool to administer your database. MSDE doesn’t come with SQLServer’s Enterprise Manager GUI tool. But if you also have the SQLServer setup somewhere you can install the client tools from that! Run the setup and it will tell you (on windows XP Home/Professional) that the database server is not available for this platform, only the client tools. Continue the setup (I just clicked "next" on all screens). It will install MDAC too so you might want to update that again to the latest version from the Microsoft website.

From the SQL Enterprise Manager you can create a new server registration and either use Windows authentication or SQL Server Authentication. User name="sa", password="secret42" or whatever SA-password you have chosen during the setup of MSDE.

People have replied:

david

2005-11-01 17:26:00

I think that command should be "svrnetcn" not "srvnetcn". Helpful article. Thanks.

Irmen de Jong

2005-11-01 19:35:00

David, you are right. It must be "svrnetcn", it has been corrected. Thanks for your feedback.

Vincent Lam

2006-04-30 23:17:00

I just want to say thank you for this article. I was already wondering why MS SQL Server won't install on my XP Pro machine...

Nahas

2006-07-06 15:55:00

Its very helpful.. i was fighting with my LAN settings now i got connected. THNX IRMEN. :->

Michael

2008-02-20 20:42:00

Ummm....this is not true. I have SQL server 2000 running on my Windows XP Pro system for development.

Not MSDE mind you, full SQL 2000.

Irmen de Jong

2008-02-21 10:12:00

Michael, can you describe how you were able to install the software? Because the SQLServer 2000 installation CDs that I have come across all gave the notification message that only the client tools can be installed because of the type of windows version:

"Microsoft SQL server 2000 enterprise edition server component is not supported on this operating system, Only client component will be available for installation."

Something tells me you're not using the full version but instead the 'developer edition' or 'workgroup edition', as mentioned in Microsoft's system requirements page

Irmen de Jong

2009-12-22 00:25:00

Notice that all of the above is no longer actual.

The current 'developer' SQLServer is SQL Server 2005 Express Edition and the management tools SQL Server Management studio Express

They both installed without a hitch on my Windows XP machine. You might have to use the SQL Server Configuration Manager to enable the TCP/IP protocol in the network communication. Check the ERRORLOG file for the default port it listens on (4433 on my machine. I think it is dynamically allocated. I haven't investigated this matter much, and am not entirely sure if you can now access the database remotely without further configuration. Maybe more on this later.)