phattrienweb

1. Enable TCP/IP protocol and set a predefined port number (1433)

To enable the TCP/IP protocol for SQL Server 2008, to accept remote connection:

1. Open SQL Server Configuration Manager. Click Start / Programs / Microsoft SQL Server 2008 R2 / Configuration Tools / SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration / Protocols for [Instance Name]. If you see that TCP/IP protocol status is disabled, right click to Enabled it.
3. Open TCP/IP properties to set the correct listening port:
   1. In Protocol / General, set Enabled = Yes.
   2. In IP Addresses / IPAll, set TCP Port = 1433 and TCP Dynamic Port = an empty string (to disable the dynamic port).
   
4. Restart SQL Server Service. Click SQL Server Services in the left panel, right click on SQL Server and select Restart action.

2. Add a firewall rule

By default, windows don’t allow inbound traffic from 1433 port. To allow inbound traffic to SQL Server:

1. Open Windows Firewall with Advanced Security. Click Start / Control Panel / System and Security / Windows Firewall / Advanced Settings.
2. Select Inbound Rules (in left panel), and click on New Rules (in Actions panel).
3. Complete all wizard steps like:
   1. Rule Type = Port.
   2. Protocol and Port = TCP on specific local port 1433.
   3. Action = Allow the connection.
   4. Profile = Domain, Private, Public.
   5. Name = SQL Server TCP/IP (1433).
   

3. Stop SQL Server Browser Service

Please, for security reasons disable the service SQL Server Browser. This service is not a requirement to accept remote (or local) connection to SQL Server!

1. Open SQL Server Configuration Manager (see the previous step 1).
2. Click SQL Server Services in the left panel, right click on SQL Server Browser and select Properties action.
   1. In Log On tab, click on Stop button.
   2. In Service tab, set Start Mode to Manual.

4. Check SQL Server Authentication

Eventually, check the authentication mode set in SQL Server.

1. Open Microsoft SQL Server Management Studio and connect you to SQL server.
2. Right click on your SQL instance and select Properties.
3. In Server Properties windows, select Security page.
4. Select SQL Server and Windows Authentication mode, and verify if you have set a correct password to the login sa.
   

5. Open a remote connection

To verify if your SQL Server is correctly configured, you can open a SQL IDE (like the excellent free tools SqlDbx). In Login page, set the following server string syntax: “tcp:[ServerName\SQLInstance],[Port]” (SqlInstance is empty if you have installed SQL Server with the default instance).
Example:

• Server = tcp:MySqlServer,1433
• Database = Master
• User = sa
• Password = xxx