Entra ID SQL Authentication

During Nerdio Manager’s deployment from Azure Marketplace, there are several Azure services provisioned that, in combination, make up Nerdio Manager. These include app service, key vault, log analytics & app insights, Azure automation, and managed SQL Server and database.

Specifically, for the managed SQL Server and database, the standard deployment only includes local authentication to SQL Server. The authentication credentials are stored in our connection string, which is saved to the key vault under ConnectionStrings—DefaultConnection.

Some customers have security policies, or stricter Azure policies, that require enabling Entra ID authentication on SQL Server. This means that the user identities are managed centrally by Entra ID, rather than SQL Server maintaining a local set of users.

This topic guides you through the process of converting the managed SQL Server from local authentication to mixed mode. In addition, it shows you how to update Nerdio Manager’s connection string from using a local SQL user to Nerdio Manager’s service principal. This means that the same object/application Nerdio Manager uses to interact with Azure, is also used for Nerdio Manager to authenticate to SQL Server.

Prerequisites

• Ensure you have SQL Management Studio installed on your device. You can download here.

• In the Entra ID portal, navigate to Enterprise Applications and make a note of the following Nerdio Enterprise Application details in the Overview section:

  • Application Name: This is added as db_owner to the SQL database.

  • Application ID: This is required within the modified connection string.

To configure SQL Server authentication via Entra ID:

1. Assign a user account as the Entra ID admin on the SQL Server.

   Note: This is in the Azure portal, in the SQL Server under Settings > Microsoft Entra ID.

2. Using SQL Server Management Studio, connect to the hosted SQL Server and sign in with your Entra ID admin account.

3. Discover the required connection details:

   • Navigate to Azure SQL database that Nerdio Manager has deployed.

   • Navigate to Settings > Overview.

   • Copy the Server Name.

   • For Authentication, for most environments select Microsoft Entra ID - Universal with MFA.

   • Specify the Username added as the Entra ID admin on the SQL Server in the first step.

     Note: You may be prompted to add your client’s IP address in the SQL Services Networking rules if it does not already exist. You may review this by navigating to the Nerdio SQL Server in the Azure portal. See this Microsoft article for more information.

4. Execute the following query to add Nerdio Manager as db_owner role.

   Note: Be sure to update the name of the application, keeping the brackets around the Nerdio Enterprise App name. This is the Nerdio Enterprise app name, not the Nerdio App Service name.

   Create user [nerdio-nmw-app] from external provider

   alter role db_owner add member [nerdio-nmw-app];

5. Execute the following query to display the users currently assigned the db_ owner role.

   Note: After assigning Nerdio Manager's application, execute this query to validate the changes.

   SELECT members.name as 'members_name', roles.name as 'roles_name',roles.type_desc as 'roles_desc',members.type_desc as 'members_desc'

   FROM sys.database_role_members rolemem INNER JOIN sys.database_principals roles

   ON rolemem.role_principal_id = roles.principal_id INNER JOIN sys.database_principals members

   ON rolemem.member_principal_id = members.principal_id where roles.name = 'db_owner'

   ORDER BY members.name

   Note: Once the db_owner role is granted to Nerdio Manager's app registration from Entra ID, you need to update the connection string saved to Nerdio Manager's Key Vault in Azure, and replace the local authentication with the Entra ID authentication details.

6. In the Azure portal, navigate to Key vaults.

7. Select the Nerdio Manager Key vault (the name is similar to nmw-app-kv-*).

8. In the blade on the left side, in the Objects section, select Secrets.

   Note: You may need to enable access for your account to read and modify secrets first. This can be done under Settings > Access Policies on the key vault.

9. Copy the value of the AzureAD-ClientSecret.

   Note: This is the current secret Nerdio is using for authentication. Alternatively, you can generate a new secret in the Entra ID portal, which is in addition to the current secret(s).

10. In the Azure portal, in the Nerdio Manager Key vault, open the value of ConnectionStrings--DefaultConnection.

11. Make a backup copy of the current connection string.

    Note: The connection string should look like this:

    Server=tcp:nmw-app-sql-<unique value>.database.windows.net,1433;Initial Catalog=nmw-app-db;Persist Security Info=False;User ID=<local SQL user>;Password=<local SQL password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

12. Update the connection string to this: (Note: The bold parts are what you need to change.)

    Server=tcp:nmw-app-sql-<unique value>.database.windows.net,1433;Authentication=Active Directory Service Principal;Database=nmw-app-db;Initial Catalog=nmw-app-db;Persist Security Info=False;User ID=<Nerdio Entra ID app ID>;Password=<app client secret>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

    Note:

    • Be sure to copy and edit the connection string unique to your environment, specifying the correct App ID and secret values, and including the Authentication= and Database= pieces.

    • Authentication: This is not the Enterprise App Object ID. Leave the default value of ‘Active Directory Service Principal’.

    • The following parameters are updated:

      • Server – Nerdio SQL Server

      • Authentication – ‘Active Directory Service Principal’

      • Database - Nerdio Database Name

      • Initial Catalog – Nerdio Database Name

      • User ID – Enterprise App ID

      • Password - AzureAD-ClientSecret

13. Once you have created the connection string, you need to create a new version of the ConnectionStrings—DefaultConnection within the Nerdio Key Vault, because you are not able to replace the current one.

    • Navigate to the Nerdio Key Vault.

    • Navigate to Objects > Secrets and select ConnectionStrings—DefaultConnection.

    • Select New Version and enter the updated connection string into the Secret Value section (see below).

    • Leave all other parameters as default and select Create.

14. Navigate to App Services.

15. Select the Nerdio Manager app service.

16. Select Stop and then select Start.

17. Once this is confirmed to be working, and you have access to the Nerdio Manager console, you may turn off SQL mixed authentication mode.

    • In the Entra ID portal, navigate to the Nerdio SQL Server.

    • Navigate to Settings > Microsoft Entra ID.

    • Select the option to limit to Entra ID authentication only (see below).

      Notes:

      • Allow a few minutes to ensure there is no trouble getting the Nerdio Manager app service to run again. Nerdio Manager does not load in the UI if the connection string is invalid or permissions have been set incorrectly. Instead, it immediately returns an error.

      • If you have trouble updating the authentication information, you can replace the original connection string and restart the app service to restore functionality.