Solved

Need help enabling remote connections to MSSQL 2008

Posted on 2011-03-10
18
573 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

758 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

18 Experts available now in Live!

Get 1:1 Help Now