Detent PointTest-SqlSpnKerberosAuth provides visibility into the security protocols used by SQL Server connections. This command is essential for organizations that rely on Kerberos authentication for secure connections.
- Who uses this: Database administrators, security teams, and IT managers who need to ensure Kerberos is being used correctly.
- Risks:
- Misconfigured SPNs can lead to NTLM usage instead of Kerberos.
- Local connections may report NTLM even when SPNs are correct.
- When a manager cares:
- When migrating to Kerberos authentication for improved security.
- When troubleshooting connection issues.
Day-to-Day Use
- Connect to the SQL Server instance using integrated security.
- Run Test-SqlSpnKerberosAuth to verify Kerberos usage.
- Analyze the result, paying attention to IsLocalCaveat and warning messages.
# Example use case:
$result = Test-SqlSpnKerberosAuth -ServerInstance "my_sql_instance"if ($result.IsSucceeded) { Write-Host “Kerberos is being used for this connection.” } else { Write-Warning $result.Reason }
Code Examples
- Verify Kerberos usage on a remote client:
$result = Test-SqlSpnKerberosAuth -ServerInstance "my_sql_instance"if (!$result.IsLocalCaveat) { if ($result.IsSucceeded) { Write-Host “Kerberos is being used for this connection.” } else { Write-Warning $result.Reason } } else { Write-Warning “This is a local connection, Kerberos usage may be incorrect.” }
What This Command Does
- Connects to the SQL Server instance using integrated security.
- Queries sys.dm_exec_connections for authentication and transport information.
- Reports whether Kerberos or NTLM is being used.
Step-by-Step Recipe
- Ensure you have the SpnManager module installed.
- Run Test-SqlSpnKerberosAuth with the SQL instance name as an argument.
- Analyze the result to determine if Kerberos is being used.
Stuck?
- Check that your SPNs are correctly configured and readable.
- Verify that your connection is not local (use a remote client).
- Consult the Microsoft documentation for sys.dm_exec_connections.
| Field | Value |
|---|---|
| Vendor | Detent Point LLC |
| Product | SpnManager 0.4.0 |
| Licence | Proprietary - licensed, not sold. See LICENSE. |
| Operational impact | Read-only. Queries Active Directory and reports findings; changes nothing. |
| Rights required | Directory read access. |
| Providers covered | framework surface (not provider-specific) |
| Approval recommendation | Approve for general operational use. It cannot alter directory state. |
Licence terms are proprietary and are supplied with the purchase, subscription or evaluation agreement. No open-source licence is granted.
| What | Why |
|---|---|
| Windows PowerShell 5.1 or later | Declared by the module manifest. |
| ActiveDirectory 1.0.0.0 | Required module. Ships with RSAT; install the Active Directory PowerShell feature. |
| A reachable domain controller | Every provider reads from Active Directory. |
| An account with directory read access | Needed to enumerate accounts and their SPNs. |
This command handles data in accordance with Microsoft’s documentation on sys.dm_exec_connections.
- Data-handling: The command queries system views, which is an approved method for gathering information about connections.
- Transit: No sensitive data is transmitted; only authentication and transport details are reported.
- At-rest: No data storage or modification occurs during the execution of this command.
- Audit trail: The command’s result can be used to audit Kerberos usage, ensuring compliance with security policies.
Example 1
Test-SqlSpnKerberosAuth -ServerInstance 'SQLSRV01.corp.example.com'Run FROM A REMOTE CLIENT: IsKerberos $true proves the SPN chain works end-to-end; AuthScheme NTLM from a remote client means Kerberos was not negotiated (missing/wrong SPN, or the client fell back).
Example 2
Test-SqlSpnKerberosAuth -ServerInstance 'SQLFCI01.corp.example.com'The DR-558/DR-313 exit check for an FCI: after registering Engine SPNs on the service account, a remote run returning AuthScheme KERBEROS is the proof that the fix holds on a live domain.
Example 3
Test-SqlSpnKerberosAuth -ServerInstance 'localhost'Returns IsLocalCaveat $true with a warning: local connections use NTLM by design, so this result cannot prove or disprove the SPN configuration.