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

Copper Contributor

I have Windows Service hosted on-premises and Azure SQL server. How I can connect Azure SQL database in Windows Service using Passwordless ConnectionString (using Azure Active Directory - Integrated)?

3 Replies

@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.

@Kidd_Ip
Thanks!

I have implemented that for Azure Function app. But I have also Windows Service on-Premises and Managed Identity do not work in on-premises applications