Much has been written about the European Union’s (EU) General Data Protection Regulation (GDPR), which came into effect last May 28, 2018. the GDPR applies no matter where you are located, and with many organizations storing critical data in their database platforms, it will be incumbent upon them to ensure that the entire IT environment complies with the legislation, to avoid fines or penalties of up to €20 million or 4% of annual worldwide turnover.
Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers), stored in Azure SQL Databases or on-premises SQL Server databases.
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but cannot view it). By ensuring on-premises database administrators, cloud database operators, or other high-privileged (but unauthorized) users, cannot access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.
This whitepaper will give you a detailed understanding of the following:
Creating SQL Server Database in BizTalk 2020
Creating always encrypted column in BizTalk Server
Querying and inserting data in the encrypted columns
Where I can download it
You can download the whitepaper here:
You can also find, download and enjoy several other free whitepapers of my own here:
Finally, my latest whitepaper has been published: “BizTalk Server and GDPR“! After doing several GDPR assessments and talked a little about this topic on INTEGRATE 2018 London event I thought this should be the next logical step considering that this is a topic that has worried a lot of BizTalk Server customers, and still worries, and in a more general way all organizations regardless of whether they use or not BizTalk Server.
In a general way, BizTalk Server is GDPR compliant – in fact Microsoft Product group announced in INTEGRATE2018 London Event that one of the features of BizTalk Server 2016 Feature Pack 3 (FP3) was FIPS and GDPR privacy standards compliant, but that was just a marketing strategy since, in its most simplistic form, BizTalk Server is a messaging broker, which doesn’t capture or store any type of data on its system other than for the time needed to complete business processes and to connect and route messages to their target systems. However, because you can process messages and/or communicate with systems that contain sensitive data (personal data) you must have some good practices in BizTalk Server Applications to comply with GDPR. So, in other words, BizTalk Server is GDPR compliant but your BizTalk Applications may not be.
What to expect about BizTalk Server and GDPR whitepaper
BizTalk Server is part of complex enterprise IT environment, we also need to guarantee that these BizTalk Server environments are GDPR compliant. This white paper intends to provide key points on how you can archive and deploy secure integrations in BizTalk Server, and how you can ensure and/or help organizations for this complex middle piece, become GDPR compliant.
This whitepaper will cover the impact of the new regulations and what needs to be done with a focus exclusively on BizTalk Server environments.
Where I can download it
The whitepaper is completely free and you can download it here:
I would like to take this opportunity also to say thanks to Lex Hegt for helping me reviewing this whitepaper especially for the incredible work of him correcting my horrible English and in this way make the document and my thoughts being readable.
I hope you enjoy reading this paper and any comments or suggestions are welcome.
You can also find, download and enjoy several other free whitepapers of my own here:
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira
One more year and one more BizTalk Server session delivered in London: “BizTalk Server: Lessons from the Road“. INTEGRATE 2018 was an amazing conference and for that, we once again need to thank you BizTalk360 team and Saravana Kumar for being able to assemble not only another great event but keeping improve it each year which is not an easy task.
About my session: “BizTalk Server: Lessons from the Road”
I was asked, once again, by the event’s organizers to deliver a session about BizTalk Server, I topic that I love and like to talk but I must confess that this time I was a bit afraid. Because of the huge success of my previous session that I presented last year, it would not be easy to keep me on the same level. But life is full of challenges that we should not be afraid, but rather face them with frontality and confidence, so I decide to deliver a session about “BizTalk Server: Lessons from the Road“.
Abstract: The session will cover small pieces of stories with practical real examples from the field to address certain scenarios/requirements. See real techniques been used is some of the most important features of BizTalk Server, some of them are out-of-the-box capabilities others are custom extensions been made in the platform. Topics include BizTalk migration strategy, content-based routing techniques, Mapping, JSON support, BizTalk administration tips, extending BizTalk out-of-the-box capabilities and many more.
Hope you enjoy! But that is not all, you can also see the rest of the amazing sessions delivered by Microsoft Product group and Microsoft Most Valuable Professionals (MVP’s) at the event also online here: https://www.biztalk360.com/integrate-2018-resources/
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira
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.
GDPR considerations
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.
Author: Sandro Pereira
Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. View all posts by Sandro Pereira
If you’re based in the European Union, your email inbox is probably jam-packed with Updated Privacy Policy emails at the moment. And for good reason.
Every business you subscribe to is currently coming into line with new laws based on the GDPR. The deadline for the GDPR is 25 May 2018, and stricter rules are being enforced with regards to handling data for citizens based in the European Union.
The result of non-compliance? Hefty fines.
In this post, we want to share with you how the GDPR is affecting the way we do business with you – our customers.
GDPR rundown
The GDPR (General Data Protection Regulation) is a new EU regulation which aims to give citizens control back over their personal data. The aim is to make companies more transparent and responsible in how they handle their customers’ personal data.
It applies to any data that can be used to personally identify you – such as date of birth, location or IP address.
It also seeks to simplify the regulatory environment for international businesses – by harmonizing the regulation that already exists for countries within the EU. It’s a big change now for future benefit.
That’s why even non-EU companies are coming into line with the GDPR.
Your privacy
Kovai Limited is relatively small in the grand scheme of things, and we’ve got a personal relationship with all of our customers. It’s a relationship we respect and cherish dearly.
We’re not an advertiser, and we don’t sell or share any of our data for profit. Your privacy has always been a big deal to us.
That’s why, even before the GDPR, we’ve always:
Maintained a detailed privacy policy
Protected your data, never sharing it in any way
Stored your data securely
Only handled customer data for administrative or product purposes
Used “double opt-in” to make sure you want to get emails from us
Included a clearly labeled unsubscribe link in all of our emails to you
Even if their policies have been pro-privacy in the past, all businesses still need to tighten their processes in light of the GDPR.
That’s why we’ve been working closely with our advisorsHappiest Mindsto ensure we are totally compliant with the new rules.
What we’ve changed
We considered a number of partners in our GDPR journey. We chose Happiest Minds because they align with our values, and specialize in the regulatory field.
Over the course of about three months, they audited our existing policies and procedures to ensure we are compliant with the GDPR. Based on their recommendations, we made a few small changes.
Each product website we own has been updated with a new privacy policy.
Every staff member undertook retraining on how to handle confidential customer data and how to collect information on “need” basis.
Every data processing staff member has been made aware of the regulatory requirements and how to comply.
Amassing a digital data footprint is an almost unavoidable part of modern life.
Collecting and processing data underpins nearly every aspect of the business and requires new laws that ensure citizens can retain control over their own data. The GDPR is not exactly brand new, but an update of previous privacy laws enshrined before the latest digital revolution.
It’s not just about privacy, but empowering citizens to take ownership of their data and how it is used. It’s about limiting some of the powers that some businesses have previously exercised over data without restraint – in the pursuit of profit.
We were happy to make the necessary changes to ensure compliance.
What we haven’t changed is how we always put our customers first. Everything we do with your data is in service of you.
