Prerequisites

1. Request a license file from Ninox for your Private Cloud or On-Premises that is granted full access for one of these Enterprise features: mssqlConnections or pgConnections.

2. Install the license file on your Private Cloud or On-Premises.

3. Get the following environment variables from your SQL Server:

   1. Server name: MSSQL_SERVER, e.g., mssqltest1.database.windows.net

   2. Database name: DB_NAME, e.g., mssql-test.ninoxdb.de

   3. Server admin: MSSQL_SA, e.g., nx-testsqlserver-admin

   4. Password: MSSQL_SA_PASSWORD

Create a new SQL connection in your Private Cloud or On-Premises

1. Log in to your Private Cloud or On-Premises.

3. Click the SQL Connections tab, then click Create New Connection.

4. Under SQL Connection, paste the configuration details from #prerequisites into the fields below:

   1. Name: enter a new name for this connection

   2. Connection Type: select MS SQL Server from the dropdown menu

   3. Active: tick the checkbox to set to true

   4. Server: see Server name in #prerequisites

   5. Database: e.g., mssql-test.ninoxdb.de

   6. User: see Server admin in #prerequisites

   7. Password: see Password in #prerequisites

   8. Encrypted: tick the checkbox to set to true

5. Click the Save and Restart button.

Validate your SQL connection with 2 scripts

Sample database

To validate the SQL connection, we are using a sample database that contains 1 table, 1 sub-table, and 3 fields:

• SQL Connections: a table

  • Connection name: a text field to enter the name of the SQL connection

• SQL Tests: a sub-table

  • SQL Query or Command: a text field to enter an SQL query or command

  • Result or Error: a multiline text field to receive the result of the SQL query or command

sendCommand

Add a button, then add the following script that is triggered on click:

do as server
	'Result or Error' := formatJSON(sendCommand('SQL Connections'.'Connection name', 'SQL Query or Command'))
end

In the sub-table SQL Tests, add a new record to run the following command in the SQL Query or Command field (1):

SELECT 1

Click the sendCommand button (2). The following result appears in the Result or Error field (3):

{"result":{"recordsets":[[{"":1}]],"recordset":[{"":1}],"output":{},"rowsAffected":[1]}}

queryConnection

Add a button, then add the following script that is triggered on click:

do as server
	'Result or Error' := formatJSON(queryConnection('SQL Connections'.'Connection name', 'SQL Query or Command'))
end

In the sub-table SQL Tests, add a new record to run the following command in the SQL Query or Command field (1):

SELECT 1

Click the queryConnection button (2) to return actual record data. The following result appears in the Result or Error field (3):

{"result":[{"":1}]}

Availability

How SQL Server Connectors work in relation to Ninox

Notes on implementation

• Some Ninox functions may change in future versions.

• Current Ninox functions for SQL only work on the server side.

• SQL commands with large data transfers may fail.

  • Data streaming is currently not available in Ninox.

• We recommend implementing security options on the SQL Server side.

  • Any user can run any SQL command in Ninox.

• We recommend setting up the login on the SQL Server side.

  • A dedicated log for SQL Server Connectors is currently not available in Ninox.

Supported use cases

SQL Server Connectors are particularly helpful in cases such as:

• You manage your production data in an SQL database and your CRM data in Ninox. For some reports you need to fetch a fitting, complex product ID from the production database.

• You manage your CRM data with Microsoft Dynamics Navision (which is based on an SQL database). You need specific data for your Ninox application. Instead of manually exporting .csv files and importing them to Ninox, you'd like transfer these files automatically.