Configure Entra ID SQL Authentication

During Nerdio Manager deployment from Azure Marketplace, several Azure services are provisioned, including App Service, Key Vault, Log Analytics and App Insights, Azure Automation, and a managed SQL Server and database. By default, the managed SQL Server uses local authentication only, with credentials stored in the connection string saved to the Key Vault under ConnectionStrings—DefaultConnection.

If your security policies require Entra ID authentication on SQL Server, user identities are managed centrally by Entra ID rather than by a local SQL Server user set. This article guides you through the process of converting the managed SQL Server from local authentication to mixed mode and updating Nerdio Manager's connection string to authenticate using Nerdio Manager's service principal — the same application object Nerdio Manager uses to interact with Azure.

Prerequisites

RBAC and permissions

If Nerdio Manager has been installed using the advanced installation method, and/or restricted permissions have been applied, ensure that the account used to connect to SQL Server has, at minimum, the Contributor role. Following the Principle of Least Privilege (PoLP), do not use an account with permissions beyond what this procedure requires.

Preparatory steps

Before carrying out this procedure, note the following details from the Nerdio Manager Enterprise Application in Entra ID. In the Entra ID portal, navigate to Enterprise Applications and in the Overview section, record the following:

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

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

Assign the Entra ID admin and connect to SQL Server

1. In the Azure portal, navigate to the Nerdio Manager SQL Server and select Settings > Microsoft Entra ID. Assign a user account as the Entra ID admin on the SQL Server.

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

   Important: As noted in the prerequisites above, ensure the account used has, at minimum, the least privilege Contributor role.

3. Enter the required connection details for the Nerdio Manager SQL database:

   1. Navigate to the Azure SQL database that Nerdio Manager has deployed.

   2. Navigate to Settings > Overview and copy the Server Name.

   3. In SSMS, paste the Server Name into the Server name field.

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

   5. In the Username field, specify the account assigned as the Entra ID admin on the SQL Server in step 1.

      Note: You may be prompted to add your client IP address to the SQL Server networking rules if it does not already exist. Navigate to the Nerdio Manager SQL Server in the Azure portal to review this. For more information, see Azure SQL Database and Azure Synapse IP firewall rules.

4. In the SSMS toolbar, confirm that the database dropdown displays nmw-app-db before running any queries. Running queries against the master database causes a Cannot alter the role 'db_owner' error.

Run the configuration queries

1. Run the following query to add Nerdio Manager as db_owner. Replace nerdio-nmw-app with your Nerdio Enterprise App name, keeping the square brackets. This is the Enterprise App name, not the App Service name.

   CREATE USER [nerdio-nmw-app] FROM EXTERNAL PROVIDER

   ALTER ROLE db_owner ADD MEMBER [nerdio-nmw-app];

2. Run the following query to validate the changes by displaying all users currently assigned the db_owner role. Confirm that Nerdio Manager's application appears in the results.

   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

Update the Key Vault connection string

1. In the Azure portal, navigate to Key vaults and select the Nerdio Manager Key Vault. The name follows the pattern nmw-app-kv-*.

2. In the left blade, under Objects, select Secrets.

   Note: You may need to enable access for your account to read and modify secrets first. Navigate to Settings > Access Policies on the Key Vault to configure this.

3. Copy the value of AzureAD-ClientSecret. This is the current secret Nerdio Manager uses for authentication.

   Note: Alternatively, you can generate a new secret in the Entra ID portal. A new secret is added alongside any existing secrets.

4. Open the value of ConnectionStrings--DefaultConnection and make a backup copy of the current connection string before proceeding. The existing string follows this format:

   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;

5. Construct the updated connection string using the format below, replacing the placeholder values with the App ID and client secret specific to your environment. Ensure the Authentication= and Database= parameters are included.

   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: The User ID value is the Enterprise App ID, not the Enterprise App Object ID. Leave Authentication set to Active Directory Service Principal. The following parameters are updated from the original connection string:

   • Server — set to the Nerdio SQL Server

   • Authentication — set to Active Directory Service Principal

   • Database — set to the Nerdio database name

   • Initial Catalog — set to the Nerdio database name

   • User ID — set to the Enterprise App ID

   • Password — set to the AzureAD-ClientSecret value

6. In the Nerdio Manager Key Vault, navigate to Objects > Secrets and select ConnectionStrings—DefaultConnection. Because the existing secret cannot be replaced directly, create a new version as follows:

   1. Select New Version.

   2. Enter the updated connection string in the Secret Value field.

   3. Leave all other parameters as default and select Create.

Restart the App Service and finalize authentication

1. In the Azure portal, navigate to App Services and select the Nerdio Manager App Service.

2. Select Stop, and then select Start.

3. After confirming that Nerdio Manager is accessible and functioning correctly, disable SQL mixed authentication mode to restrict access to Entra ID authentication only:

   1. In the Azure portal, navigate to the Nerdio Manager SQL Server.

   2. Navigate to Settings > Microsoft Entra ID.

   3. Select the option to limit authentication to Entra ID only.

   Notes:

   • Allow a few minutes after restarting before verifying access. Nerdio Manager does not load in the UI if the connection string is invalid or permissions are set incorrectly — it returns an error immediately.

   • If you experience issues, replace the original connection string and restart the App Service to restore functionality.

Additional resources

• Configure and manage Microsoft Entra authentication with Azure SQL — Microsoft Learn

• VNet integration firewall requirements