Secure SQL Queries
  • 21 Feb 2024
  • 4 Minutes to read
  • Dark
    Light
  • PDF

Secure SQL Queries

  • Dark
    Light
  • PDF

Article summary

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 a query 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 and Stored Procedures. No need for SQL Server Management Studio
  • Central Query Repository - maintaining queries is much easier
  • The end-users don't need to have direct access to SQL Server databases. 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 that can be accessed by the BizTalk360 service account

Executing pre-stored queries

To execute the pre-stored queries, follow the steps as shown below in the 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.

  1. Log in to the BizTalk360 application
  2. Select the environment from the environmental panel
  3. Click the Administration tab in the panel
  4. Click the expand button next to the Advanced Services tab and select the Secure SQL Queries link
  5. 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
  6. 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 to pre-loaded queries that come as 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 number of records of the Spool table from the MessageBox database. Follow the steps as shown below to add a new query:

  1. Log in to the BizTalk360 application
  2. Select the environment from the environmental panel
  3. Click the Administration tab in the Navigation panel
  4. Click the expand button next to the Advanced Services tab and select the Secure SQL Queries link 
  5. In the Secure SQL Queries screen, click the '+' icon. This will open the Secure SQL Query query page
  6. Type the query in the space provided and select 'Run Query'
    1. SQL Instance - Enter the name of the SQL instance 
    2. Database - Enter the name of the message box database (In this case, BizTalkDTADb)
    3. Authentication Mode -Select the authentication type. Windows , SQL Server and Azure AD are the supported Authentication type .(Windows Authentication is selected as default) 
    4. Query results are displayed in the pane 
  7. Select the Save option and type the query name 
  8. Select Save
Authentication mode 
1.Windows Authentication - BizTalk360 connects to the SQL instance with a Windows account
2.SQL Authentication - BizTalk360 Connects to the SQL instance using the username and password provided by the user.  Users can also connect to the Azure database using SQL authentication
3.Azure Active Directory Authentication-BizTalk360 Connects to the Azure SQL servers using Azure Active Directory password/ Azure Active Directory service principal /Azure Active Directory Access Token based on the user selection.


Proxy:

For Azure AD authentication proxy will work based on the below configuration:

user need to add proxy tag in the web config file in both Monitoring and analytics web config file.

<span style="box-sizing: border-box; color: rgb(36, 36, 36);"><span style="box-sizing: border-box; color: rgb(17, 119, 0);"><</span><span style="box-sizing: border-box; color: rgb(17, 119, 0);">system.net</span><span style="box-sizing: border-box; color: rgb(17, 119, 0);">></span><br style="box-sizing: border-box;"></span><span style="box-sizing: border-box; color: rgb(36, 36, 36);">    <span style="box-sizing: border-box; color: rgb(17, 119, 0);"><</span><span style="box-sizing: border-box; color: rgb(17, 119, 0);">defaultProxy</span> <span style="box-sizing: border-box; color: rgb(0, 0, 204);">useDefaultCredentials</span>=<span style="box-sizing: border-box; color: rgb(42, 0, 255);">"true"</span> <span style="box-sizing: border-box; color: rgb(17, 119, 0);">/></span><br style="box-sizing: border-box;"></span><span style="box-sizing: border-box; color: rgb(36, 36, 36);"><span style="box-sizing: border-box; color: rgb(17, 119, 0);"></</span><span style="box-sizing: border-box; color: rgb(17, 119, 0);">system.net</span><span style="box-sizing: border-box; color: rgb(17, 119, 0);">></span></span>


You can modify the query at any time according to your requirements. To edit a query:

  1. In the Secure SQL Queries screen, click the Save as Query icon. This will pop open the Secure SQL Query Details page
  2. In the Secure SQL Query Details page, you can modify the value as per the requirement
  3. 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:

  1. In the Secure SQL Queries screen, click the Open Saved Search Query icon

  2. Select Saved Queries and hit the queries for which you want to delete

  3. Select Delete 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 Delete to delete the secure SQL query

  4. The query will no longer be available in the drop-down



Was this article helpful?