Overview of Secure SQL Queries
BizTalk360 offers the Secure SQL Queries functionality as 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.
BizTalk360 comes with a pre-loaded set of queries and allows users to define secure SQL 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.
The following are the advantages of Secure SQL queries functionality in BizTalk360:
- 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 service account, therefore only the service account requires access
- Queries can be executed against any SQL instance/database
Executing Prestored queries
To execute the pre-stored queries, follow the steps as shown below in exact order. Let us consider an example to execute a query to retrieve the list of 'All Disabled Receive Locations'. The query will look into the BizTalkMgmtDb and retrieve the information of disabled receive locations.
Log into the BizTalk360 application
Click the 'Operations' tab in the Navigation panel
Click the expand button next to 'Data Access' tab and select 'Secure SQL Queries' link
In the Secure SQL Queries screen, under Build Query Expression:
- Select the pre-stored query "Sendport to Specific Location [BT360DOC01\BT360DOC01:BizTalkMgmtDb]" from the drop-down
- The query will execute and the results will be displayed in the Query Results pane
Once the query is executed, the tab name will be renamed to the name of the query (Sendport to Specific Location).
Secure SQL Query Management
In addition pre-loaded queries that come as an out-of-the-box, BizTalk360 allows you to manage the existing SQL queries and add/edit/delete your own queries.
Let's take an example of adding a new query that will fetch the Spool Size of the BizTalkMgmtDb. This will involve a SELECT statement that will retrieve the value of 'SpoolSize' from the message box database. Follow the steps as shown below to add a new query:
Log into the BizTalk360 application
Click 'Operations' tab in the Navigation panel
Click the expand button next to the 'Data Access' tab and select 'Secure SQL Queries' link
In the Secure SQL Queries screen, click the 'New Query' icon. This will pop open the Secure SQL Query Details page.
In the Secure SQL Query Details page:
- Friendly Name - Enter a friendly name for the Secure SQL query that can be easily understood by users Ex. Calculate Spool Size
- SQL Instance - Enter the name of the SQL instance (In this case, BT360DOC01)
- Database - Enter the name of the message box database (In this case, BizTalkMgmtDb)
- Enter the SQL Query - Type the query into the space provided
Click 'Save' to save the query
You can modify the query information any time according to your requirements. To edit a query:
- In the Secure SQL Queries screen, click the 'Edit' Query icon. This will pop open the Secure SQL Query Details page.
- In the Secure SQL Query Details page, you can modify the value as per the requirement
- Click 'Save' to save the query
You cannot delete the preloaded queries from the drop-down list. You can only delete secure SQL queries entered by you or other users in the system. To delete a query:
In the Secure SQL Queries screen, click the 'Delete' Query icon. This will pop open the "Confirm Delete" message box with the message as "Are you sure, you want to delete this query? This query may be used by other users as well". Click 'Yes' to delete the secure SQL query
The query will no longer be available in the drop down