When SQL Server is configured for Active Directory (AD) authentication within a domain environment, users typically authenticate seamlessly through Windows Authentication. However, this becomes problematic when users need to connect from machines outside the domain perimeter. The certificate trust issue you're encountering is a common roadblock in such hybrid scenarios.

The root cause stems from SQL Server's encryption requirements. For Active Directory password authentication to work externally:

-- Check current certificate configuration
SELECT name, subject, expiry_date 
FROM sys.certificates 
WHERE name LIKE '##MS_Certificate%';

You'll need to properly configure your certificate chain. For development environments, you can temporarily bypass strict validation:

// C# connection string with certificate trust override
Server=myServerAddress;Database=myDataBase;
Authentication=Active Directory Password;
User Id=myDomain\\myUsername;Password=myPassword;
TrustServerCertificate=True;

For production environments, consider these robust approaches:

1. Deploy a publicly trusted certificate for your SQL Server instance
2. Configure conditional access policies in Azure AD (for hybrid environments)
3. Set up Active Directory Federation Services (ADFS)

Here are proper connection strings for different scenarios:

// Standard integrated authentication (domain-joined)
"Server=sqlserver.domain.com;Integrated Security=True;"

// AD Password auth for external clients
"Server=sqlserver.domain.com;Authentication=Active Directory Password;
UID=user@domain.com;PWD=password;Encrypt=True;TrustServerCertificate=False;"

// Using service principal
"Server=sqlserver.domain.com;Authentication=Active Directory Service Principal;
User ID=application-id@tenant-id;Password=app-secret;"

Ensure these ports are open for external authentication:

• TCP 1433 (SQL default)
• TCP 443 (for Azure AD token acquisition)
• UDP 88 (Kerberos authentication)

When authentication fails, check these diagnostic commands:

-- Check successful logins
SELECT login_name, COUNT(*) as session_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name;

-- Review failed attempts
SELECT event_time, server_principal_name, error_number
FROM sys.fn_get_audit_file('path_to_audit_file', DEFAULT, DEFAULT);

When configuring SQL Server authentication with Active Directory (AD) in enterprise environments, we typically create logins mapped to AD groups as follows:

-- Create login from AD group
CREATE LOGIN [DOMAIN\SQLUsers] FROM WINDOWS;
GO

-- Grant server role
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\SQLUsers];
GO

This works perfectly for domain-joined machines where users authenticate via Windows Authentication. However, when users attempt to connect from non-domain joined clients using Active Directory Password Authentication, they encounter certificate trust issues:

Error: 0 - The certificate chain was issued by an authority that is not trusted

The root cause stems from SQL Server's encrypted connections requiring proper certificate validation. Here are the essential configuration steps:

-- On SQL Server:
-- 1. Obtain a valid SSL certificate from your enterprise CA
-- 2. Bind it to SQL Server using Configuration Manager
-- 3. Ensure the certificate chain is trusted by clients

-- Alternative quick fix (not recommended for production):
-- Add TrustServerCertificate=True to your connection string
Server=myServerAddress;Database=myDataBase;
Authentication=Active Directory Password;
User ID=user@domain.com;Password=myPassword;
TrustServerCertificate=True;

For secure cross-domain authentication, implement these solutions:

1. Configure Azure AD Authentication (hybrid mode):
   - Register your SQL Server in Azure AD
   - Enable both Windows and Azure AD authentication

2. Set up AD FS (Active Directory Federation Services):
   - Establish trust relationship between domains
   - Configure relying party trust for SQL Server

3. Client-side certificate installation:
   - Export your enterprise root CA certificate
   - Install it in the Trusted Root Certification Authorities store
   on client machines

For various authentication scenarios:

// Azure AD Integrated Authentication
"Server=server.database.windows.net;Database=myDB;
 Authentication=Active Directory Integrated;"

// AD Password Authentication with proper cert handling  
"Server=myserver;Database=myDB;
 Authentication=Active Directory Password;
 User ID=user@domain.com;Password=myPassword;
 Encrypt=True;TrustServerCertificate=False;"

// Using ADAL for token-based authentication
var authContext = new AuthenticationContext("https://login.microsoftonline.com/tenantID");
var result = authContext.AcquireTokenAsync("https://database.windows.net/",
    "clientID", 
    new UserCredential("user@domain.com", "password")).Result;

When implementing this configuration, watch for these specific problems:

• Ensure TCP/IP protocol is enabled in SQL Server Configuration Manager
• Verify the SQL Server service account has proper permissions in AD
• Check firewall rules for port 1433 (default) or your custom port
• Confirm DNS resolution works correctly for both forward and reverse lookups
• Validate SPN (Service Principal Name) registration for the SQL Server service account