locdang
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
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
ASKER
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.
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.
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
Awaiting for your reply
www.bitlatech.com
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):
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):
ASKER
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)
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)
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
Double check that your tcp/ip protocol is enabled:
Clipboard01.jpg
ASKER
Hi,
They are all definately enabled correctly, and whenever I test I turn off the firewall to be sure.
Xavier.
They are all definately enabled correctly, and whenever I test I turn off the firewall to be sure.
Xavier.
After enabling tcp/ip have you restarted SQL Server service?
ASKER
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.
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
Check on which ports server listens:
netstat -an
ASKER
connecting locally to 127.0.0.1,1433 works fine, and netstat -an reports listening on 1433 is ok
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?
Have you tried enabling browser and connecting using machine name?
ASKER
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.
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.
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.
ASKER
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.
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
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
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