>_nexthopreachable

Windows Server Lab: Active Directory, SQL Server, and IIS

Ali Bahadır Coşkun17 min read

Most internal business applications run on the same few building blocks. Active Directory (AD) holds the users, groups, and computers and answers their authentication requests with Kerberos. DNS lets clients find the domain controllers and the services. A web server publishes the application, and a database server holds its data. Scheduled jobs move files in and out, backups protect the database, and monitoring watches the whole chain. On Windows, these are AD Domain Services, Internet Information Services (IIS), SQL Server, and SQL Server Agent. Most of the work on such a system is operations work: checking that everything is healthy, granting access, restoring data, and finding out which layer failed when users report a problem.

The lab is built around a small synthetic application called TradeOps. Operators sign in with their domain accounts, see a list of trades, and move them through the statuses BOOKED, VALIDATED, and SETTLED. New trades arrive in files from an upstream system every 15 minutes, batch jobs validate and settle them, and a settlement extract is delivered to a file share. The bank theme and every name and number in it are invented.

In this article, we'll go through the topology, then each part of the lab and how it works, and finally what I practised on it. Every screenshot comes from the lab itself.

Topology

The lab has four virtual machines on one Linux host. DC01, WEB01, and SQL01 run Windows Server 2025 and belong to the bank.test domain. MON01 is an Ubuntu server that runs Prometheus and Grafana and doubles as a jump host with DNS and network troubleshooting tools.

The virtual machines run directly on KVM, the virtualization built into Linux, not as PNETLab nodes. They are all connected to a private virtual switch on the host, bankbr0, and the host itself is 10.77.0.1 on it. Nothing in the lab is reachable from outside the host. The Windows desktops are reached with Remote Desktop through an SSH tunnel.

MachineAddressOSRole
DC0110.77.0.10Windows Server 2025AD DS, DNS, DHCP for bank.test
WEB0110.77.0.20Windows Server 2025IIS and TradeOps, SSMS, file shares, upstream feed task
SQL0110.77.0.30Windows Server 2025SQL Server 2022 Developer, SQL Server Agent jobs, backups
MON0110.77.0.40Ubuntu 26.04Prometheus, Grafana, troubleshooting tools
Host10.77.0.1UbuntuKVM, the bridge, the metrics exporter

The Domain

DC01 is the only domain controller of bank.test, and it also runs DNS and DHCP.

A domain is a group of users, groups, and computers that share one directory database and one security policy, and a domain controller (DC) holds a copy of that database and answers authentication requests. Active Directory depends on DNS: clients find a DC by querying SRV records such as _ldap._tcp.dc._msdcs.bank.test, which the DC registers itself. _kerberos on port 88 is the Key Distribution Center (KDC) that issues Kerberos tickets, and _ldap on port 389 is the directory. If these records are missing, logons fail even though the DC is up.

The bank.test zone also holds a trades record for the application and the aliases files and grafana. DHCP hands out addresses to client devices. The servers have static addresses.

Access follows the AGDLP model: user Accounts go into Global groups by role (GG-), global groups go into Domain Local groups (DL-), and the domain local groups get the Permissions. The application group Trade-Users contains the three business role groups, so a user gets the application by being in the right team, and when someone changes teams, every permission follows. A baseline GPO linked to the Servers OU sets a logon banner, a 15-minute session lock, and PowerShell script block logging on every member server.

SQL Server

SQL01 runs SQL Server 2022 Developer Edition, updated to the latest cumulative update. Developer Edition has all the features of Enterprise Edition and is free to use for testing.

The instance and its databases

An installation of SQL Server is called an instance. The lab has one, the default instance, which listens on TCP port 1433. It runs as two main Windows services: SQL Server, the database engine itself, and SQL Server Agent, which runs scheduled jobs such as backups. The Agent depends on the engine, so stopping SQL Server also stops the Agent, and starting SQL Server again does not start the Agent automatically.

Besides the TradeOps database, every instance has a few system databases. The two worth knowing first are master, which stores the logins and the server settings, and msdb, which stores the Agent jobs and the history of every backup and restore.

Each database is stored as two files: a data file (.mdf) with the tables, and a transaction log (.ldf). Every change is written to the log first. If the server crashes, SQL Server reads the log when it starts again and makes sure no committed change is lost. The log is also what makes the backups in this lab possible.

Logins, users, and permissions

SQL Server has two levels of identity. A login lets someone connect to the server. A database user, which is linked to a login, lets them into one database. Being able to connect is not enough to see any data: the user also needs permissions inside the database.

This server accepts Windows authentication only. Nobody connects with a separate SQL username and password. Every connection is a Windows account, so the same accounts and groups from Active Directory are used everywhere:

Permissions are given with GRANT and taken away with REVOKE. There is also DENY, which blocks a permission even if the user gets it some other way, for example through a group. SQL Server also has ready-made roles: db_owner can do anything in a database, and sysadmin can do anything on the whole server. They are the easy way out when an application gets a permission error, and the wrong one.

The TradeOps database has four tables (Trades, TradeEvents, Clients, and Instruments) and a few stored procedures. A stored procedure is a saved piece of SQL code that runs on the server, like a function. The application's account, BANK\svc_trade, can run exactly two of them, ListTrades and ChangeTradeStatus, and nothing else:

The account can't read or change the tables directly. The procedures can, because the procedures and the tables have the same owner, and in that case SQL Server only checks whether the caller may run the procedure. This is the principle of least privilege: if someone stole the application's account, all they could do is list trades and change a status through the rules built into the procedure. Those rules are part of the business logic too: a settled trade can't be changed, and a trade must be validated before it can be settled.

The connection from the web server to SQL01 is encrypted with TLS, and the application checks the server's certificate before it connects. If the certificate can't be trusted, it refuses to connect.

When two people change the same data

A database has to deal with many users at the same time. While SQL Server changes a row, it locks the row, and other sessions that want to read it wait until the change is committed or rolled back. Usually that takes milliseconds. But if a transaction is left open, for example in a query window that someone forgot about, everyone who needs that row waits. This is called blocking, and to the user it looks like a page that never finishes loading. SSMS has a tool, Activity Monitor, that shows which sessions are waiting and which session is blocking them.

A web page has a different problem. An operator opens the page, reads a trade, and clicks a button a minute later. In the meantime, someone else might have changed the same trade. TradeOps solves this with a version number. Every row in the Trades table has a Version column that SQL Server changes on every update. The page sends back the version it showed, and the update only goes through if the version hasn't changed. If it has, the operator gets "Trade changed or does not exist. Refresh and retry." instead of silently overwriting someone else's change. Every successful change is also written to the TradeEvents table with who did it and when.

Where to look when something goes wrong

  • SQL Server error log (in SSMS, under Management): startups, shutdowns, failed logins, and backups.
  • Job history (in SSMS, under SQL Server Agent): the result of every scheduled job.
  • Windows Event Viewer: messages from SQL Server and from the application.

A permission error doesn't appear in the SQL Server error log. It goes back to the application, so you find it in the application's own log, here in Event Viewer on WEB01.

IIS and the TradeOps Application

How IIS works

IIS has two parts. A part of Windows itself, HTTP.sys, receives every request on port 443 and puts it in a queue. Behind it, a worker process, w3wp.exe, runs the application code and builds the response.

Each application runs in an application pool, and each pool has its own worker process. If one application crashes or slows down, the others keep running. A pool can also be recycled, which means its worker process is replaced with a fresh one. By default IIS does that once every 29 hours, and also when the application has been idle for 20 minutes. If the worker process crashes five times within five minutes, IIS stops the pool to protect the server. Whatever the reason, a stopped pool means every user gets "503 Service Unavailable".

The pool also decides which Windows account the application runs as. The TradeOps pool runs as the service account BANK\svc_trade, and that is the account SQL Server sees when the application connects. The other pools use a built-in account:

Sites and bindings

A site is the website as IIS knows it. Its binding says how requests reach it: the protocol, the IP address, the port, and optionally a host name. The TradeOps site has one binding, HTTPS on port 443, with a certificate for trades.bank.test. The certificate is what the browser checks to make sure it is talking to the real server.

The settings of a site come from two places. Server-wide settings are in a central file, applicationHost.config, and each site can have its own web.config file in its folder. The site's file can add to or override the server settings. IIS Manager is a graphical editor for both files.

Authentication: who are you?

With Windows authentication, users don't type a password into the page. The browser proves who the user is with the account they are already signed in to Windows with. It uses one of two methods.

Kerberos is the main one. When the user logs on to Windows, the domain controller gives them a ticket. When they open the site, the browser asks the domain controller for a second ticket, just for this website, and hands it to IIS. IIS can check the ticket on its own, without asking the domain controller again. For this to work, the domain controller has to know which account the website runs as. That link is called a service principal name (SPN), and here it is HTTP/trades.bank.test registered on svc_trade.

NTLM is the older fallback. The server sends a random challenge, the browser answers with a value calculated from the password, and the server asks the domain controller whether the answer is right. It works when Kerberos can't, but it is slower and weaker.

On the TradeOps site, anonymous access is turned off and Windows authentication is turned on, so nobody can open the page without being identified:

Authorization: are you allowed in?

Knowing who the user is doesn't mean they are allowed in. That second decision is authorization. On the TradeOps site, it is a short list of rules: allow members of BANK\Trade-Users, deny everyone else. The rules are checked from the top, and the first one that matches wins.

Windows reads a user's group memberships when they log on. If someone is added to Trade-Users, they have to sign out and back in before the change takes effect.

A member of Trade-Users sees the trade list:

Reading the IIS log

IIS writes one line per request to its log in C:\inetpub\logs\LogFiles. Each line has the user name, the HTTP status code, and a sub-status that says more precisely what happened. The most useful ones here:

StatusWhat it means
200OK
401.2The browser hasn't sent credentials yet. This is normal: it's the first step of every Windows login
401.1The login failed, for example a wrong or expired password
401 with a user nameThe user was identified but is not allowed in
500The application itself crashed
503The application pool is stopped

A 503 never reaches the site, so it isn't in this log. It is in a separate error log written by HTTP.sys, in C:\Windows\System32\LogFiles\HTTPERR.

One failure doesn't show up as an error code at all. When the application can't reach SQL Server, it still returns 200 and shows "Database unavailable" on the page. A check that only looks at the status code would call the site healthy. The real error is in Event Viewer on WEB01, as event 1001 from TradeOps.

Batch Jobs

SQL Server Agent runs six scheduled jobs on SQL01:

JobWhenWhat it does
Feed importEvery 5 minutesLoads new trade files into the database
Straight-through processingEvery 5 minutesValidates new trades automatically
Settlement cycleEvery 30 minutesSettles due trades and writes a report to a file share
Transaction log backupEvery 15 minutesBacks up the log
Daily full backup02:00 UTCBacks up the whole database
Housekeeping01:00 UTCArchives old trades and deletes old files

A trade file is loaded completely or not at all. The last line of every file says how many rows it should have, so an incomplete file is recognized and moved to a rejected folder with a note that says why. If the Agent service is stopped, none of these jobs run, including the backups, and the runs that were missed are not made up later.

Backups and Recovery

The lab uses two kinds of backup. A full backup copies the whole database once a night. A log backup copies the changes from the transaction log every 15 minutes. With both, the database can be restored to any point in time: first the full backup, then the log backups one after the other, up to the minute you need. The restore window in SSMS shows them on a timeline:

The log backups have to form an unbroken chain. If one is missing, nothing after it can be restored. With a log backup every 15 minutes, the most data the database can lose is 15 minutes of work. That number is called the recovery point objective (RPO).

Practice restores always go to a database with a different name, never over the live one. In the SSMS restore window, this needs care: when you restore from a database, one option is selected by default that would take the live database offline. It has to be unchecked.

The backups are on SQL01's own disk. That's fine for learning, but they wouldn't survive the loss of the server. In a real setup, they would also be copied somewhere else.

Monitoring

Prometheus on MON01 collects measurements every 30 seconds: whether each service is running, how full the disks are, how old the last backup is, whether the jobs succeeded. It also signs in to the application like a user every few seconds and checks that the trade list appears. 22 alert rules turn these measurements into alerts. An alert only fires after the problem has lasted for a while, usually a minute or more, so a single bad measurement doesn't raise an alarm. Grafana shows it all on one dashboard. The red bands in the request path panel are two outages from the exercises:

Checking the parts and checking the whole service from the outside are both needed. Every service can be running while users still can't work, as the "Database unavailable" case shows.

What I Practised

  • Application pool outage: I stopped the TradeOps pool, saw every user get a 503, and found the reason in the HTTP.sys error log.
  • SQL Server outage: I stopped SQL Server and saw the page still load with "Database unavailable". I traced it through Event Viewer and the Prometheus alerts, and started both SQL Server and SQL Server Agent again.
  • Login problems: I compared a user who couldn't log in (an expired password) with a user who logged in but wasn't allowed in (not in Trade-Users), using the IIS log.
  • Missing permission: I removed the application's permission on one stored procedure, found the error in Event Viewer, and gave back only that one permission.
  • Blocking: I left a transaction open in SSMS, watched the application's requests pile up in Activity Monitor, and ended it.
  • Two people changing the same trade: I submitted the same change twice and saw the second one rejected.
  • Point-in-time restore: I restored the database to a separate copy as it was at a given minute and checked it with DBCC CHECKDB.
  • Rejected trade file: I sent an incomplete file and followed it to the rejected folder, the error note, and the failed job.
  • Incidents: I ran the daily check and worked tickets from the incident simulator, such as an upstream feed that had stopped delivering files.