Home / server / Questions / 118801
Accepted
scottm
Asked: 2010-03-04 07:57:58 +0800 CST

How can you connect to a SQL Server not on your domain?

  • 772

I have a test machine that's not allowed on our domain because we are testing corporately unsupported applications (SQL 2008 and Server 2008). I want to use management studio to connect to the SQL2008 server but can't get it working. I have authentication set to mixed-mode, I've checked 'allow remote connections to this server', but when I try to access it, I get the error

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Since it says the provider is Named Pipes, I enabled Named Pipes on the server, but still no dice. I've tried connecting to the system name, the IP, the system name\instance and IP\instance, all to no avail. Is what I'm trying to do not possible?

Edit:

Well, through some basic troubleshooting, I've found that I can't ping the server from my client computer, but I can ping the client computer from the server? They are both plugged into the same switch, and are sitting next to each other. The windows firewall on the server is turned on, is there some specific settings I need to enable?

DAH! So it was the firewall blocking me. How can I enable the firewall and still connect?

sql-server sql-server-2008 remote-access

4 Answers

  1. Best Answer

    In order to connect to a named instance across domains I've always had to create an alias for the instance and have the remote clients connect using the alias. You need to make sure that the SQL Browser Service is running on the server hosting SQL Server for this to work.

    If you're connecting to the default instance it should work fine to connect by IP instead of hostname.

    EDIT:
    For your firewall, you'll need to allow incoming traffic through on TCP port 1433 by default for SQL. If SQL is listening on a different port than 1433 then you'll need to allow traffic on that port instead.

    • 1

  2. I ran xp_readerrorlog to find the port the server was listening on. I never set it, so I assumed it was listening on 1433, however, it was listening on 49192. I enable that port in the firewall and can now connect.

    • 1

  3. Try using ODBC to setup a DSN to test the various connection types. It sometimes gives more diagnostic information, and it's easy to switch between connection types (TCP/IP, Pipes, etc.)

    • 0

  4. Try enabling TCP/IP connections as well.

    • 0