On May 25, 2018, European privacy law is going to enforce new global standards for privacy rights, security, and compliance. This new regulation is known as GDPR – General Data Protection Regulations. Lots of users who are using SQL Server often wonder How to make SQL Server GDPR compliance? Before you read this blog post, I suggest you read my earlier blog post about here which explains everything about GDPR. This blog post we will take about Authentication in SQL Server (Windows and Mixed Mode) which is for GDPR Article 25.
GDPR Article 25 – Data protection by Design and Default
Here are you are responsible for controlling who has access to your personal data and how the data is processed, stored and accessed in the future. This also requires implementing various safeguards to manage the stored data.
One of the ways to achieve this is to implement Authentical in SQL Server. Let us learn about that today.
Authentication in SQL Server
With the help of Authentication, we can ensure that only users with proper credentials can access the database and its services. SQL Server has two different kinds of authentication modes a) Windows Authentication and b) Mixed Mode.
Windows Authentication is recommended over Mixed Mode because it provides it allows only authenticated Windows user to access SQL Server. This adds an additional layer of security as well as it also extends its services to Azure Active Directory authentication. With the help of Windows Authentication, we can enable Kerberos security protocols as well as use built-in password policy enforcement.
Additionally, do not forget to create separate accounts for users with different role and purpose. It is never a good idea to create a single user login and use it for all the customers. Though I have seen a very little adoption of the Contained Database feature, it would be a good idea to explore it to isolate user account to a specific database.
Simple words: Windows Authentication is the best option to manage users for SQL Server via Active Directory. When the permission of user changes (leaves organizations or gets promoted), they can be easily managed via roles mapped to user groups.
Reference: Pinal Dave (https://blog.sqlauthority.com)