Implement A Secure Environment For A Database Service | Authentication | Authorization

In this blog, we are going to cover Implement a secure environment for a database service. This module investigates the acts of conceding authorizations and what the different consents do inside an information base.

Topics we’ll cover:

• Introduction 
• Active Directory And Azure Active Directory
• Authentication and Identities
• Security Principals

Introduction To Implement a Secure Environment For A Database Service

Azure SQL Database has authentication and authorization choices that are not quite the same as the choices in SQL Server. This is on the grounds that Azure SQL Database and Azure SQL Managed Instance depend on Azure Active Directory rather than Windows Server Active Directory.

This module investigates the acts of conceding authorizations and what the different consents do inside an information base. This module likewise investigates the idea of “least privilege”. While the inherent jobs in SQL Server and other databases give expansive brush of safety advantages, numerous applications need more granular security on database objects.

Active Directory And Azure Active Directory

Azure Active Directory is used to manage the identity and access management solution for the cloud using a single identity per user.

Active Directory is a Microsoft-managed windows server this is used to manage permissions and control access to network resources.

Authentication And Identities

• Both on-premises SQL Server establishments and SQL Server establishments inside Azure Virtual Machines support two methods of authentication: SQL Server authentication and Windows Authentication.
• When utilizing SQL Server authentication, SQL Server-explicit login name and secret passkey are put away inside SQL Server, either in the master database or on account of contained users, inside the client database. Utilizing Windows Authentication, clients to interface with the SQL Server utilizing a similar Active Directory account they use to sign into their PC.
• Active Directory authentication is viewed as safer in light of the fact that SQL Server authentication takes into consideration login data to be found in the plain content text while being passed across the network. Likewise, Active Directory authentication makes it simpler to oversee client turnover.
• On the off chance that a user leaves the organization and you use Windows authentication, the administrator would just need to lock the single Windows record of that client, rather than recognizing every event of a SQL login.

Security Principals

• Security Principals are entities that can request SQL Server resources and to which you can (usually) grant permissions. There are several sets of security principles in SQL Server. Security principals exist at either the server level or the database level and can be either individuals or collections.
• Some sets have a membership controlled by the SQL Server administrators, and some have a fixed membership.

1.) Schemas And Securable For Database

• Before we take a look at the details of safety principles, we need to understand the logic of securable and schemas. SQL Server and Azure SQL Database have three extensions for security. Secures are the resources inside the database to which the authorization framework manages access. For instance, a table is securable.
• To specify access control, SQL Server contains securable in settled progressive hierarchy systems called scopes. The three securable scopes are the database, the server, and the schema.
• A schema is a collection of objects within your database, which allows objects to be grouped into separate namespaces.

2.) Logins And Users For Database

Regardless of the method of authentication that is utilized, a login name used to get to your SQL data set will be set up as a login inside the example. Those logins are set up at the occasion level of SQL Server and put away in the master database. These users only have access to the database that the user is set up with. For the best purpose, we will create the user.

CREATE USER [dba@contoso.com] FROM EXTERNAL PROVIDER;

GO

If logins are created at the instance level in SQL Server, a user should then be created within the database, which maps the user to the server-based login as shown in the following example.

USE Master

GO

CREATE LOGIN demo WITH PASSWORD = 'Pa55.w.rd'

GO

USE WideWorldImporters

GO

CREATE USER demo FROM LOGIN demo

GO

3.) Database Roles

Inside a database, authorizations can be conceded to the users that exist inside the database. On the off chance that different users all need similar authorizations, you can make a database job inside the database and award the required consents to this job.

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd'

GO

CREATE USER [DP300User2] WITH PASSWORD = 'Pa55.w.rd'

GO

CREATE ROLE [SalesReader]

GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1]

GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User2]

GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader]

GO

4.) Application Roles

Application roles can also be created inside a SQL Server database or Azure SQL DB. Unlike database roles, users are not made members of an application role. An application role is activated by the user, by supplying the pre-configured password for the application role.

1.) Built-In Database Roles

Microsoft SQL Server contains a few fixed database jobs inside every database for which the consents are predefined. Clients can be added as individuals from at least one jobs

2.) Fixed Server Roles

These roles assign permissions at the scope of the entire server. Server level principles, which include SQL Server logins, Windows accounts, and Windows group can be added into fixed server roles. These jobs allot consents at the extent of the whole worker. Server level Principals, which incorporate SQL Server logins, Windows records, and Windows group can be added into fixed worker jobs.

Related/References

• Exam DP-300: Microsoft Azure Database Administrator Associate
• Microsoft Certified Azure Database Administrator Associate | DP 300 | Step By Step Activity Guides (Hands-On Labs)
• Azure SQL Deployment Options | SQL Managed Instance | SQL Database| SQL On VM
• Migrate SQL Server To Azure SQL Database
• Implement A High Availability And Disaster Recovery Environment
• Use External Table On Azure SQL Managed Instance To Read Data From Azure SQL Database
• Optimize Query Performance In SQL Server
• Automate Tasks In Azure SQL Server

Next Task For You

We will cover all the exam objectives related to how to perform migrations, Hands-On Labs, and practice tests in our Azure Database Administrator training program. If you want to begin your journey towards becoming a Microsoft Certified: Azure Database Administrator Associate by checking our FREE CLASS.