Troubleshooting SQL Server connection issues.

Question

I'm relatively inexperienced at SQL Server, but am trying to troubleshoot some of the (non-SQL Server-related) logic and UI issues in a Delphi app someone else wrote which uses it. Currently trying to get the dev environment functioning.

Here's the environment:

Dev machine: Virtual machine (VMWare) running Windows XP, using Bridged Networking. Can connect to folders/files on the server machine fine. I have an old version of SQL Server 2005 Express installed on this machine. I can connect to it okay from this same machine, using the SQL Server Management Studio Express, and also via my Delphi components (ADO, can read/write table data to a grid, etc).

"Server" (aka S1): Actually just a Windows XP workstation. I put a bone stock SQL Server 2008 install on it. Since installing I have enabled Protocols for SQLEXPRESS -> Name Pipes, and TCP/IP, using the Configuration Manager, and restarted the SQL Server service. The SQL Server is set to use Windows NT authorization. This is the machine and SQL Server instance I need to connect to from the Dev machine.

I can connect to the S1 SQL Server instance from the same machine using the Management Studio, but I cannot connect to it from the Dev box, Using either the 2005 Management Studio (which I'm not even sure is supposed to be possible given different versions) or the Delphi components string builder (TADOConnection using OLDEDB Provider for SQL Server). When I try the latter, the S1 SQL Server instance does not show up in the drop-down list, and if I manually type in S1\SQLEXPRESS, using either Windows NT Integrated Security or a Specified Username and Password, I get the following error:

---------------------------
Microsoft Data Link Error
---------------------------
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
---------------------------
OK   
---------------------------

This is a peer-to-peer network; no domain controller, etc. Username and passwords on the two machines are identical.

How do I go about troubleshooting this?

================================================
Using Andomar's pointer and the link I commented with below (which explains the Surface Area Configuration tool went away in SQL Server 2008), I've gotten to this in the S1 machine's SQL Server Management Studio settings, and set it as follows, but still no non-local connection ability. What am I missing?

Answer 1

By default, SQL Server Express listens only on the localhost.

You can change this setting using the SQL Server Surface Configuration Area tool. From the startup screen, choose "Surface Area Configuration..." Under the SQLEXPRESS instance, open "Remote Connections". Make sure "Local and remote connections" is checked.

Answer 2

You need to make sure that the Server itself is listening for remote connection. This can be done using the SQL Server Surface Configuration Area tool. I only have 2005 installed but from the screen shots you posted you are working in the wrong area. You need to enable remote connections for the Database engine. The properties you posted are un-related to that setting. You will get the option to add it for TCP/IP only or TCP/IP and Named Pipes.

In addition, you need to make sure the SQL Browser service is running and the necessary ports are open. By default SQL server will come up on a random port. The SQL Server Browser service starts up on a fixed port (1434) and provides the information back to clients as to what port the server you are looking for is running on.

This is necessary because you can run multiple instances of SQL Server on one machine.

You can setup SQL server to listen on a specific port and then include the port number in the connection string bypassing the SQL Server Browser but that requires some more work.