Solved

Need help enabling remote connections to MSSQL 2008

Posted on 2011-03-10
18
574 Views
Last Modified: 2012-05-11
MSSQL 2008 on Windows 2008 Server Standard R2

Hi,

I have been bashing my head against a wall for about a month now trying to enable remote access to my MSSSQL server.

I have switched off the firewall completely so and it made no difference so that is not the issue.

I have confirmed that I have enabled the right ip addresses in the sql tcp/ip settings and sql is set to allow remote logins.

There is only 1 instance installed on the server, and local logins work without a hassle.

I have no idea why this is not working, if you have any ideas please let me know!

Xavier.
0
Comment
Question by:locdang
  • 7
  • 7
  • 2
  • +2
18 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35093249
Connect using SSMS right click on your instane pick propertes. Then go to connections and choose 'Allow remote connections'
Please verify also in SQL server configuration manager that tcp/ip is enabled.
0
 
LVL 6

Expert Comment

by:Hernan Ruggiano
ID: 35093250
Try This:
 
      Open the SQL Server Configuration Manager application. This is found in the Configuration Tools folder of the Microsoft SQL Server 2008 folder.
 
      Expand the SQL Server Network Configuration Manager node.

      Expand the SQL Server Network Configuration node.

      Click Protocols for MSSQLSERVER.

      Right-click TCP/IP and click Enable.

      After setting the protocol you must restart the SQL Server service.

      Also Make sure SQL Browser is running,

Regards,
Hernan
0
 
LVL 4

Expert Comment

by:bitla
ID: 35093521
To enable remote connection on SQL Server 2008 Express, see the step below:

1. Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

2. Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.

3. (Optional) Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.


1.)Open SQL Server Configuration Manager. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
2) On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.
3)Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.
4)On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.
5)Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.
6) On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
7) Right-click on TCP/IP and select Enable to enable the protocol.
8) There is a pop-up shown up that you have to restart the SQL Service to apply changes.
9) On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
10)Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.
11) Right-click on the SQL Server Instance and select Properties.
12)On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.
13) Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.
14) Right-click on the SQL Server Instance and select Restart.

NO BAAN BAANG HEAD --- CONNECT WITH PEOPLE FOR SOLUTIONS
0
 
LVL 1

Author Comment

by:locdang
ID: 35101964
Daniel_PL / hruggian: Thank you, but as I stated in the original request that I have already done this.

bitla: Thank you for the in depth instructions however I performed all of what you advised and still no joy.

I should also mention that I have attempted a reinstallation (just incase something went funny during the original install) and still the same problem.

Any more ideas?

Thank you!

Xavier.
0
 
LVL 4

Expert Comment

by:bitla
ID: 35107916
Do you have proxy server? If so you need put an exception on my device /server, it might be causing the issue.

Awaiting for your reply

www.bitlatech.com
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35107997
Please post error message when attemting to connect to SQL Server remotely, also check SQL Server error log, system security log in case of any errors.
You can also try to conect your instance using tcp/ip, see following image (paste your ip address, I used amazon's ip to show how to connect using ip address):

 connection
0
 
LVL 1

Author Comment

by:locdang
ID: 35113679
Hi,

bitla: There are no proxies in place.

Daniel_PL: here is the error message:

Cannot connect to 173.xxx.xxx.11,1433.

------------------------------
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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35115513
So it seems that your server has not enabled tcp/ip or your frewall is blocking your connection.
Double check that your tcp/ip protocol is enabled:


Clipboard01.jpg
0
 
LVL 1

Author Comment

by:locdang
ID: 35115523
Hi,

They are all definately enabled correctly, and whenever I test I turn off the firewall to be sure.

Xavier.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35115586
After enabling tcp/ip have you restarted SQL Server service?
0
 
LVL 1

Author Comment

by:locdang
ID: 35115593
Yes, but I have completed this step before opening this support ticket, as detailed in the original post, but I have reconfirmed and restarted services and the server multiple times since.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35115862
Check your client configuration has tcp/ip enabled. Try connecting  locally using loopback address (127.0.0.1,1433).
Check on which ports server listens:
netstat -an
0
 
LVL 1

Author Comment

by:locdang
ID: 35120961
connecting locally to 127.0.0.1,1433 works fine, and netstat -an reports listening on 1433 is ok
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35121431
So tcp/ip is working, maybe you're picking the wrong ip address (ping -a <your machine name>).
Have you tried enabling browser and connecting using machine name?
0
 
LVL 1

Author Comment

by:locdang
ID: 35123746
Im definately using the correct IP address, otherwise I would not be able to remote into the machine to test locally :)

I guess your beginning to see how weird this situation is!

I wont be able to 'browse' to the machine as it is not on a LAN, its in the cloud.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35126828
Are you sure there are no other firewalls before SQL Server? It seems tcp/ip is enabled and working. I think there is other firewall blocking connection to your server on that port.
0
 
LVL 1

Author Comment

by:locdang
ID: 35126910
Only one firewall, the windows server 2008 one, which when I test I turn off for all profiles. The firewall is as disabled as possible.
0
 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 35873021
Please go through below it might help:

Before starting SSMS, try by clicking on the SSMS icon and select as Run as Administartor and try to login into SSMS.
Try connecting using the DAC (Dedicated Administrator Connection). http://msdn.microsoft.com/en-us/library/ms178068(SQL.90).aspx 

If both the above scenarios will not work delete the old SSL certificates and install new one.

--Venkat
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now