SQL Monitor 11

Authenticating with OpenID Connect

By default, users log in to SQL Monitor using the passwords set by the administrator.

Alternatively, administrators can set SQL Monitor to authenticate users with their OpenID Connect which can enable scenarios with single sign-on or two-factor authentication. When a request is not authenticated SQL Monitor will contact your identity provider and redirect to its login page. After the login credentials are verified the identity provider will redirect back to SQL Monitor.

When using OpenID Connect administrators can restrict which servers users can access in the same way as when Active Directory authentication is used.

Configuring Authentication Provider

The first step to set up OpenID Connect is to register SQL Monitor as an application in your identity provider. Specify the redirect URL for SQL Monitor as https://yourSqlMonitorHost.com/openidconnectaccountThis is the URL that the Identity Provider will use after a user is authenticated.

SQL Monitor will make the OpenID Connect authorization request using the "openid profile" scopes.

This step should also provide you with information like the ClientID (sometimes referred to as ApplicationId) that you will need to finish the setup in SQL Monitor itself.

Switching to OpenID Connect authentication

OpenID Connect is only supported on IIS with HTTPS enabled. See here for more information on how to set up SQL Monitor in IIS. Application initialization should also be configured as described here.

Only administrators can switch to OpenID Connect authentication.

  1. In the Configuration tab, under Application options, click Authentication settings:

  2. Select Use OpenID Connect:

  3. A popup appears:
  4. Enter the ClientId. It was returned by your Identity Provider after registering SQL Monitor in it.
  5. Enter the Username claim name or Group claim name. This the name of a claim that will be supplied by your identity provider after a user logs in and SQL Monitor will use that information to identify which user logs on. Common examples of claim names to be used for the Username claim name field value are email or preferred_username. For the Group claim name a common example is groups.
  6. Under Authority, enter the URL to reach your authentication provider. E.g. https://login.microsoftonline.com/[tenant-id]/v2.0. This value will also be used to verify the issue of your id token through an exact match (including any trailing slashes).

  7. Add an Administrator user or group. Normally it is the username of the Administrator of SQL Monitor or a name of a user group to which an Administrator belongs to. It needs to map to the value returned in the field specified in Claim name before.

    We recommend you create an administrator group and specify this as the administrator account. This means you can add more users to the administrator group in your identity provider instead of configuring new users in SQL Monitor.

  8. Click Save settings
    SQL Monitor logs you and all other users out.
  9. Restart your SQL Monitor website on IIS
  10. Access the login page for SQL Monitor which should redirect you to your Identity Provider's login page.

Configuring OpenID Connect scopes

By default SQL Monitor will use openid and profile as the scopes for the authentication request. Some authentication providers may require additional scopes to return specific data like groups. You'll be able to configure these scopes by running the following these steps

  1. Connect to your SQL Monitor repository.
  2. Run the following script. It's important to keep the previously mentioned default scopes (“openid” and “profile”) and add any others you might need like “groups”

    Edit OpenID scopes used by SQL Monitor

    INSERT INTO [settings].[KeyValuePairs] VALUES ('OIDC-Scope', 'openid profile <AnyAdditionalScope>, 'STRING')
  3. Restart SQL Monitor’s website on IIS.

Switching from OpenID Connect authentication to default authentication

Only administrators can switch back from OpenID Connect authentication to default authentication.

  1. Log in to SQL Monitor as an administrator.
  2. In the Configuration tab, click Authentication settings.
  3. Select Basic authentication.
    The Confirm authentication changes window opens:

  4. Click Confirm
    SQL Monitor logs you and all other users out.
  5. Log in to SQL Monitor with your SQL Monitor credentials.

Switching back to basic authentication does not clear any OpenId Connect configuration stored in SQL Monitor.

If you've forgotten your password, see: Resetting your SQL Monitor password

If you are unable to access the user interface, follow the instructions from Incorrect service account credentials.

Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com

Didn't find what you were looking for?