Skip to content

AdminUI: Connecting to SQL Server using Integrated Security

If you wish to use Integrated Security here is what you need to know.

Internet Information Server (IIS)

Websites created in IIS are, by default, configured to run using the ApplicationPoolIdentity. This ensures that you are using a low-privileged account, scoped to that website. An ApplicationPoolIdentity is not a full user on your system, but something in between - a virtual user. It is, however, accessible as IIS APPPOOL\{AppPoolName}.

When AdminUI is setup using our installer, it will use the ApplicationPoolIdentity. However, this means that when SQL Server Integrated Security is used, your website will not be able to access the database.

You could authorize the ApplicationPoolIdentity to access the database, but you may have more control if you use a separate domain account.

AdminUI User Account

If you create a domain account, only the AdminUI API will need access to the database, and therefore this custom user. When creating this user, be aware it will need to exist in a domain accessible to your IIS Server and SQL Server instance.

In our example, we name our account IIS_AdminUI.

Once you have created your IIS_AdminUI user open the Internet Information Server Manager application, and locate the IdentityApiAppPool application pool:

IIS Application Pools

Open the "Advanced Settings" for IdentityApiAppPool, and change the identity account to your custom account;

Account Entry

Once you have completed this step, your AdminUI API site will be running using the newly defined account.

As you are using Integrated Security with SQL Server, we now need to give the user permission to access the AdminUI database.

SQL Server Permissions

The final step in this process is to add the login to your SQL Server instance; you can do this using SQL Server Management Studio or using TSQL.

SQL Server Management Studio

Inside SQL Server Management Studio, locate the Security -> Logins node, using the right-click menu and selecting the "New Login" option.

SQL Account Creation

Using the new user dialogue add the user you used for your Application Pools, in our example, we used IIS_AdiminUI.

You will need to give the user role membership of db_datareader and db_datawriter for your IdentityServer database. These roles allow the account read/write access to the database as well as permission to execute stored procedures.

Depending on your setup, you may also need to give AdminUI execute permissions on stored procedures.

TSQL

Alternatively, you can use TSQL to add the user and also assign the roles in one step using the following statements

Use IdentityExpressDb
CREATE User [DOMAIN\IIS_AdminUI]

EXEC sp_addrolemember 'db_datareader', 'DOMAIN\IIS_AdminUI'

EXEC sp_addrolemember 'db_datawriter', 'DOMAIN\IIS_AdminUI'

Connection String

You can now use database connection strings with integrated security set to true:

Server=myAdminUIDB;Database=myAdminUIDB;Trusted_Connection=True;

Finally

Access your AdminUI site; it will now be using the new user account with SQL Server integrated security.

Who's Using Our Products

Our products and services for IdentityServer are loved by so many. Here are just a few...

  • Hawkins Inc
  • Repower
  • Bosch
  • RandA
  • Plymouth NHS
  • American Heart Association
  • Systopia
  • Deliotte

Awards & Certifications