Sending a Microsoft Teams Alert From a SQL Server Agent Job

An alert in an unread inbox is nearly the same as no alert. A Teams alert can put a failed SQL Server Agent job where the on-call team already looks, with Database Mail ready when delivery fails.

Build the Teams Alert Receiver First

Create a Teams Workflows flow that accepts a webhook request and posts to the intended channel. Use the supported workflow trigger and record its owner, channel, and lifecycle. A webhook tied to one person's account can disappear when that account changes. Protect the generated URL as a secret. Anyone holding it can attempt to post to the flow. I send a harmless test message before connecting Agent. Confirm the channel, formatting, and visible sender. A message delivered to a test channel is not proof the production team will see it.

Decide what the message needs: server, job, failing step, timestamp, and a short error. Avoid full query text or customer data. Put a correlation ID or job run identifier in the alert when your process has one.

Use an Agent PowerShell Step

Create an Agent job step that reads the webhook URL from an approved secret source and posts an Adaptive Card. The Teams Workflows webhook template expects a message with an attachments array, and a body with only a text property does not post. Keep -Depth on ConvertTo-Json. Without it, Windows PowerShell 5.1 silently sends the card as the string System.Collections.Hashtable. Run it under a proxy or Agent service context with only the required outbound access. Do not embed the URL in job step text, since people with job read access can see it. The sample uses an environment variable for clarity. A real deployment should inject the secret through your managed secret path and test how Agent inherits it.

# PowerShell
$uri = $env:TEAMS_WEBHOOK_URL
if ([string]::IsNullOrWhiteSpace($uri)) { throw 'Webhook URL is missing.' }
$card = @{
    type    = 'AdaptiveCard'
    version = '1.4'
    body    = @(
        @{ type = 'TextBlock'; weight = 'Bolder'; text = 'SQL Server Agent job failed' },
        @{ type = 'TextBlock'; wrap = $true; text = "Server: $env:COMPUTERNAME. Check job history." }
    )
}
$payload = @{
    type        = 'message'
    attachments = @(@{ contentType = 'application/vnd.microsoft.card.adaptive'; content = $card })
} | ConvertTo-Json -Depth 10
Invoke-RestMethod -Uri $uri -Method Post -ContentType 'application/json' -Body $payload -TimeoutSec 10

Connect Job Failure to the Teams Alert

Have the work step go to the notification step on failure, then mark the job failed after notification. A successful notification must not turn a failed backup or ETL task into a green job. Capture the original error and failing step in job history before composing a short message. I keep a distinct test job that always fails in a safe way so the alert route can be checked after changes. The channel can be busy; a subject-like first line helps people scan it.

What if the webhook is down? Set a bounded timeout. Do not let the alert step hang forever while the original failure waits for attention. Track notification failure separately in job history and invoke Database Mail as the fallback.

Keep a Mail Path

Configure Database Mail and an Agent operator so the failure still reaches a mailbox if Teams delivery fails. Test that path independently. The fallback is not an excuse to ignore a broken workflow for weeks. I log a distinct message when the webhook call fails, including the HTTP status or exception class but excluding the secret URL. Keep mail content short enough for an on-call person to decide which job needs attention. The call below needs the server setting Database Mail XPs turned on and a mail profile named OperationsMail. On my test instance that setting was off, and the call failed with error 15281, which says the component is turned off. With the setting on and no profile of that name, it stops with error 14607, profile name is not valid. With both in place, the mail queued and reached my test mail server while SQL Server Agent was stopped, since Database Mail runs from msdb queues, not from Agent.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = N'OperationsMail',
     @recipients = N'dba-oncall@example.invalid',
     @subject = N'SQL Server Agent alert delivery failed',
     @body = N'Check the failed job and the Teams notification step.';

Consider the SQL Server 2025 Route

SQL Server 2025 includes sys.sp_invoke_external_rest_endpoint, disabled by default. It posts only to HTTPS endpoints; a plain HTTP URL stops with error 31610. A caller outside sysadmin needs GRANT EXECUTE ANY EXTERNAL ENDPOINT in the database, or the call stops with error 8189. The SQL Server machine must trust the endpoint certificate. Against a self-signed certificate it did not trust, my call stopped with error 31608 and HRESULT 0x80070008, which reads like a memory error and says nothing about the certificate. Use a protected credential and an approved destination policy. Do not store a Teams webhook URL in a procedure body or pass it to a query log. The call accepts a JSON payload and returns a response for inspection. This route removes a separate PowerShell step, but it also gives the database engine outbound network capability. Review that choice with the security team.

After the feature is enabled and the approved endpoint is made available to the job session, the call has this shape. The sample reads the endpoint from session context so no secret URL appears in the step text. Your secret-management process must set that value securely before the call. With no value set, the block stops on purpose with error 50020. With a value set but the server setting external rest endpoint enabled still off, the call fails with error 31643.

DECLARE @WebhookUrl nvarchar(4000) =
    CONVERT(nvarchar(4000), SESSION_CONTEXT(N'OpsWebhookUrl'));
DECLARE @response nvarchar(max);
IF @WebhookUrl IS NULL THROW 50020, 'Webhook URL is missing.', 1;
EXEC sys.sp_invoke_external_rest_endpoint
     @url = @WebhookUrl,
     @method = N'POST',
     @payload = N'{"type":"message","attachments":[{"contentType":"application/vnd.microsoft.card.adaptive","content":{"type":"AdaptiveCard","version":"1.4","body":[{"type":"TextBlock","weight":"Bolder","text":"SQL Server Agent job failed"},{"type":"TextBlock","wrap":true,"text":"Check job history for the failed step."}]}}]}',
     @timeout = 10,
     @response = @response OUTPUT;
SELECT @response AS webhook_response;

Keep Database Mail as fallback here too. A stored procedure returning successfully does not prove somebody read the channel. Test the whole path through an actual failed Agent job.

Keep the Webhook Secret Out of History

Agent step definitions, PowerShell transcripts, error messages, and troubleshooting screenshots can expose a webhook URL. Store it in a controlled secret mechanism and give the job identity read access only to that secret. I test failure logging with an invalid endpoint in a nonproduction flow to make sure the full URL is not written into job history. Rotate the secret after a suspected exposure. A channel webhook is an inbound door, even when the message is only an alert.

Do not include full SQL error text in Teams by default. Some errors carry object names, file paths, query fragments, or literal values. Send job and step identifiers plus a short safe summary. The DBA can open secured Agent history for detail. That makes the channel useful without turning it into a second log store.

Test the Human Response to a Teams Alert

An alert that arrives at 2 AM still needs an owner and action. I ask the on-call team to run a test failure and follow the message to the relevant job history. Does the channel notification surface on the device they actually carry? Does the message distinguish production from development? Does it say which job failed and when? The workflow can post perfectly while the human path fails.

Schedule a periodic end-to-end test after Teams or Workflows changes. Check that the flow owner remains active and that channel permissions still allow posting. If the fallback mail fires, investigate the webhook failure rather than accepting both as equal routes forever. The best alert has a clear primary path, a tested backup path, and no secret material in either one.

Verify Delivery, Ownership, and Noise

Run the safe failure job. Confirm the original job remains failed, the Teams channel shows one actionable message, and the mail fallback behaves when the webhook is deliberately unavailable in the test. Rotate the webhook secret and verify the job follows the rotation. Record who owns the flow and who receives its failure notifications. I revisit alerts after team changes; an old channel can become a museum of ignored red icons.

Control duplicate alerts. A job that retries several times should say when the final failure requires action, while still preserving each attempt in history. A Teams alert should carry enough context for a DBA to open the right job without copying private data into a channel. The best alert turns a failure into a clear next step.

Related reading on this blog: SQL Server Alert Management: From Chaos to Clarity and Alerting on Long-Running Queries With a SQL Agent Job.

An alert is not a webhook call, it is a message somebody receives and can act on.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.