Login to Azure SQL Server in on-premises Windows Service using Azure Active Directory

Kalyani1714 

    # Register the Windows Service in Azure AD:

• Sign into the Azure portal and navigate to the Azure Active Directory service.
• In the left menu, select "App registrations" and click on the "New registration" button.
• Provide a name and select the account types that can access the application.
• In the "Redirect URI" section, choose "Web" and enter a dummy URL such as "https://localhost".
• Click on the "Register" button to create the application.

    # Configure Azure SQL Server to allow Azure AD authentication:

• Sign into the Azure portal and navigate to the Azure SQL Server instance.
• In the left menu, select "Active Directory admin" and then click on the "Set admin" button.
• Choose "Azure AD admin" and select the registered application you created in step 1.
• Click on the "Save" button to set the Azure AD admin for the server.

     # Grant the registered application access to the Azure SQL Server:

• In the Azure portal, navigate to the Azure SQL Server instance.
• In the left menu, select "Firewalls and virtual networks" and add a rule to allow access from the IP address of the on-premises Windows Service.
• In the left menu, select "Access control (IAM)" and click on the "Add role assignment" button.
• Choose "Contributor" as the role and select the registered application you created in step 1.
• Click on the "Save" button to grant Contributor role access to the server.

    # Configure the Windows Service to use Azure AD authentication:

• Install the Azure Active Directory Authentication Library (ADAL) for .NET in the Windows Service project.
• In the code that connects to the Azure SQL Server, add the following code to authenticate with Azure AD and obtain an access token:

string tenantId = "<your-tenant-id>";
string clientId = "<your-client-id>";
string clientSecret = "<your-client-secret>";
string resource = "https://database.windows.net/";

var authenticationContext = new AuthenticationContext("https://login.microsoftonline.com/" + tenantId);
var credential = new ClientCredential(clientId, clientSecret);
var result = authenticationContext.AcquireTokenAsync(resource, credential).Result;

SqlConnection connection = new SqlConnection("<your-connection-string>");
connection.AccessToken = result.AccessToken;

Replace the placeholders with the actual values for your Azure AD tenant, registered application, and Azure SQL Server connection string.

By following these steps, you can enable a Windows Service running on-premises to use Azure AD authentication to connect to an Azure SQL Server instance.