Connecting to Databases
A Database Connection lets your reports read data from a SQL Server or PostgreSQL database, using the [Database.Xxx:] macros.
If the database cannot be reached from the internet, install a Magic Suite Agent on your network instead, and use the Agent SQL macros, such as [Agent.SqlValue:]. Please raise a support ticket if you need more guidance.
Before you start
- Allow our servers to reach the database: their public IP range is 46.249.212.128/27
- Have a database login that can read the data your reports need
Creating the Connection
- In Admin, open Connections, then click Create
- Choose Database as the type
- Enter a name and, optionally, a description. We recommend Default as the name of the first Connection of each type
- Fill in:
- URL: the database server's host name, such as db.example.com. For SQL Server on a port other than 1433, add the port after a comma, as db.example.com,14330
- User Name and Password: the database login. A PostgreSQL password may contain any characters, semicolons and quotes included (coming in version 4.7)
- Configuration: see Configuration below
- Click Test Connection, then Save
Configuration
The Configuration is JSON. Set only what you need: a setting you leave out takes its default.
| Setting | What it does | Default |
|---|---|---|
| Type | The database engine: Mssql for SQL Server, or Postgres for PostgreSQL. | Mssql |
| Name | The database to read from. On SQL Server, leave it out to use the login's default database. | None |
| Port | PostgreSQL only: the server's port. Ignored when the URL includes a port, as host:port. For SQL Server, put the port in the URL instead, as server,port. | 5432 |
| TimeoutMs | How long one attempt at a query may take, in milliseconds, when the macro does not set its own. | 20000 |
| ConnectTimeoutSeconds | SQL Server only: how long to wait to connect, in seconds. | 15 |
| Encrypt | SQL Server only: true requires encryption, false allows unencrypted traffic. | Required |
| TrustServerCertificate | SQL Server only: true accepts the server's certificate without checking it, for a server with a self-signed certificate. This is less secure. | false |
| SqlServerAuthenticationMethod | SQL Server only: how to sign in. Leave it out for a SQL Server login. For a Microsoft Entra app, use ActiveDirectoryServicePrincipal, with the app's client ID as the User Name and its secret as the Password. Microsoft's documentation lists the other values. | A SQL Server login |
For example, for SQL Server:
{
"Type": "Mssql",
"Name": "Sales"
}
And for PostgreSQL on port 5433:
{
"Type": "Postgres",
"Name": "sales",
"Port": 5433
}
Tip: Connections, Tools, Configure a Connection fills in the Configuration with a form, with help on every setting (coming in version 4.8).
Using it in reports
Choose the Connection with [Database.Connection:], then query it with [Database.Value:], [Database.Values:], [Database.List:], [Database.Table:] or [Database.Graph:]. For example:
[Database.Connection: name=Default]
[Database.Value: sql="SELECT COUNT(*) FROM Orders"]
More information
- Microsoft: ConnectTimeout
- Microsoft: Encrypt, and connecting with strict encryption
- Microsoft: TrustServerCertificate
- Microsoft: signing in to SQL Server with Microsoft Entra ID, including every SqlServerAuthenticationMethod value