[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1073
  • Last Modified:

Can't Connect Remotely to SQL Server 2008

I installed a new Windows Server 2008 with a fresh install of SQL Server 2008.  I cannot connect to SQL Server from a remote workstation.  I "believe" that I have configured SQL Server for port 1433 and have opened port 1433 on the Windows Server firewall.  However, both Windows 2008 and SQL 2008 have such different UIs from my previous servers that it is possible I may have missed something that might otherwise be considered obvious.

Any and all help is greatly appreciated.  Thanks
0
Edward Stevens
Asked:
Edward Stevens
  • 13
  • 10
1 Solution
 
Maniac_47Commented:
What types of errors are you getting?  If it is a "Named Pipes" error, you may have to enter the SQL Configuration console and enable either TCP/IP or Named Pipes (or both if you prefer).  

I would be curious to see what error messages are appearing to see if they are timeout based or specific to a configuration, could you please try connecting through SQL Management Studio on the server / on a workstation in the environment and post the error messages that you get?
0
 
Edward StevensIT ConsultantAuthor Commented:
The error message on the remote workstation is as follows:

TITLE: Connect to Server
------------------------------
Cannot connect to PegSys-Srvr1\Production.
------------------------------
ADDITIONAL INFORMATION:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476 
------------------------------
BUTTONS:
OK
------------------------------

Thanks
0
 
Edward StevensIT ConsultantAuthor Commented:
In SQL Server 2005, you had to go to the Surface Area Configuration tool to allow SQL to accept remote connections.  Where is that UI in SQL 2008?  Maybe that is what I missed.  Just a thought.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Maniac_47Commented:
Start -> All Programs -> SQL Server 2008 -> Configuration Tools -> Sql Server Configuration Utility.

In there it will be very simmilar to '05 as the surface area config console.  Check under Sql Server Network Configuration and verify the protocols.
0
 
Edward StevensIT ConsultantAuthor Commented:
Okay.  That was good info.  I found that part of the system.  When  I look at it, there are several parts of the tree that have Client Protocols as a branch.
Under Native Client (32-bit), I see that Shared Memory, TCP/IP, and Named Pipes are enabled.  TCP/IP is set to 1433.
Under SQL Server Network Configuration, I see that Protocols for Production (my instance name) has Shared Memory and TCP/IP enabled.  Named Pipes is disabled in this branch as well as VIA which is disabled on all branches.  TCP/IP is set as follows...
Enabled = YES
Keep Alive = 30000
Listen All = Yes

Each of the IP instances (1-6, and ALL) are set as Active, Enabled, Dynamic Ports = 0, and Port = {blank}.  {blank} represents no actual value, not the physical text {blank}.

Under Native Client (assumed 64-bit since it doesn't say and the previous Client stated 32-bit), I see Shared Memory, TCP/IP, and Named Pipes as enabled.  TCP/IP is set to port 1433.
0
 
Maniac_47Commented:
Under the Network Configuration, I would enable the Named Pipes protocol and verify that isn't the issue.

Also, when you mention "remote workstation" are you speaking of truly remote (i.e. VPN into the machine) or are you speaking of a workstation on the same network, just not the studio on that server?

If it is truly remote, we'd have to look at your NAT settings and external firewall configuration.  
What kind of authentication have you set up for your SQL instance?
0
 
Edward StevensIT ConsultantAuthor Commented:
The "remote" workstation is on the LAN with the server, just indicating that it is not the server itself.

I made the change for Named Pipes and restarted the service.  No dice.  I shouldn't have to reboot but will do so if you think it is worth a shot.  The server has no users attached currently so I am free to do so at will.
0
 
Maniac_47Commented:
Is this a SQL Server 2008 Express install?  If so the rules to access the instance are different than with a standard edition.

If it is an Express install I believe this can help you:
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

if it is a standard install, please let me know and I can see what else may be going on here.
0
 
Edward StevensIT ConsultantAuthor Commented:
It is Standard, not Express.  Thanks
0
 
Maniac_47Commented:
Just a shot in the dark - are all the SQL services running within services.msc?  Most importantly in this case, the SQL Server Browser Service?  That would produce this error of the server not being found.

Also, just as a sanity check on my part, can you ping/access other items on this server from the workstation?  I know, I know, just something I'd normally have to check.
0
 
Edward StevensIT ConsultantAuthor Commented:
All services for SQL appear to be running except for SQL Active Directory Helper Service which is not running and is Disabled.

The server is my production server for file sharing and Exchange 2010 as well as SQL (when it is running).  No other users on it now but I am processing email and accessing my file shares so the server is working okay as far as I know.
0
 
Maniac_47Commented:
Can you verify the firewall is allowing the incoming and outgoing connections on that port?  I know you said you had configured it, but it may have just been for the outgoing connection or vice versa.  If that's all looking good, give me a few more minutes to see what else may be going on.
0
 
Edward StevensIT ConsultantAuthor Commented:
How can I tell?  I have an Inbound rule for 1433 and an Outboud rule for 1433.  I assume they are working.  Is there a way for me to test?
0
 
Maniac_47Commented:
you can telnet SERVERNAME 1433 and see if it allows the connection from the remote machine

If that is working, that would eliminate this item from the troubleshooting process.

Also, please disable the firewall on the workstation so we can be sure there isn't something else going on with the workstation.
0
 
Edward StevensIT ConsultantAuthor Commented:
Okay.  I am on a call right now and will get back to you as soon as I can do what you have suggested.  Hopefully within the hour.  Thanks
0
 
Edward StevensIT ConsultantAuthor Commented:
I was able to perform the test.  When I try to telnet using port 1433, no connection.  If I then telnet using port 80, I get connected.
This suggests a firewall issue, correct?  I have entries in the Windows Server 2008 firewall for both inbound and outbound rules.  Could they be configured wrong?
0
 
Maniac_47Commented:
Would it be possible to disable the firewalls on both the server and the workstation?  If so, that would tell us if SQL is configured correctly to accept the connections on that port.  If it connects successfully, then that would denote a firewall issue with either the client or server.

Let me see what else would be happening server side that may be preventing connections on that port
0
 
Edward StevensIT ConsultantAuthor Commented:
Okay, so here is something really weird...but then again...it is Microsoft hard at work.  :)
I turned off both machine's firewalls (workstation and server) and I could connect.  I then systematically turned on each firewall one at a time, testing between each one to see where the problem was (Windows Server 2008 has three firewalls, one for Domain, one for Public, and one for Private networks.
At each step in the progression, I could still connect.  Could it be something as stupid as "I needed to turn the firewalls off and back on to update the rules"?
I am rebooting both machines now so that I can test to prove that I am still able to connect after a restart of either machine to eliminate any possible fluke in the testing.
I will post my results shortly.
0
 
Edward StevensIT ConsultantAuthor Commented:
So...no connectivity after the reboot will all firewalls turned on.  After further testing, I have determined that the issue is with the Windows Server 2008 Domain Network fiewall.  When that firewall is turned off, I have connectivity.
Any ideas on how to properly configure that one?
0
 
Maniac_47Commented:
ok, we're getting somewhere now - sorry I was stuck in traffic after being on-site with a client.

I would check to ensure the firewall also has ports for:

NETBIOS naming
1434 for SQL Browsing Service
135 for T-SQL Debugging (for future use)

I believe the issue will be with the 1434 port, as it states it can't see the server after it is enabled with 1433 open.  Please open those ports and let me know if that doesn't fix it

*crosses fingers*
0
 
Edward StevensIT ConsultantAuthor Commented:
I laso added ports 1433, 1434, 135, 137, 138, and 139.
0
 
Edward StevensIT ConsultantAuthor Commented:
Thanks for your help on this!
0
 
Maniac_47Commented:
so glad that worked!

its always the last, simple thing to check :-)

Thank You for being so patient!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 13
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now