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

Troubleshoot unexpected Jenkins and GitHub SSH connection drops with practical fixes, root cause analysis, and best practices to keep your CI/CD pipeline stable and reliable.

Table of Contents

• Purpose of Blog
  Introduction
• Step 1 — Enable Inbound Rules
• Step 2 — Configure SQL Database
  • Step 2.1—Enable SQL Server and Windows Authentication mode
  • Step 2.2—Enable Remote Connections
  • Step 2.3—Enable SQL Service to listen on TCP/IP
  • Step 2.4—Restart SQL Server Service
• Conclusion

---

Purpose

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

Introduction

Microsoft SQL Server is a relational database management system developed by Microsoft, offering a robust platform for storing and managing data. Utilizing 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, the local port is 1433, and the 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 it.

Step 2.2—Enable Remote Connections

Navigate to Properties → Connections, and under Remote server connections, check the 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, 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.

Stay tuned to blogs.ddevops.com for more deep dives into Infrastructure as Code, CI/CD,
automation, and Silo-Free Engineering

Reference Link:

Reference Blog for you:
How to Install and Configure Zabbix 6.0: Complete Guide
The Evolution of DevOps: Development and Operations
Introduction to CI/CD Pipelines

Written By Hamna Siddiqui