We all know that the WCF-SQL adapter enables BizTalk Server to perform composite operations on any SQL Server database. A composite operation can include any number of the following operations, and in any order:
- The Insert, Update and Delete operations on the tables and views
- Stored procedures that are surfaced as operations in the adapter
It can also execute Transact-SQL and CLR:
- Stored procedures in an SQL Server database
- Scalar and table-valued functions in an SQL Server database. And so on,
In resume operations at the Tables, Views, Procedures, Scalar Functions, and Table-Valued Functions, levels will be supported.
Stored Procedure permissions
I personally like to use Stored Procedures instead of directly accessing the tables which are available in the database.
Regarding the required access permission in SQL Server for BizTalk Server, to connect to a particular database to extract or store data, or in this case, be able to call stored procedures, what teams normally do is creating:
- A new SQL user with “db_owner” privileges
- Or they give “db_owner” privileges to the service account that is running the BizTalk Server host instance, for example, “BTSHostSrvs” (BizTalk Host Instance Account)
Why? Because this is simple and quick, and they don’t need to worry about lack of permissions or the proper permissions.
But sometimes these tables contain sensitive data or personal data, and nowadays with General Data Protection Regulation (GDPR) in the European Union (EU), this sometimes can be a backdoor for other possible problems. Teams need to start thinking in concepts like “Privacy by Design” and “Privacy by Default” for their solutions:
- “Privacy by Designs” holds that organizations need to consider privacy at the initial design stages and throughout the complete development process of new products, processes or services that involve processing personal data
- “Privacy by default” means that when a system or service includes choices for the individual on how much personal data he/she shares with others, the default settings should be the most privacy-friendly ones
So, companies should be more careful and more strict in:
- Who has access to what?
- Limit the number of persons that can access that information to the strictly essential persons
- Define a better access granularity and restrict access, once again, to the essential tasks
- A service account that consumes or store new data shouldn’t be a database owner or a sysadmin.
Secure Stored Procedure permissions
Of course, giving “sysadmin” or “db_owner” would solve all our problems but it goes against security best practices.
One way, or -personally- the best way, for you to properly define a better access granularity and restrict access to the essential tasks or in other words, the essential stored procedures, is to create a new server role, for that particular database, in SQL Server. Follow below steps to create such a server role:
- Open SQL Server Management Studio and connect to your SQL server
- In the Object Explorer, access to your database and expand it
- Expand the Security folder
- Right-click the “Database Roles” folder and select “New Database Role…”
- In the “New Database Role” window
- On the “Role name” property, on the General page, enter a name for the new database role, for example, “db_spexecution”
- At the Securables page, under Securables, click the “Search” button
- On “Add Objects” window, select “Specific objects…” and click “OK”
- On “Select Objects” windows, click “Object Types…” and then select “Stored Procedures”
- After selecting the object type, click “Browse…” and from the “Browser for Objects” window, select the stored procedures you want to invoke(only the one that you need)
- Click “Ok” and again “OK” to return to the main “New Database Role” window
- The last step, on the Securables page, is to give Execute permissions “Grant” and “Grant with”
- Finally, on the General tab, add the service account that is running the host instance to the Role Members for that role
- Click “OK” to finish
It gives you more work, that is for sure, but now you will have a properly access granularity defined, with the minimum rights defined for the actually necessary tasks. Nothing more, nothing less… as things should be.