Windows
  • 87 Views
  • 23/11/2025

How to enable remote mssql server on windows

Enabling a remote Microsoft SQL Server on Windows involves a few steps: configuring SQL Server to allow remote connections, opening firewall ports, and optionally enabling SQL Server authentication. Here’s a detailed step-by-step guide:

1. Enable TCP/IP in SQL Server Configuration

  • Open SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration → click Protocols for [InstanceName].
  • Ensure TCP/IP is Enabled.
    • Right-click TCP/IP, select Properties → go to IP Addresses tab:
    • Scroll down to IPAll section.
  • Set TCP Port to 1433 (default) or another port of your choice.
  • Click OK, then Restart SQL Server service for the changes to take effect.

2. Allow SQL Server Authentication

  • Open SQL Server Management Studio (SSMS).
  • Connect to your server → right-click server → Properties → Security.
  • Set Server Authentication to SQL Server and Windows Authentication mode.
  • Restart the SQL Server service.
  • Make sure you have a SQL login user:
CREATE LOGIN your_user WITH PASSWORD = 'StrongPassword';
ALTER LOGIN your_user ENABLE;

3. Configure Windows Firewall

  • Open Windows Defender Firewall → Advanced Settings.
  • Go to Inbound Rules → New Rule…
    • Rule Type: Port
    • Protocol: TCP
    • Port: 1433 (or your configured port)
    • Action: Allow the connection
    • Profile: Domain, Private, Public (as needed)
    • Name: SQL Server TCP 1433
  • Apply and save.

4. Test Remote Connection

sqlcmd -S <ServerIP>,1433 -U your_user -P StrongPassword

5. Optional: Configure SQL Browser Service (for named instances)

  • If you are using a named instance instead of the default instance:
    • Open SQL Server Configuration Manager → SQL Server Services.
    • Start SQL Server Browser.
    • Ensure UDP port 1434 is open in firewall for the browser.

Thanks for visit my website