CODEWITHSUNDEEP
sql-server
Andie
Andie

Reputation: 473

Set SQL SERVER Express 2017 to allow remote connections

Just recovering my server from a major crash, which led to windows server 2016 being re-installed...

I have a desktop application which connects to the sql server database, trouble is now I have the database setup on the server I cant remember how to make it available to external sources (the application)

Can you help?

Upvotes: 6

Views: 24266

Answers (2)

Rick P
Rick P

Reputation: 51

For any of you using Named instances (or SQL Express) you need to also open up UDP port 1434. Here are command lines I used instead of the above UI process.

To Open TCP (SQL Server and SQL Browser):

NETSH advFirewall firewall add rule name="Allow: Inbound: TCP: SQL Server Services" dir=in action=allow protocol=TCP localport=1433,1434,2382

To Open UDP (SQL Server Dynamic for Named and SQLEXPRESS):

NETSH advFirewall firewall add rule name="Allow: Inbound: TCP: SQL Server Services Dyn" dir=in action=allow protocol=UDP localport=1434

Upvotes: 5

Melvin
Melvin

Reputation: 435

Use the following steps to enable remote connections to your SQL Server,

  1. Open SQL Server Management Studio.
  2. Right-click your server's name and select Properties.
  3. Select Connections option
  4. Tick the checkbox "Allow remote connections to this server."
  5. Select OK.

Enable TCP/IP and Open 1433 port

  1. Take SQL Server Configuration Manager.
  2. Select SQLServer network Configuration->Protocols for MSSQLServer
  3. In the right-hand pane,make sure that TCP/IP is Enabled.
  4. Right click on TCP/IP and select the Properties option.
  5. In the TCP/IP Properties dialog select the IP Addresses tab and scroll down to IPAII.

  6. Make sure that the TCP Port is 1433.

    Configure a Windows Firewall for Database Engine Access

  7. Take Windows Administrative tools

  8. From Administrative Tools select the Windows Firewall with Advanced Security option
  9. In the Windows Firewall with Advanced Security dialog, click on the Inbound Rules option and select the New Rule command
  10. In the New Inbound Rule wizard select the Port option and click Next
  11. In the Protocols and Ports window specify the protocols and ports to which a rule applies. Select the TCP option, in the Specific local ports text box enter the 1433 port, and click Next

  12. In the Action window select the Allow the connection to specify the action to be taken when a connection matches the conditions specified in the rule

  13. Specify the profiles for which the rule applies in the Profile window, and click Next

  14. In the last window specify the name of the created rule and click the Finish button

Configure remote access on a named instance of SQL Server

  1. Take Windows Administrative tools
  2. From Administrative Tools select Windows Firewall with Advanced Security option
  3. In the Windows Firewall with Advanced Security dialog, click on the Inbound Rules option and select the New Rule command
  4. In the New Inbound Rule wizard select the Port option and click Next
  5. In the Protocols and Ports window specify the protocols and ports to which a rule applies. Select the TCP option, in the Specific local ports text box enter the 1433 port, and click Next
  6. In the Action window select the Allow the connection to specify the action to be taken when a connection matches the conditions specified in the rule
  7. Specify the profiles for which the rule applies in the Profile window, and click Next
  8. In the last window specify the name of the created rule and click the Finish button

Upvotes: 24

Related Questions