• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

Need help enabling remote connections to MSSQL 2008

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
locdang
Asked:
locdang
  • 7
  • 7
  • 2
  • +2
1 Solution
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
Hernan RuggianoCommented:
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
 
bitlaCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
locdangAuthor Commented:
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
 
bitlaCommented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
locdangAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
locdangAuthor Commented:
Hi,

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

Xavier.
0
 
Daniel_PLDB Expert/ArchitectCommented:
After enabling tcp/ip have you restarted SQL Server service?
0
 
locdangAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
locdangAuthor Commented:
connecting locally to 127.0.0.1,1433 works fine, and netstat -an reports listening on 1433 is ok
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
locdangAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
locdangAuthor Commented:
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
 
VENKAT KOKULLASQL Server DBACommented:
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

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.

  • 7
  • 7
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now