How to add an SQL provider
What are Integrations?
Dashboard Server Integrations allow you to create your own providers. Each Integration has it's own form to help you input the information required for different providers.
What is a provider?
Providers contain the connection details to external platforms. A provider only needs to be set up once and can then be used when creating tiles on a dashboard.
There are two types of integrations and therefore providers:
generic Web API providers that can connect to any REST API
dedicated providers that connect to a specific external platform or database (SQL, ServiceNow, Azure Active App Insights, Elasticsearch, etc.)
About SQL providers
An SQL provider contains the connection string to the SQL database you want to connect to. You can use Microsoft SQL Server or ODBC drivers for your connection.
Since Dashboard Server version 5.4 you need an SQL provider to use the SQL tile. .
If you created SQL tiles before Dashboard Server version 5.4, they were created with a connection string in the tile instead of a provider.
As soon as you update to v5.4 those connection strings will automatically be converted into providers. If identical connection strings are detected, they'll be merged into one provider.
The new SQL providers will be named in the following format: servername.databasename (if server and database name can't be identified they'll be named SQL provider 1, SQL provider 2, etc.)
Adding an SQL provider
Make sure you have access to the database you want to use.Configuring access to a database for an SQL provider
To be able to query databases, such as the Operations Manager database, the Dashboard Server application pool identity needs to be given the db_datareader role in SQL Server Management Studio for the database you wish to query. See How to configure access to a database for use with the SQL tile
Enter a name for your provider that makes it easy for you to identify. You can't change the name after you created the provider.
Driver and Connection String:
The connection string depends on your database and the driver you've chosen:Connection strings when you are using Microsoft SQL Server Driver
Connection string for any database:
With integrated security:Copy
With username and password:Copy
If the SquaredUp server and SQL server are in the same domain you can just use the SQL server name, if they are not in the same domain you should use the fully qualified domain name.
If you want to specify an instanceCopy
If you want to specify a portCopy
Server=SQLServer1,51433;Database=CRMDB;Integrated Security=True;Connection strings when you are using ODBC
Note: You need to use 64-bit ODBC drivers.
Note: When an SQL tile uses an ODBC provider, you can't use mustache values for your queries in that tile.
If you are using an ODBC driver
After you've set up your ODBC driver in Windows on the SquaredUp server, add the following at to your connection string:
Driver=<Name of your ODBC driver>;
If you are using a System DSN
Note: You need to use a System DSN, not a User DSN.
After you've set up your System DSN in Windows on the SquaredUp server, enter the following instead of a connection string:
DSN=<Name of your System DSN>;Copy
Click save to save your new provider.