In this blog, we will take a detail look about the feature Secure SQL Queries which is available in BizTalk360 and we shall look how effectively it can be used in by the BizTalkies and what are all the things we can achieve by having this feature and how we made this feature so reliable for you.
Secure SQL Queries
Secure SQL Queries is a secure platform to store predefined queries and provide access to BizTalk members to execute the queries. BizTalk360, by default, comes pre-loaded with a set of queries and allows the users (with permissions) to build secure SQL queries that are more appropriate to the organization’s business.
Reliability of Secure SQL Queries
BizTalk Support members will typically have a bunch of standard SQL queries they run on a regular basis to get some reporting on BizTalk databases. Each individual team member will have their own set of SQL queries and they manage it on their local machines. Which will basically result in two things, useful reporting queries are not shared between team members and maintaining the queries in SQL files and accessing different files for the different environment is not productive.
BizTalk360 allows you to save the SQL queries so that the queries can be saved under a centralized area and available for access to all the BizTalk members.
SQL queries are used for fetching the report from the BizTalk Database where admins are concerned to provide full access as it may have confidential information and interested to give privilege only for the authenticated BizTalk members. This can be handled by using the User Access Policy feature in BizTalk360 which allows to create a user and provide the permission for the saved Secure SQL Queries (only the enabled query will be listed for the users). Permission can be provided for the Secure SQL Queries as follows Add Query, Edit & Execute Query, Edit and Save query, Delete Query and Export Query.
At any point in time, there may be a chance of data loss or change may happen in the queries. To handle this sort of situation BizTalk360 provides you the feature Governance & Audit for Secure SQL Queries. If any operation like Create, Edit, Delete is performed against the Secure SQL Queries will be captured in the BizTalk360’s Governance & Audit section. So that we can find the details of the operation performed through the feature Governance & Audit for Secure SQL Queries.
Functionalities of Secure SQL Queries
In BizTalk360, you can query against the table’s data. Based on the customer’s requirement it allows you to store and execute the stored procedures.
In real-time when more users are involved, or remote connections are being established with the SQL Server, SQL authentication can be used. In shared servers where different users should have access to different databases and when a client connects to an instance of SQL Server on another computer than the one on which the client is running, in that case, SQL Server authentication is needed. Such user can select the Authentication Mode as SQL Authentication so that the user name and password can be provided, and the query can be saved and executed
Using the SQL Authentication Mode, you can get connected to the Azure Database by providing the Azure Database’s instance name, username, password for saving the queries.
Let’s glimpse on Secure SQL Queries in BizTalk360
Secure SQL Queries is implemented in the Operation, Monitoring, and Analytics section. Since most of our customers use this feature widely.
Secure SQL Queries in Operation
BizTalk360 comes with a pre-loaded set of queries. The users can choose one of these pre-loaded queries from the drop-down list. When the item is selected from the drop-down, the query automatically executes, and the result is displayed in the Query Results pane. It allows users to define and add/edit/delete your own queries. In which you can execute the query to check for the results.
Secure SQL Queries in Monitoring
You can monitor the Database Query results by configuring the query which returns the scalar value. If there is any deviation in the query results, then BizTalk360 will trigger an alert notification.
Secure SQL Queries in Analytics
The BizTalk360 Reporting feature includes a widget called Secure SQL Queries, where you can map the existing Secure SQL Queries to such custom widgets. The custom SQL widgets hold the top 100 records of the mapped query and send that as a report on the scheduled time. This feature can be powerful as it enables you to automatically receive reports based on SQL queries, without the need to requesting it manually over and over again!
BizTalk databases can grow extremely bigger. It’s not uncommon to have over 1 million records in certain tables (ex: MessageInOut table in Tracking Database). Executing some queries like just a plain “SELECT * ..” on those tables without any query optimization or locks can have serious performance impact during business hours. Always try to write the query which returns the selective results.
Secure SQL Queries are designed to allow the users to execute only the ‘Select’ statement for queries and Execute command for Stored procedures. This will ensure the reliability of the data in the BizTalk Server and other databases.
Say, for instance, there may be a chance that the normal/a superuser runs a DELETE/TRUNCATE/DROP/UPDATE/INSERT commands through the secure SQL queries in BizTalk360. The above-mentioned commands are restricted by design to avoid data loss and secure the data.
Benefits of having Secure SQL Queries
- Single management tool for users to execute the queries
- No need for SQL Server Management Studio
- Central Query Repository – maintaining queries is much easier
- The end-users need not have direct access to the SQL database
- The queries will be executed in the context of the service account, therefore only the service account requires access
- Queries can be executed against any SQL instance/database