Skip to content

Add MSSQL Data Source

This page explains the process of adding a Microsoft SQL Server (MSSQL) data source in the Pirivision Port module.

After the MSSQL connection is created successfully, the data source can be used for T-SQL queries in the Compass module. Queries prepared in Compass are then transformed into chart / KPI formats in Cartography and used in Horizon or Atlas dashboards.

Prerequisite

Before adding an MSSQL data source the following must be ready:

  • SQL Server host/IP and port (default: 1433); instance name if Named Instance is used
  • Database name
  • Authentication type (SQL Server / Windows Authentication) and credentials
  • The TCP/IP protocol must be enabled on the target server

→ Port — MSSQL Overview

Information Needed Before Adding

Information Description Default Example
Name Display name given to the data source inside Pirivision — MES SQL Server
Description Purpose of the data source — MSSQL type DB for MES Data
Database Name Name of the database to connect to — mes_production
Host IP address or domain of the SQL Server — pirivision.digitheta.dev
Port SQL Server connection port 1433 14330, 1500
Username Database username (SQL auth) — postgres
Password User's password — postgres

Default SQL Server Port

The default port for SQL Server is 1433. Named instances (SERVER\INSTANCE) usually use a dynamic port; in that case you must fix the TCP port via SQL Server Configuration Manager and enter that port.

Authentication Mode

The Pirivision MSSQL connection works in SQL Server Authentication mode (username + password). Windows Authentication (Integrated Security) is not supported in this version.

1. Open the Port Module

Click Port in the left menu. Port is the module where data sources are added, listed, edited, and tested in Pirivision.

Port Root Directory

If no data source has been added before, the screen shows a Connect Your Factory Data message.

To add a data source from this screen you can use one of:

  • Add New Data Sources
  • Add Your First Data Source

2. Start the Add New Data Source Flow

Click the Add New Data Sources button at the top right.

If you are on the empty list screen, the Add Your First Data Source button in the middle starts the same flow.

3. Select the MSSQL Data Source Type

In the screen that opens, data source types are listed under Select Your Data Source Type.

To add MSSQL:

  1. Go to the Relational Databases (SQL) section.
  2. Pick the MSSQL card.
  3. Click Next at the top right.

MSSQL Data Source Selection

Info

For relational database connections in Pirivision, PostgreSQL, MySQL, and MSSQL options are available.

4. Fill in the MSSQL Form

After MSSQL is selected, the Add a New MSSQL Data Source screen opens.

The form consists of four main sections:

  1. Display Info
  2. Connection Settings
  3. Authentication
  4. Location

MSSQL Data Source Add Form

Difference from PostgreSQL

The Schema field present in the PostgreSQL form is not present in the MSSQL form. In SQL Server, schema (e.g. dbo) is specified by prefixing it to the table name in the query (dbo.production_logs).

5. Display Info

Display Info determines how the data source appears in the Pirivision UI.

Field Required Description
Name Yes The name used to identify the data source within Pirivision.
Description No A short note explaining what the data source is for.

Name

Used to identify this data source on panels and lists.

Good examples:

MES Production SQL Server
Plant2 SCADA MSSQL
ERP Sales DB
Quality Control SQL

Bad examples:

test
db1
mssql
new source

Naming Recommendation

Combine plant, system, and data type info in the data source name. For example: Plant2_MES_MSSQL.

Description

An optional field that explains what this connection is for.

Example:

MSSQL data source containing production orders, shift reports, and calibration results.

6. Connection Settings

Connection Settings contains the technical info required to connect to the SQL Server.

Field Required Description Default Example
Database Name Yes Name of the database to connect to. — mes_production
Host Yes IP address or domain of the SQL Server. — pirivision.digitheta.dev
Port Yes Port the SQL Server is listening on. 1433 14330, 1500

Database Name

The full name of the database to connect to on the server.

Examples:

mes_production
factory_erp
scada_history
quality_db

Note

SQL Server database names are not case-sensitive (default collation), but a typo causes the connection to fail. Copy the name verbatim from Object Explorer → Databases in SSMS.

Host

The network address of the server where SQL Server runs.

Examples:

localhost
127.0.0.1
192.168.1.105
db.company.local
pirivision.digitheta.dev

Warning

The Pirivision server must be able to reach the SQL Server host over the network. Just because you can connect with SSMS from your computer does not mean the Pirivision server can. Check firewall, security group, and port-forwarding settings.

Named Instance

Named instances in SERVERNAME\INSTANCENAME format cannot be entered directly via this form. Fix the TCP port in SQL Server Configuration Manager and enter only the server name/IP as Host, and the fixed port as Port.

Port

The SQL Server connection port.

Default value:

1433

Custom installation examples:

1434
14330
1500

Is TCP/IP Enabled?

In SQL Server installations, the TCP/IP protocol may be disabled by default. In SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for ..., TCP/IP must be Enabled and the service restarted.

7. Authentication

Database user info is entered in the Authentication section.

Field Required Description Example
Username Yes SQL Server username (SQL Authentication) postgres
Password Yes The user's password postgres

Username

The SQL login name authorized to connect to the database and run queries.

Examples:

postgres
readonly_user
dashboard_user
pirivision_user

Permission Recommendation

For dashboard reads, it is safer to use a separate user/login with read-only access to only the necessary tables (e.g. pirivision_readonly).

Windows Authentication

This version supports only SQL Server Authentication (mixed mode). If the server is configured only as "Windows Authentication mode", switch it to mixed mode in SSMS and restart the service.

Password

The password for this user. The password field is hidden. It can be temporarily revealed via the eye icon if needed.

Security

Do not display the database password in plain text in documents, screenshots, or shareable media. Use a separate login/user for Pirivision.

8. Location

Location determines where the created data source is stored in the Pirivision folder structure.

Field Description Example
Target Folder Folder where the data source is saved PORT

When you click the Target Folder field, the folder selection dialog opens:

Location — Target Folder field

Target Folder selection dialog

Example folder layout:

PORT/
├── Plant 1/
│   ├── SCADA/
│   ├── MES/
│   └── Quality/
├── Plant 2/
│   ├── SCADA/
│   └── Energy/
└── Test Sources/

9. Save the Connection With Test & Save

After all required fields are filled in, click the Test & Save button at the bottom right.

This works in two stages:

  1. The SQL Server connection is tested.
  2. If the test succeeds, the data source is saved automatically.

The screen shows:

Connection will be saved automatically if the test is successful

Success

If the test is successful, the MSSQL data source is listed on the Port screen and becomes available for query creation in Compass.

Failure

If the test fails, check host, port, database name, username, password, and network access info.

10. Verify the Data Source in the List

After a successful save, returning to the Port main screen shows the created MSSQL data source in the list.

MSSQL Data Source in the List

The list card shows the following info and actions:

Field / Action Description
Data source icon Indicates this is an MSSQL data source.
Name Data source name.
Description Description text.
Usage status In Use or Not in Use info.
Edit Updates connection info. → Edit Data Source
More Opens the additional actions menu.
↳ Move Moves the data source to a different folder. → Move Data Source
↳ Duplicate Creates a new copy with the same settings. → Duplicate Data Source
↳ Delete Removes the data source. → Delete Data Source

Delete Operation

The delete operation may not be reversible. If the data source is used by a query, chart, or dashboard, check dependencies before deleting. The backend blocks deletion if it is used in this user's Compass queries (you do not have permission to delete this datasource or a usage check error).

If the data source is not yet used in Compass or a dashboard, the status typically reads:

Not in Use

11. Common Errors

Error / Symptom Possible Cause Solution
Cannot connect Host wrong or unreachable Check IP/domain info and network access.
Timeout Server unreachable or firewall blocked Check firewall, VPN, security group, and network settings.
Authentication failed Wrong username or password Verify username/password info.
Database does not exist Wrong database name Check the actual database name on SQL Server.
Permission denied The user lacks privileges Grant the user the required read permissions.
TLS error Server requires TLS or certificate validation fails Check SQL Server TLS/certificate configuration.
Will not save Test failed The connection is not saved unless the test succeeds.

12. Next Step

After the MSSQL data source is created in the Port module, the process continues with Compass, Cartography, Horizon, and Atlas.

flowchart LR
    A["Port<br/>MSSQL Data Source"] --> B["Compass<br/>T-SQL Query"]
    B --> C["Cartography<br/>Chart / KPI"]
    C --> D["Horizon<br/>Page / Board Preparation"]
    D --> E["Atlas<br/>Dashboard View"]

Summary

The MSSQL data source addition process:

  1. Open the Port module.
  2. Click the Add New Data Sources button.
  3. Pick MSSQL as the data source type.
  4. Fill in the Display Info fields.
  5. Fill in the Connection Settings fields (Database Name, Host, Port — no Schema).
  6. Enter the Authentication info (SQL Authentication).
  7. Choose the Target Folder.
  8. Use Test & Save to test and save the connection.
  9. Verify the data source in the Port list.
  10. Create a T-SQL query in the Compass module.
  11. Create a chart / KPI in the Cartography module.
  12. Prepare a page or board in Horizon.
  13. Build the dashboard view in Atlas.