Skip to content
Related Articles
Open in App
Not now

Related Articles

Microsoft Azure – Identifying & Accessing Management for Azure SQL

Improve Article
Save Article
  • Last Updated : 30 Sep, 2021
Improve Article
Save Article

In this article, we will look into the second aspect of secure the Azure SQL as identity and access management. In your data center where you’ve got a certain policy setup or access controls to operating system methods, there has to be a way to give people permission to do various things in the Azure ecosystem. It is called the Azure Role-Based Access Control or RBAC. 

All Azure operations that you’re doing to deploy various resources use RBAC. It’s a system based on specific permissions from subscriptions, people that have subscriptions based on various roles. For example, you have an owner of a subscription or something called a contributor, who has access and can give permission to deploy Azure resources like Azure SQL.

Azure RBAC is separable from the SQL security system that you would give authentication rights to login to a SQL Server, but it’s going to give you all those rights and access to control and manage Azure resources. Separation of duties concepts from deploying and managing Azure resources to the SQL server security system itself. You can use the Azure portal or CLI to go through and use RBAC-type policies and systems. 

Now what Azure has done for users is, instead of you creating specific roles for Azure SQL access, Azure actually have some built-in roles. You can be a SQL Database Contributor, which is going to give you specific rights to deploy Azure SQL Database, or even a Managed Instance Contributor to deploy Managed Instance.

Azure even has a SQL Security Manager role where you do not have access to deploy things in Azure for Azure SQL, but you have certain rights to audit access to things going on in the Azure ecosystem for Azure SQL. Once you get past that, you get the traditional process and capabilities you need to learn about authorization to do authentication into Azure SQL. This is going to feel very familiar to you and still very similar to SQL Server. 

The very first thing you need to think of is Mixed Mode authentication. In SQL Server, you have two types of modes:

  1. Windows Authentication or Integrated Security
  2. SQL Authentication.

 Because the fact that we require a SQL Server login as an admin, and in fact, when you deploy, you get what’s called a server admin is that SQL Authentication, both for the logical server or for Managed Instance. Mixed-mode is forced, it has to be. In fact, this server admin that SQL is going to be a server-level principal for your logical server in Azure Database, and it’s going to be a member of the sysadmin role for Managed Instance. 

Now, you’re saying to yourself, “That’s interesting, but I need Windows Authentication, I need that Integrated Security Authentication,” and so Azure supplies and allows you to do Azure Active Directory Authentication for administrators and for logins. This is what it looks like for Managed Instance. 

Azure Managed Instance:

  • Azure AD Server Admin
  • SQL or Azure AD Logins
  • Database Users
  • SQL Server Contained Database support

For Managed Instance, it additions that SQL login, that’s a sysadmin, you can create an Azure Active Directory Server admin as well, and that becomes a member of the sysadmin role. You can addition can create logins just like you’re used to today with SQL Server for SQL logins or Azure Active Directory logins just like you would for Windows logins. You then can go through the process of creating database users that are mapped to these logins. Azure even supports the concept like in SQL Server of a SQL Server Contained Database system.

Azure SQL Database:

  • Azure AD Server Admin
  • SQL logins
  • loginmanager and dbmanager roles for limited server admins
  • Database Users
  • Contained Database User including Azure AD

Very similar to MI, you can create an Azure Active Directory server admin that’ll be just like that SQL admin you created in the beginning when you deploy an Azure SQL Database. You also can create SQL logins, but this is where it divergence a little bit. We have special server admin roles in the logical server that are going to allow certain logins to manage logins or to manage databases across your logical server. Now, you could create database users mapped to these logins as well in Azure Database, but here’s what Azure recommends; Azure has the concept of Contained Database users without creating a Contained Database, and they could even be Azure Active Directory users. 

Setup & configure authentication and authorization

 Now that you’ve seen what the capabilities look like for authentication, it is important to remember when you deploy, you’re going to choose your SQL Server admin. But after you deploy, you can actually add an Azure Active Directory admin both for MI or Azure Database. Then just like with SQL Server, admins that you’ve authenticated can now create other users, other logins. Then once you’re done with that process, you grant access to SQL Server within this system just like you would a SQL Server whether it’s using roles, whether that’s granting specific authentication to objects, to logins, or to users.

You need Azure RBAC to get rights to do things with Azure. Once you’ve got that down and deployed resources, then you’re going to use SQL Server authentication methods, which are slightly different in some cases with MI and Database, including Azure Active Directory authentication. Then it’s going to feel just like a SQL Server. It’s going to grant that access to the objects you need based on roles, or based on logins that you’ve created, or based on Database users.

My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!