- Configuración del router del servicio de internet, activar el puerto para escuche la aplicación en el firewall correspondiente.
- Confiuración del Sql Sourface de SQL (TCP/IP), habilitar el Servicio Browser.
- Configurar el Sql server Configuration Manager, y establecer el protocolo de conección a estatico, por default lo maneja dinámico, cada que reinicias el servicio de SQL este le asigna un puerto diferente, en este caso yo utilice el puerto 1433 como ESTATICO y creo que es buena prantica.
- Apagar los servicios y verificar que estos escuchen, en la entrega anterior coloque un software que me parece muy bueno y que les ayuda a comprar que los puertos esten listos tanto en la pc como el router, aqui les dejo la liga de nuevo PortFoward
- Algo que fue importante es como llamar la instancia desde afuera y creo que con lo anterior y si todo sale bien no deberian tener bronca..
Configure Microsoft SQL Server to allow remote connection and remote SQL Management Studio access
To get straight to the point. I have been trying to setup Microsoft SQL Server 2008 to allow remote connection and specifically I wanted the SQL server to allow SQL Server Management Studio on a different computer completely outside the network to be able to access this remote SQL Server and manage it fully without having to login on the remote Dedicated standalone SQL server. I also wanted to be able to access this server remotely from visual studio .net on the client machine. Please be aware that I am trying the extreme settings here of getting client (management studio or visual studio) to connect to the host server (SQL Server) where the client is on a completely different network from the host. i.e. This is a 100% Internet only remote connection not within the same network, lan, or even trusted domains, forests, etc..
There are a lot of setup steps that you need to do but remember there are two main parts of this setup:
- configure SQL Server to accept remote connection and,
- Setup and Configure the firewall running on this server machine to allow inbound incoming connections.
Step 1 is straightforward and there are a lot of MSDN and Technet articles that explain it. But anyway, to make this post informative here are the main steps:
- Go to Programs -> SQL Server -> SQL Server Configuration tools -> SQL Server Configuration Manager.
- Make sure that the SQL Server Browser service is running under (SQL Server Services).
- Go to SQL Server Network Configuration, open the SQL Server instance that you want to use. Make sure that the TCP IP Protocol is enabled.
- right click on this TCP/IP protocol node and click properties. Go to IP Addresses tab. Make sure that the server IP Address is: 1) on the list of IPs, 2) is Active, 3) is enabled.
- You can either: a) let your SQL server to listen on all server ports for any incoming SQL server connection OR b) you can specify which exact port to listen to. For a) set TCP dynamic Port to “0″ and leave the TCP port value blank. To achieve b) set dynamic value to blank and the TCP port to the port number that you want the server to listen to.
- Restart the SQL Server instance service from SQL server Services node (All this configuration is still done from within the SQL Server Configuration Manager).
- Go to the SQL server instant in management studio ->right click -> properties -> connections -> Allow remote connections to this server.
- Also, Go to the SQL server instant in management studio ->right click -> properties -> Security -> Allow SQL Server and Windows authentication mode.
- Look at this article for more details: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/ec77d275-99c7-4cc0-b60d-707bde6f8c67/
Step 2 is the step I struggled with. Basically, I was unable to connect and didn’t know whether it is a Firewall problem or a problem with my configuration of the SQL server (step 1). Best way to troubleshoot that, allow ALL incoming inbound connections on your firewall. Try to connect remotely to the server (using the SQL Server management studio or Visual Studio .NET), if it works, then your issue was the Firewall. If it doesn’t, then it is an issue with SQL server configuration. PLEASE REMEMBER to disallow incoming inbound connections after you did this test!!!
For step 2, you need to do the following to setup and configure your windows server Firewall to accept and allow incoming connections to the host server on which you got SQL Server. So, Host Server Firewall configuration is as follows:
- Allow incoming TCP port number 1433
- Allow incoming UDP port number 1433
- Allow incoming UDP port number 1434
- Allow incoming TCP port number 135
- Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.
Once I have added all these ports to my exception list, i.e. opening all these ports for incoming traffic allowing access to my server, once I have done that, all my problems were solved and I managed to connect to the SQL server remotely via the Internet (not an internal network) where the client and host have no direct connections what so ever.
Some articles and useful resources related to step 2 (Firewall setup):
- http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/ec77d275-99c7-4cc0-b60d-707bde6f8c67/
- http://msdn.microsoft.com/en-us/library/cc646023.aspx
- http://msdn.microsoft.com/en-us/library/ms175043.aspx
One final note: The format to input the server name in the SQL Management Studio connect to server object box is as follows:
for example: 92.37.54.28,2345\InstanceName
If you selected the all ports option. i.e. dynamic port = 0 and TCP Port is blank (as in step 1 above), then the format will be something like that:
server name box should have: 92.37.54.28\InstanceName or just the IP address for the default instance (haven’t tried to setup a default instance though.. I have used named instances for added security.
That’s all for now. Hope this helps. Please write a comment if you have anything to add, correct me or if you have any related question. Please let me know if you need any help or support on this.
Thanks,
Mohamed Mostafa
