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 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.
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.
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.
Secure SQL Queries is implemented in the Operation, Monitoring, and Analytics section. Since most of our customers use this feature widely.
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.
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.
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.
We are constantly improving on the features based on feedback in the customer forum. BizTalk360 will continue to provide more useful features in every release. Why not give BizTalk360 a try!
