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 = fDRIVER={{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! ??