3

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?

Improve this question
3
  • @scotty: see the edit in my answer re: your firewall.
    – squillman
    Mar 3, 2010 at 16:32
  • From yhour edit, assuming no dns issues, it sounds as if you need to open the SQL ports in the server firewall.
    – Kara Marfia
    Mar 3, 2010 at 16:32
  • @Kara Marfia, I've enabled UDP 1434 and TCP 1433, but have the same problem when the firewall is turned on.
    – scottm
    Mar 3, 2010 at 16:34

4 Answers 4

Reset to default
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.

Improve this answer
4
  • The server isn't on a domain, just the client, does your advice still apply?
    – scottm
    Mar 3, 2010 at 16:11
  • @scotty2012 - yes, give it a whirl!
    – squillman
    Mar 3, 2010 at 16:17
  • Doesn't work, but I think I may have a more basic problem, see the edit
    – scottm
    Mar 3, 2010 at 16:28
  • 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?
    – Kara Marfia
    Mar 6, 2010 at 18:11
1

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.

Improve this answer
0

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

Improve this answer
0

Try enabling TCP/IP connections as well.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .