How to Enable Remote Connections to Windows SQL Server using IP Address

Purpose:

We are adding an IP address to windows SQL server in order to allow access to the database from that particular IP remotely.

What’s Microsoft SQL Server?

Microsoft SQL Server is a relational database management system developed by Microsoft, offering a robust platform for storing and managing data. Utilising Transact-SQL as its query language, SQL Server provides advanced security features, scalability from small to enterprise-level deployments, and additional components like Integration Services, Reporting Services, and Analysis Services for data integration, reporting, and analytics. With support for in-memory processing, advanced analytics with R and Python, and seamless integration with other Microsoft technologies, SQL Server is a versatile and widely used solution for efficient and secure data management across various industries.

Step 1 — Enable Inbound Rules:

In the start menu search Windows Defender Firewall with Advanced Security. On the left, in the navigation column, click on Inbound Rules. Now double click on the SQL file you want to configure.

Navigate to Protocols and Ports and verify the Protocol type is TCP, Local port is 1433, and Remote port is set to All Ports.

Now navigate to the Scope section and under Remote IP address, select These IP addresses and click on Add…:

Now under This IP address or subnet enter the IP address and press OK, then press Apply and then OK again.

Step 2 — Configure SQL Database:

Step 2.1 — Enable SQL Server and Windows Authentication mode:

Open SQL Server Management Studio (SSMS) and log in to your database. Right click on the database and click on Properties → Security and then under Server authentication select SQL Server and Windows Authentication mode to enable.

Step 2.2— Enable Remote Connections:

Navigate to Properties → Connections and under Remote server connections check-mark on Allow remote connections to this server checkbox.

Step 2.3 —Enable SQL Service to listen on TCP/IP:

Open SQL Server Configuration Manager and in SQL Server Network Configuration → Protocols for MSSQLSERVER and right click on TCP/IP and press enable.

Step 2.4 — Restart SQL Server Service:

Go to SQL Server Service and right click on SQL Server (MSSQLSERVER) and press Restart.

Conclusion

Now you can run SSMS on the client machine, and connect to the SQL Server remotely by specifying its local or public IP address, or name.