How do I connect a Windows XP machine to another client system running SQL Server?
If you're attempting to connect to an instance of Microsoft SQL Server from a Windows XP system, you may run into a number of odd problems. Windows XP in particular may have problems because of the operating system's age and the various odd, corner-case incompatibilities that can arise between it and other versions of Windows. Also, if the SQL Server instance you're using is running on a client version of the OS -- say, you're using SQL Server Express on Windows Vista, Windows 7 or Windows 8 -- the oddities can multiply.
More on Windows installation and management
Windows migrations continue despite BYOD and the cloud
Top Windows security tips of the year (so far)
Which Windows user profile should you be using?
Will there ever be one Windows management console to rule them all?
Here are some quick tips for making sure that connections from Windows XP work with Microsoft SQL Server instances on workstation versions of Windows.
Be sure to use the proper server name (or address) and the proper server instance. Multiple instances of Microsoft SQL Server can be running on the same system, so just knowing which system to connect to isn't enough. If you're using SQL Server Express, the default instance name is \sqlexpress, so the connection name would be something like computer-name\sqlexpress.
Make sure your instance of SQL Server is configured to accept connections via TCP/IP. For security's sake, recent versions of SQL Server by default accept only local connections via the shared memory protocol. Make sure the server is set to accept connections via TCP/IP for remote hosts.
Temporarily turn off firewalls on the server and client machines as a troubleshooting measure. Emphasis on the word temporarily. Unless you're working on an entirely private network segment -- and even then -- you don't want to leave firewalls turned off on production machines. Leave them off until you get a connection running, then debug them separately if need be.
Don't use dynamic port assignments for Microsoft SQL Server. If you hard-assign SQL Server to listen on Port 1433 -- the standard port assignment for SQL Server -- this makes it easier to set exceptions for any firewalls that might be running on either the client or server machines. In short, it narrows down the range of things that can go wrong.
For one of the most complete and detailed rundowns on troubleshooting any connection to SQL Server, check out Rick Byham's encyclopedic article at TechNet.
This was first published in November 2012