Azure Pipeline: Executing SQL Script via Service Principal with Minimal Permissions

Introduction:

Welcome back to our Azure DevOps journey! In this blog, we will delve into a more secure approach to integrating Azure SQL Database with Azure Pipelines. While our previous blog highlighted setting up a service principal as the Azure Directory admin of the SQL Server, we have recognized the importance of reducing unnecessary permissions to enhance security. Join us as we explore the steps to grant precise access to service principals and leverage them in conjunction with service connections for secure SQL script execution within your pipelines.

Prerequisites:

Let’s ensure we have the necessary prerequisites in place:

1. Azure DevOps Account: You should have an active Azure DevOps account with the appropriate permissions to create and manage pipelines.

2. SQL Server or Azure SQL Database: You need to have an existing SQL Server or Azure SQL Database instance where the SQL script will be executed. I have already written a blog to setup Azure SQL Database and SQL Server. Read it from here.

Step 1: Create a Service Principal in Azure Portal

1. Sign in to the Azure portal (https://portal.azure.com) with your Azure account credentials.

2. Navigate to the Azure Active Directory (AD) service.

3. Under the App registrations section, click on New registration to create a new application registration.

   

4. Provide a name for your service principal and select the appropriate account type. For example, you can choose between Accounts in this organizational directory only or Accounts in any organizational directory.

   

5. Once created, note down the Application (client) ID and Directory (tenant) ID as these will be needed later during the service connection setup.

   

6. Under the Certificates & secrets section, create a new client secret, and securely store the generated value for later use. Remember that, the secret value will be shown only once after that it will be hidden. If you forgot or lost the secret value, then you have to generate the new one. This secret will be required to authenticate your service principal.

   

Step 2: Creating the stored procedure

For the testing purpose, we will add and remove the guest (user in the database) to and from the db_ddladmin role. We will add the script of that into a stored procedure. Let’s create that:

1. Create two stored procedures on your database, one will add the role and other will drop the role. It is defined with the WITH EXECUTE AS OWNER clause, which means it will execute with the permissions of the owner.

CREATE PROCEDURE dbo.AddRoleToMember
 WITH EXECUTE AS OWNER
 AS
 BEGIN
      EXEC ('ALTER ROLE db_ddladmin ADD MEMBER [guest];');
 END

 CREATE PROCEDURE dbo.RemoveRoleFromMember
 WITH EXECUTE AS OWNER
 AS
 BEGIN
      EXEC ('ALTER ROLE db_ddladmin DROP MEMBER [guest];');
 END

Step 3: Providing access to the Service Principal

We have set up the service principal. Now, we need to grant the necessary access of the Azure SQL server to the service principal, enabling it to execute the queries on the database.

1. Firstly, we need to create a Login. Open SSMS. Connect to your Server. Expand the Databases. Right click on the Master database, which is under System Databases and click on New Query. Paste the below query and run it.
   

   CREATE LOGIN [TestApp1] FROM EXTERNAL PROVIDER;
   

   Explanation of above query:
   This command creates a login called “TestApp1” using an external authentication provider. In this case, the authentication is delegated to an external system or service that will handle the user authentication process. This type of login allows users to authenticate using external credentials, such as those from Azure Active Directory, Active Directory Federation Services (ADFS), or other identity providers.

   Note: The above query will help in solving this error: Error 1: Exception calling “Open” with “0” argument(s): “Login failed for user ‘’.”

2. Now, let’s add our Service Principal as a User. Open SSMS. Connect to your Server. Expand the Databases. This time, Right click on your database and click on New Query. Paste the below query and run it.
   

   CREATE USER [TestApp1] FOR LOGIN [TestApp1]
   ALTER ROLE [db_datawriter] ADD MEMBER [TestApp1]
   

   Explanation of above query:
   The first command creates a user called “TestApp1” in your database, based on the previously created login “TestApp1”. The user is associated with the login and allows the login to access and interact with the objects within your database.
   The second command adds the user “TestApp1” to the “db_datawriter” role within your database. The “db_datawriter” role is a built-in database role that grants the user the necessary permissions to write data to the database.

3. The last thing we have to do is to grant the execute permission on the stored procedures to the user. Run the below query on your database. This step is mandatory else you will get this error: Exception calling “ExecuteNonQuery” with “0” argument(s): “The EXECUTE permission was denied on the object ‘AddRoleToMember’, database ‘DemoDB’, schema ‘dbo’.”
   

   GRANT EXECUTE ON dbo. AddRoleToMember TO [TestApp1];
   GRANT EXECUTE ON dbo. RemoveRoleFromMember TO [TestApp1];
   

   Explanation of above query:
   The first command grants the EXECUTE permission on the ‘AddRoleToMember’ stored procedure in the ‘dbo’ schema to the database principal named ‘[TestApp1]’. The ‘[TestApp1]’ principal will be able to execute the ‘AddRoleToMember’ stored procedure.
   The second command grants the EXECUTE permission on the ‘RemoveRoleFromMember’ stored procedure in the ‘dbo’ schema to the database principal named ‘[TestApp1]’. The ‘[TestApp1]’ principal will be able to execute the ‘RemoveRoleFromMember’ stored procedure.

Bonus:

I am adding some SQL scripts below to verify the permissions which we have given above:

1. This query shows what all permissions user is having of the objects:

SELECT
      DB_NAME() AS [Database],
      princ.name AS [User],
      perm.permission_name AS [Permission],
      perm.state_desc AS [State],
      obj.name AS [Object]
FROM
      sys.database_principals AS princ
JOIN
      sys.database_permissions AS perm ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN
      sys.objects AS obj ON obj.object_id = perm.major_id
WHERE
      princ.name = 'TestApp1'; -- Replace with the name of the user

1. This query shows the roles the user is having on the database:

SELECT 
  r.name AS RoleName
FROM 
  sys.database_role_members m
JOIN 
  sys.database_principals r ON m.role_principal_id = r.principal_id
JOIN 
  sys.database_principals u ON m.member_principal_id = u.principal_id
WHERE  
  u.name = ‘TestApp1’;

Note: We can also set the service principal as the Azure Directory Admin. But, it will grant the administrative privileges on the SQL Server. This means, it will give the elevated permissions and full control over the SQL Server instance.

Step 4: Create a Service Connection in Azure Pipelines

1. Open your Azure DevOps organization and navigate to your project.

2. Go to Project settings.

   

3. Select Service connections under the Pipelines section and click on New service connection.

   

4. Choose the appropriate service connection type based on your requirements. For example, you can select Azure Resource Manager for interacting with Azure resources.

5. Select the Azure Resource Manager option and click on Next.

   

6. Select the appropriate authentication method. In this case, choose Service principal (manual) to manually enter the credentials.

   

7. Enter the Application (client) ID, Directory (tenant) ID and the client secret of the service principal.

8. Optionally, you can test the connection to ensure it is successful.

   

9. Provide Service connection name and click on Verify and save to create the service connection.

   

Note: You may encounter the following error if you fail to grant the service principal the necessary Azure subscription access permission.

To solve this error. Follow the below steps:

1. Login to Azure Portal

2. Click on All Services

3. Click on Subscriptions

4. Go to Access Control (IAM). Click on Add and select Add role assignment.

   

5. Select the Role. I have selected Contributor for now. Select the radio button for User,group or service principal in Assign access to. Select the service principal name as a Member, click on Review, and assign to add the role.

6. Now, try to click on verify under Service Connection screen. It will succeed.

Step 5: Generate an Access Token

1. Open your Azure DevOps organization and go to your project.

2. Navigate to the Releases section and create a new release pipeline. Add a new empty stage to it and name it anything. I have named it Generate Token.

   

3. Open the Generate Token stage, under the Agent job, select the appropriate agent and add a new task.

   

4. Search for the Azure CLI task and add it to the pipeline.

   

5. Configure the task to use the created service connection by selecting the appropriate service connection from the Azure Resource Manager connection dropdown. Select Script Type to PowerShell and Script Location to Inline script.

   

6. Enter the below PowerShell script under Inline script:
   

$token= & az account get-access-token --resource=https://database.windows.net --query accessToken
Write-Host("##vso[task.setvariable variable=accessToken]$token")

Let’s try to understand the code:
The “az account get-access-token” command retrieves an access token for the specified resource.
The access token obtained using the Azure CLI command with the “ — resource“parameter set to “https://database.windows.net” can be used to authenticate and authorize access to Azure SQL Database resources.
The “ — query accessToken” option filters the command output to extract the value of the accessToken field.
The extracted access token is assigned to the “$token” variable in PowerShell for further use.

Step 6: Open the SQL Connection and run the stored procedure

We can use the access token generated on the previous step to open the SQL connection and run our stored procedures or the other SQL queries. Let’s add a task on the pipeline to do this:

1. Add a new task on our existing stage (Generate Token). Search for PowerShell and add it. Select Inline from the Type dropdown.

   

2. Add the below code under Script. For testing, I am only executing one stored procedure, which is assigning the ddl_admin role to [guest].
   

   $sqlServerFQN = '$(SqlServerFQN)'; 
   $sqlDatabaseName = '$(SqlDatabaseName)'; 
   $conn = new-object System.Data.SqlClient.SqlConnection; 
   $conn.ConnectionString = "Server=tcp:$($sqlServerFQN),1433;Initial Catalog=$($sqlDatabaseName);Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"; 
   $conn.AccessToken = $(accessToken); 
   $conn.Open();
   $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
   $SqlCmd.CommandText = "EXEC dbo.AddRoleToMember;";
   $SqlCmd.Connection = $conn;
   $SqlCmd.ExecuteNonQuery();
   $conn.Close();
   

   Explanation of the above code:
   $sqlServerFQN: Represents the fully qualified name (FQN) of the SQL Server instance.
   $sqlDatabaseName: Represents the name of the SQL database.
   It opens the SQL connection using the details provided and runs the query.
   For testing, we are assigning the role of db_ddladmin to the guest user. It closes the connection after running the query.

3. Make sure to add these 2 variables by going to variables tab. We are using this in the PowerShell code.

   

4. Our setup is ready. Run the release pipeline, it will run the query on the database.

Step 7: Let’s Test

1. I have ran the pipeline. Let’s go to the database and verify ddl_admin role is assigned to the [guest] or not. Run the below SQL query on your database to check which user is having ddl_admin role:
   

   SELECT 
   DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
   FROM 
   sys.database_role_members AS DRM  
   RIGHT OUTER JOIN 
   sys.database_principals AS DP1  ON DRM.role_principal_id = DP1.principal_id  
   LEFT OUTER JOIN 
   sys.database_principals AS DP2  ON DRM.member_principal_id = DP2.principal_id  
   WHERE 
   DP1.name = 'db_ddladmin';
   

2. I got the below results after executing the above query. It means ddl_admin role has been assigned to the [guest]:

   

Conclusion:

By adopting a minimal permissions approach and securely integrating Azure SQL Database with Azure Pipelines, you can elevate your CI/CD processes to new levels of security. By reducing unnecessary access and precisely granting the required permissions, you can confidently execute SQL scripts within your pipelines, knowing that your database remains protected. Embrace the power of service principals and service connections to streamline your deployments while maintaining a strong security posture.