Service Principle in Azure

Question

What is service principle and how to create and use it to connect azure sql database?

Tuhin Paul · Answer

Part -2 Steps to Create and Use a Service Principal Step 1: Create a Service Principal Using Azure CLI : Run the following command to create a service principal: az ad sp create-for-rbac --name "MyAppServicePrincipal" --skip-assignment Replace "MyAppServicePrincipal" with a meaningful name for your service principal. The command outputs: appId (Application ID) password (Client Secret) tenant (Tenant ID) Using Azure Portal : Go to Azure Active Directory > App registrations > New registration . Provide a name for the app and register it. Note the Application (client) ID and Directory (tenant) ID . Go to Certificates & secrets > New client secret to create a client secret. Step 2: Assign Roles to the Service Principal To allow the service principal to access Azure SQL Database, assign it a role (e.g., Contributor or SQL DB Contributor ). Using Azure CLI : az role assignment create --assignee --role "Contributor" --scope /subscriptions/ /resourceGroups/ /providers/Microsoft.Sql/servers/ Replace , , , and with appropriate values. Using Azure Portal : Go to your Azure SQL Server > Access control (IAM) > Add role assignment . Select the role (e.g., Contributor ) and assign it to the service principal. Step 3: Use the Service Principal to Connect to Azure SQL Database To connect to Azure SQL Database using the service principal, you can use libraries like pyodbc (Python) or System.Data.SqlClient (C#). Example: Python with pyodbc Install the required libraries: pip install pyodbc azure-identity Use the following code to connect: from azure.identity import ClientSecretCredential import pyodbc # Service Principal credentials tenant_id = " " client_id = " " client_secret = " " # Azure SQL Database details server = " .database.windows.net" database = " " # Get access token credential = ClientSecretCredential(tenant_id, client_id, client_secret) token = credential.get_token("https://database.windows.net/.default").token # Connect to Azure SQL Database connection_string = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database}" connection = pyodbc.connect(connection_string, attrs_before={1256: bytearray(token)}) # Execute a query cursor = connection.cursor() cursor.execute("SELECT @@VERSION") row = cursor.fetchone() print(row[0])

Tuhin Paul · Answer

Part -1 A Service Principal is an identity created for use with applications, services, and automation tools to access Azure resources. It is essentially a "user account" for applications, allowing them to authenticate and interact with Azure services without using a personal user account. Main Terms Application ID (Client ID) : A unique identifier for the application registered in Azure Active Directory (AAD). Used to identify the application during authentication. Tenant ID : The Azure Active Directory tenant where the application is registered. Represents the organization or directory in Azure. Client Secret : A password or certificate used for authentication. Must be securely stored and rotated periodically. Roles and Permissions : The service principal is assigned roles (e.g., Contributor, Reader) to control access to Azure resources. Roles are assigned at the subscription, resource group, or resource level. Use Cases : Automating Azure Tasks : Deploying resources using Infrastructure as Code (IaC) tools like Terraform or ARM templates. Managing Azure resources programmatically. Connecting to Azure SQL Database : Applications or scripts can use a service principal to authenticate and interact with Azure SQL Database. CI/CD Pipelines : Integrating with Azure DevOps, GitHub Actions, or Jenkins to automate deployments and testing. Monitoring and Management : Automating monitoring, logging, and management tasks using Azure Monitor, Log Analytics, or Azure Automation.

Jaish Mathews · Answer

What is a Service Principal in Azure? A Service Principal in Azure is a security identity used by applications, services, or automation tools to access Azure resources. It works like a user identity but is designed for applications, enabling authentication and authorization without a human user. In the context of Azure SQL Database , a Service Principal can be used to authenticate and connect securely without using passwords. Steps to Create and Use a Service Principal for Azure SQL Database 1. Create a Service Principal (App Registration) Go to Azure Portal : Azure Portal Navigate to "Azure Active Directory" : In the left menu , select Azure Active Directory . Create a New App Registration : Click App registrations > New registration . Provide a Name (e.g., AzureSQLServicePrincipal ). Choose "Accounts in this organizational directory only" . Click Register . Get Client ID and Tenant ID : After registration, go to Overview and note down: Application (client) ID Directory (tenant) ID Create a Client Secret : Go to Certificates & secrets > New client secret . Provide a name , select expiration, and click Add . Copy the client secret value (it won't be visible later). 2. Assign the Service Principal to the Azure SQL Database Go to the SQL Server in Azure Portal : Navigate to SQL Server (not the database) where your Azure SQL Database is hosted. Enable Managed Identity Authentication (Optional): Go to Azure Active Directory under settings. Set "Azure AD Authentication Only" to Yes (if needed). Create an Azure AD Admin (If Not Set) : Go to Active Directory admin > Set admin . Select your Azure AD Service Principal and save changes. Grant Permissions in SQL Server : Connect to SQL Server using Azure Data Studio or SSMS . Run the following SQL to create a user and assign roles: -- Switch to the correct database USE [YourDatabaseName]; GO -- Create a new user for the Service Principal CREATE USER [YourServicePrincipalName] FROM EXTERNAL PROVIDER; GO -- Assign required roles (Example: db_owner) ALTER ROLE db_owner ADD MEMBER [YourServicePrincipalName]; GO 3. Connect to Azure SQL Database Using the Service Principal Using Azure AD Authentication in C# (ADO.NET) You can authenticate using Azure.Identity and the Access Token from the Service Principal. using System; using System.Data.SqlClient; using Azure.Identity; class Program { static void Main() { string tenantId = "your-tenant-id"; string clientId = "your-client-id"; string clientSecret = "your-client-secret"; string sqlServer = "your-sql-server.database.windows.net"; string database = "your-database-name"; var credential = new ClientSecretCredential(tenantId, clientId, clientSecret); string accessToken = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" })).Token; string connectionString = $"Server={sqlServer}; Database={database}; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30;"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.AccessToken = accessToken; connection.Open(); Console.WriteLine("Connected successfully!"); } } } Summary Create a Service Principal in Azure AD. Assign the Service Principal to Azure SQL Database . Grant necessary permissions using SQL. Use the Service Principal to connect via code. This approach enhances security by eliminating password storage and relying on Azure AD authentication . Let me know if you need further clarification! ??

Service Principle in Azure

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)