---
title: "Prepairing Data Sources: Microsoft SQL Server"
url: "https://docs.noreja.com/en/article/managing-data-sources-microsoft-sql-server"
language: "en"
last_updated: "2026-02-28T11:35:53.497Z"
---

# Prepairing Data Sources: Microsoft SQL Server

Preparing Microsoft SQL Server for external connectivity is a critical step in establishing a secure and reliable analytics infrastructure. By enabling remote connections, configuring TCP/IP protocols, opening firewall ports, setting authentication modes, provisioning least-privilege users, and optionally enforcing encryption, administrators ensure that tools like Noreja can access data safely and efficiently. A properly configured SQL Server environment minimizes connectivity issues, strengthens security posture, and provides a stable foundation for scalable process intelligence and advanced analytics.

## Overview

Before the Noreja application can connect to SQL Server, the database admin must ensure the server is listening on a network protocol, allow remote connections, configure firewall and SQL Browser settings, and create a database user with appropriate permissions. They should also consider encryption requirements, fixed ports for named instances, and validation of connection strings. The steps below reference Microsoft documentation to guide each task.

## Enable Remote Connections

By default, SQL Server may not accept connections from other machines. You must explicitly enable remote access:

**Allow Remote Connections**

In SQL Server Management Studio (SSMS), right-click the server in Object Explorer and choose **Properties**.

Select **Connections** and check **Allow remote connections to this server** [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-ver16&utm_source=chatgpt.com).

**Configure via T-SQL** (alternative)

```plaintext
EXEC sp_configure 'remote access', 1;
RECONFIGURE;
```

This ensures logins from remote servers are accepted [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/remote-servers?view=sql-server-ver17&utm_source=chatgpt.com).

**Restart Services**  
After changing remote-access settings, restart the **SQL Server** service to apply changes [apesoftware.com](https://www.apesoftware.com/calibration-control/help/sql-remote-connections?utm_source=chatgpt.com).

## Enable and Configure Network Protocols

To allow TCP/IP connections—the most common protocol for external tools—you must enable and configure protocols in SQL Server Configuration Manager:

**Open Configuration Manager**  
Go to **SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for <Instance>**.

**Enable TCP/IP**

Right-click **TCP/IP**, select **Enable**, then restart the SQL Server service [knowledgebase.apexsql.com](https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/?utm_source=chatgpt.com)[learn.microsoft.com](https://learn.microsoft.com/en-us/answers/questions/2123222/sql-remote-connection-for-sql-server-2022-is-not-w?utm_source=chatgpt.com).

On the **IP Addresses** tab, set **TCP Port** for each IP (e.g., `1433`) and clear any **TCP Dynamic Ports** to enforce a static port [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?view=sql-server-ver17&utm_source=chatgpt.com).

**SQL Server Browser Service**  
For named instances, ensure **SQL Server Browser** is set to **Automatic** startup and is **Running**. This service listens on UDP 1434 and directs client connections to the correct port [knowledgebase.apexsql.com](https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/?utm_source=chatgpt.com)[learn.microsoft.com](https://learn.microsoft.com/en-us/answers/questions/1276660/unable-to-connect-remotely-to-new-sqlserver-2022-e?utm_source=chatgpt.com).

## Configure Windows Firewall (or Network ACLs)

Incoming traffic on the SQL Server port must be allowed:

**Open Firewall Ports**

Allow **TCP 1433** (default instance) or the custom port you configured.

If using named instances and dynamic ports, you may need to allow `sqlservr.exe` as a program exception instead of a port [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver17&utm_source=chatgpt.com).

**Enable SQL Server Browser**  
If clients connect using instance names (not explicit ports), allow **UDP 1434** for the Browser service [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver17&utm_source=chatgpt.com).

**Test Connectivity**  
From a client machine, use `telnet servername 1433` or `Test-NetConnection -ComputerName servername -Port 1433` to confirm the port is open [learn.microsoft.com](https://learn.microsoft.com/en-us/answers/questions/2123222/sql-remote-connection-for-sql-server-2022-is-not-w?utm_source=chatgpt.com).

## Choose and Configure Authentication Mode

SQL Server supports two authentication modes; you must enable the one matching your security policy:

-   **Mixed Mode (SQL & Windows Authentication)** In SSMS, right-click server → **Properties** → **Security** → select **SQL Server and Windows Authentication mode** [apesoftware.com](https://www.apesoftware.com/calibration-control/help/sql-remote-connections?utm_source=chatgpt.com).
-   **Windows Authentication Only** Use this when leveraging Active Directory; no SQL-level passwords are used. Ensure the external tool supports integrated security.

**Restart Required**  
Changing authentication mode requires restarting the SQL Server service [apesoftware.com](https://www.apesoftware.com/calibration-control/help/sql-remote-connections?utm_source=chatgpt.com).

## Create a Login and Database User

Grant the external tool a dedicated account with least-privilege access:

**Create Server Login**

```plaintext
CREATE LOGIN [tool_user] WITH PASSWORD = 'StrongP@ssw0rd';
```

**Create Database User**

```plaintext
USE [YourDatabase];
CREATE USER [tool_user] FOR LOGIN [tool_user];
```

**Grant Permissions**  
At minimum, grant `CONNECT` on the database and `SELECT` on required schemas or tables:

```plaintext
GRANT CONNECT TO [tool_user];
GRANT SELECT ON SCHEMA::dbo TO [tool_user];
```

Adjust for INSERT/UPDATE/EXECUTE as needed by your tool [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/sql-server/connect-to-database-engine?view=sql-server-ver17&utm_source=chatgpt.com).

## Optional: Enforce Encryption

If the tool requires encrypted connections:

-   **Install Server Certificate**  
    Use a CA-signed certificate in the **SQL Server Configuration Manager → Protocols → Certificate** tab.
-   **Force Encryption**  
    On the **Flags** tab for TCP/IP, set **Force Encryption** to **Yes**, then restart the service [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/sql-server/connect-to-database-engine?view=sql-server-ver17&utm_source=chatgpt.com).
-   **Client Configuration**  
    Ensure the tool’s connection string includes `Encrypt=True;TrustServerCertificate=False;`.

## Verify and Test

Before production use, confirm everything works end-to-end:

**Test via SSMS**  
From a remote machine, connect using the tool’s credentials and server name/port [learn.microsoft.com](https://learn.microsoft.com/en-us/answers/questions/1183324/using-ssms-unable-to-connect-to-a-remote-%28network%29?utm_source=chatgpt.com).

**Check Logs**  
Review SQL Server Error Log for any failed login or network-related errors.

**Monitor Performance**  
Use Dynamic Management Views (e.g., `sys.dm_exec_connections`) to verify connection counts and types.

By following these steps—enabling remote connections and TCP/IP, opening firewall ports, configuring authentication, creating a dedicated user, and optionally enforcing encryption—DBAs ensure that external tools can reliably and securely connect to Microsoft SQL Server.