This post talks about using Secure SQL Queries in BizTalk360. To demonstrate I walk though a scenario that shows how to expose BizTalk data to users safely. Moreover I will explain how the BizTalk360 security model helps us to grant access to only some users.
The Problem
Our BizTalk exchange uses a custom database for mapping and our internal users want to read and update these mappings. For example consider the mapping of external customer account codes to internal codes as shown below.
I don’t like this pattern but this is not the purpose of this article. On the other hand if you want to read a discussion about a better ways to do this, look here.
Although this data does not change very often, if a new café or warehouse is built then someone has to ring the SQL Administrators to get a new row added. Furthermore to get a listing of what the existing mappings are another call is required. We want to liberate our users so they can serve themselves.
A Solution – BizTalk360 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.
Let’s see how we can use this to expose BizTalk data securely. Firstly open Secure SQL queries on the Operation tab. Secondly add a friendly like “Top 1000 Account Code Mapping”. Thirdly add the SQL Instance name. Fourthly add the Database. Finally add the query “SELECT TOP (1000)[ExternalAccountCode],[InternalAccountCode],[Description] FROM [BTHub].[dbo].[AccountCodeMapping]” and select save.
Now a our query appears in the “Select a Query dropdown”. Selecting that the query runs and display the results in the bottom. That was easy.
Furthermore creating search for a specific row allows the user to edit the query and then execute query to look for particular entry. This is quite powerful for the internal users because they often don’t know whether a particular account code is mapped or not.
Restricting User Access and Action
We will show how BizTalk360 can restrict which users can run the query and only allow them to edit and execute queries but not save them.
Open the BizTalk360 user access policy under settings. Select edit on a user and then select next and next again until you get to this screen.
Click on Advanced after the Secure SQL Queries option. On the following page select Can Access: Execute and Edit only. In addition only grant access to the canned queries that you want that user to run. Thus we don’t tick the UK queries because this user is not a UK user. The use when he logs on only sees his queries and not anyone else’s.
Conclusion
In summary BizTalk360 empowers our users to read BizTalk mapping data in a safe and secure way. We give them canned queries that run well and only give them access to the queries that they need. I think this is a hidden gem in the BizTalk360 application.
