Effective Usage of Secure SQL Queries Feature in BizTalk360

|  Posted: December 23, 2019  |  Categories: BizTalk360

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.

secure-sql-queries

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.

sql-authentication

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!

Performance impact

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

Conclusion

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!

Back to Top