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 :
- Start svrnetcn.exe
- Add TCP/IP to the enabled protocols
- Select properties of TCP/IP
- Put 0 into port number and apply settings.
- Restart SQL Server from within the Service Manager
- Back in svrnetcn tool: Put 1433 (the default) back into the TCP/IP port number and save settings
- 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.
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.