Microsoft SQL plugin
This plugin allows for the connection from SquaredUp agent to any MSSQL instance which is available to connect to with SQL Login.
Click the following link for additional content such as blogs, videos, use cases, and more:
This plugin should be used whenever you want to connect to an MSSQL database, regardless of whether that database is hosted in a cloud environment such as Azure, AWS, GCP, etc.
Adding a data source
To add a data source click on the + next to Data Sources on the left-hand menu in SquaredUp. Search for the data source and click on it to open the Configure data source page.
Before you start
- Will SquaredUp need to connect to this data source via a relay agent?
- Do you need to whitelist SquaredUp traffic?
- Check the required user permissions
- Configure the data source in SquaredUp
Will SquaredUp need to connect to this data source via a relay agent?
This plugin is a hybrid plugin, meaning it can connect to either a cloud or an on-prem data source.
- If your data source is available on the internet, then you do not need to connect via a relay agent.
- If your data source is on a private network and is not publicly accessible, then you will need to configure a relay agent before you configure the plugin. See Deploying a relay agent.
This feature is available with an Enterprise plan
Relay agents allow you to securely connect to data sources inside your own network (on-prem).
A relay agent is installed on a server on your internal network, and has access to your data source.
When a plugin uses a relay agent it means that you don't need to open your firewall to allow SquaredUp access to the data source.
If you have already created a relay agent in SquaredUp that can access this data source, then you can skip this step and choose Connect via relay agent when Configuring the data source.
Do you need to whitelist SquaredUp traffic?
If your data source requires connections to be whitelisted, here are the IP addresses our platform use for each region:
- US:
52.20.146.185, 34.198.107.84 - Europe:
34.248.251.213, 54.73.229.191
Required user permissions
When configuring the data source, you must enter the Username and Password of a user with access to your database. This user must have the following permissions:
- Primary database:
VIEW SERVER STATE
- Secondary database:
CONNECT:SELECT:VIEW DEFINITION:
Configuring the data source
Display name:
Enter a name for your data source. This helps you to identify this data source in the list of your data sources.Connect via relay agent
If you are connecting to an on-prem data source then select this toggle, so you can use a relay agent to connect securely.
Agent group:
Select the Agent Group that contains the agent(s) you want to use. Agent groups are managed from Settings > Relay Agents- Username:
Enter the SQL or Windows login username. - Password:
Enter the SQL or Windows login password. - Server:
The server address or IP to connect to. Do not include a port with the SERVER value.If you are adding the MSSQL on-prem data source, the server must be accessible from the machine running the SquaredUp on-prem agent.
- Database:
The name of the database to initially connect to. Any queries executed without a database explicitly set will be run against this database. - Port:
The port to use to connect to SQL. The default is 1433, but can be changed as needed. - Encrypt connection:
If the destination server supports encryption then tick this to set it as required for the connection. This setting should be ticked when connecting to a managed Azure SQL Database. Ignore certificate errors:
If you activate this checkbox the data source will ignore certificate errors when accessing the server. This is useful if you have self-signed certificates.- Enable object indexing:
Select to enable object indexing, which creates objects in the Map used to create context sensitive objects and drill-downs. The following fields display:- Object query:
Enter a database query to return the objects you want to index.These plugins support the creation of objects from a database query. The resulting table must contain sourceId, name, and type fields. Additional fields are allowed, and any non-null fields will be included with the object when imported into the Knowledge Graph.
A sample query result is below:
In the above sample table two objects will be created in the Knowledge Graph. The first will be of type user and have a sourceId of abeLincoln and a single property for initials. The operating_system attribute will not be added to this object as it has a null property. The second object created will be a server with sourceId server_1 and a single property of operating_system.
- Relationship query:
If you are creating objects with the Object query set in the previous step, you can relate those objects in the Map.Along with an Object Query an additional Relationship Query can also be used. The result of this query must contain the fields inV, outV, and label. The rows of this result will be converted to relationships in the Knowledge Graph.
A sample query result is below:
After the import is complete the two objects created from the Object Query will now be linked, with Abraham Lincoln owning Infra-SQL01.local
- Object query:
Restrict access to this data source:
Optionally, enable this toggle if you only want certain users/groups to have access to the data source, or those with the permission to link it to new workspaces. See data source access control for more information.The term data source here really means data source instance. For example, a user may configure two instances of the AWS data source, one for their development environment and one for production. In that case, each data source instance has its own access control settings.
By default, Restrict access to this data source is set to off. The data source can be viewed, edited and administered by anyone. If you would like to control who has access to this data source, switch Restrict access to this data source to on.
Use the Restrict access to this data source dropdown to control who has access to the workspace:
- By default, the user setting the permissions for the data source will be given Full Control and the Everyone group will be given Link to workspace permissions.
- Tailor access to the data source, as required, by selecting individual users or user groups from the dropdown and giving them Link to workspace or Full Control permissions.
- If the user is not available from the dropdown, you are able to invite them to the data source by typing in their email address and then clicking Add. The new user will then receive an email inviting them to create an account on SquaredUp. Once the account has been created, they will gain access to the organization.
- At least one user or group must be given Full Control.
- Admin users can edit the configuration, modify the Access Control List (ACL) and delete the data source, regardless of the ACL chosen.
See Access control for more information.
Click Test and add to validate the data source configuration. SquaredUp will now attempt to connect to SquaredUp using the provided authentication method.
- Testing passed – a success message will be displayed and then the configuration will be saved.
- Testing passed with warnings – warnings will be listed and potential fixes suggested. You can still use the data source with warnings. Select Save with warnings if you believe that you can still use the data source as required with the warnings listed. Alternatively, address the issues listed and then select Rerun tests to validate the data source configuration again. If the validation now passes, click Save.
- Testing Failed – errors will be listed and potential fixes suggested. You cannot use the data source with errors. You are able to select Save with errors if you believe that a system outside of SquaredUp is causing the error that you need to fix. Alternatively, address the issues listed and then select Rerun tests to validate the data source configuration again. If the validation now passes, click Save.
You can edit data source configurations at any time from Settings > Data Sources.
Testing and troubleshooting
If you encounter an error refer to the guidance or contact our support team in-app or via SquaredUp Support
Next steps
Data streams
You can use these data streams to create new tiles to show data, or if there are preconfigured dashboards installed you can copy or edit those.
Data streams standardize data from all the different shapes and formats your tools use into a straightforward tabular format.
While creating a tile you can tweak data streams by grouping or aggregating specific columns.
Depending on the kind of data, SquaredUp will automatically suggest how to visualize the result, for example as a table or line graph.
Data streams can be either global or scoped:
- Global data streams are unscoped and return information of a general nature (e.g. "Get the current number of unused hosts").
- A scoped data stream gets information relevant to the specific set objects supplied in the tile scope (e.g. "Get the current session count for these hosts").
See Data Streams for more information.
The following data streams are installed with this plugin.
SQL Stored Procedure
Execute the specified Stored Procedure on the target database and return the results
Parameters