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

Question

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?

Solution 1

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.

Solution 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.

Comments

[squillman]

@scotty: see the edit in my answer re: your firewall.

[Kara Marfia]

From yhour edit, assuming no dns issues, it sounds as if you need to open the SQL ports in the server firewall.

[scottm]

@Kara Marfia, I've enabled UDP 1434 and TCP 1433, but have the same problem when the firewall is turned on.

[scottm]

The server isn't on a domain, just the client, does your advice still apply?

[squillman]

@scotty2012 - yes, give it a whirl!

[scottm]

Doesn't work, but I think I may have a more basic problem, see the edit

[Kara Marfia]

This answer being accepted says to me that your issue is resolved, but your edit & the most recent comment sound as if it's not?