Installing SQL Server Without Active Directory: Authentication, Security and Workgroup Availability Groups

Most SQL Server installations happen inside an Active Directory domain, but not every environment fits that. Servers in a DMZ, OT and plant-floor networks, small branches, standalone cloud VMs, or systems deliberately kept out of the corporate domain for security reasons can run on a workgroup server. SQL Server installs and runs fine there too, but some things work differently around authentication, service accounts and high availability.

This article covers what to watch for when installing SQL Server on a server without a domain.

Installation Differences

Service accounts

Domain accounts and group managed service accounts (gMSA) aren’t available; if your servers are domain members, our gMSA setup guide is the better path. Use the virtual accounts that setup suggests by default instead (NT Service\MSSQLSERVER, NT Service\SQLSERVERAGENT). Virtual accounts are managed locally, need no password and give each service its own identity. If the service needs to reach a network share (for example a backup folder), consider the options in the authentication section below.

Authentication mode

Without a domain, having users on other machines connect with their Windows identity isn’t practical. So Mixed Mode is usually needed:

• Create separate SQL logins for applications, with strong passwords and the password policy enforced.
• Disable the sa account; use a separate login with a name that’s hard to guess for administration.
• Administrators who log on to the server itself can keep using Windows Authentication through local Windows groups.

No Kerberos, only NTLM

Without a domain, Kerberos authentication isn’t available. In practice that means:

• Passing a Windows identity from one server to another (the double hop) doesn’t work; connecting with the user’s identity through linked servers or middle tiers such as SSRS isn’t possible. Use explicit login mappings for linked servers in these scenarios. See our linked server guide.
• As an old workaround, local Windows accounts created with the same name and password on two machines (pass-through authentication) can be used; but keeping the passwords in sync and audited is hard, so it’s not recommended as a permanent solution.

Security Hardening

Servers outside a domain often sit in riskier network zones, so hardening matters even more:

• Open the SQL Server port in the firewall only to the source IPs that need it; use a static port and turn off SQL Server Browser.
• Limit local administrator accounts; since there’s no Group Policy, configure local security policies (password policy, user rights) by hand and document them.
• Monitor failed login attempts and forward them to a SIEM. See our SQL Server Audit guide.
• Send backups off the server, to a target reached with separate credentials.

High Availability: Domain-Independent Availability Groups

From Windows Server 2016 and SQL Server 2016, Windows Server Failover Clusters can be built without a domain (workgroup clusters), and Always On Availability Groups can run on them. In this setup:

• Nodes must share the same DNS suffix and be able to resolve each other’s names.
• The cluster is built with local administrator accounts that have the same name and password on each node.
• Authentication between database mirroring endpoints uses certificates instead of Windows.

-- On each replica: create a certificate for the endpoint and give it to the other replica
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<strong password>';
CREATE CERTIFICATE AG_Node1_Cert WITH SUBJECT = N'AG endpoint certificate - Node1';
BACKUP CERTIFICATE AG_Node1_Cert TO FILE = N'C:\Certs\AG_Node1_Cert.cer';

CREATE ENDPOINT Hadr_endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE AG_Node1_Cert, ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

The other node’s certificate is imported on each replica, and permission to connect to the endpoint is granted to a login mapped to that certificate. A failover cluster instance (FCI), on the other hand, isn’t used in this model because it requires shared storage and domain-based cluster resources. See our Always On guide for the options.

Checklist

• Services run under virtual accounts.
• If Mixed Mode is used, the password policy is enforced and sa is disabled.
• Connection encryption is configured with a trusted certificate.
• The firewall allows only the needed IPs and a static port.
• Local security policies are configured by hand and documented.
• If high availability is needed, a certificate-based, domain-independent AG is planned.