SQL Server DBA's Handbook: Navigating Active Directory Integration and Security

Edition: Saturday, February 3rd, 2024

In today's issue, I will show how to perform a simple installation of Active Directory, cover basic concepts, and explain why it's crucial to a DBA.  

For DBAs, understanding how SQL Server integrates with Active Directory is important for ensuring secure, efficient access management and authentication processes.  Active Directory enables better security and streamlined administration vs SQL Server authentication alone. 

Learning the concepts will allow more effective communication between you, the DBA, and your systems administrators within your organization.

The problem is that many DBAs lack foundational knowledge of Active Directory; this gap can lead to inefficient management practices, compromised data integrity, and increased operational risks.  You may ask, "With the advent of the cloud and Azure's Microsoft Entra, is Active Directory still relevant?"  Absolutely. 

Active Directory has been prevalent in 100% of the environments I've worked in and isn't going away soon.  I'd recommend learning both.

Understanding and implementing Active Directory with SQL Server is not just an administrative necessity but a strategic advantage in managing databases securely and efficiently.

Here's what we'll cover today:

• Key concepts of Active Directory (AD) and its importance for SQL Server.

• Step-by-step guide to integrating SQL Server with Active Directory.

• Managing user access and permissions effectively through Active Directory

• Troubleshooting Service Principal Names

 Let's get started.  

Key Concepts:

Active Directory (AD) is the backbone for managing user identities and securing access to network resources, including SQL Server databases.  It enables DBAs to leverage centralized authentication, simplify user management, and apply robust security policies across SQL Server environments.  

1. Domains: Domains are administrative units in Active Directory, which can include a collection of users, groups, and computers. For SQL Server, being part of a domain facilitates centralized management of user access and security policies, ensuring consistent security and access controls across the SQL Server environment.
2. Domain Controllers (DC): These servers manage all security-related aspects of user-domain interactions, including authentication and authorization. For SQL Server, this means that database access and permissions can be controlled at the domain level, enhancing security and simplifying management.
3. Organizational Units (OUs): OUs provide a way to organize domain objects into logical groups. In the context of SQL Server, OUs can be used to group and manage database servers and users based on different business units or departments, making administration more streamlined.
4. Authentication Protocols (Kerberos/NTLM): AD uses Kerberos as the primary authentication protocol, with NTLM for backward compatibility. SQL Server integrated with AD uses these protocols for secure authentication, ensuring that database access is secure and authenticated.
5. Users and Groups: Active Directory allows the creation of user accounts and groups. SQL Server leverages this capability for user authentication and authorization. Groups can be used to assign database permissions to multiple users simultaneously, enhancing security and reducing administrative overhead.
6. Service Principal Name (SPN): A service principal name is a unique identifier for a service instance.  SPNs are used in Kerberos authentication to associate a service instance with a service logon account.  This association is necessary for Kerberos to perform its role in mutual authentication, ensuring that both the user and the service verify their identities to each other.  A SPN for SQL Server might look like 'MSSQLSvc/mydbserver.mydomain.com:1433'.  For Kerberos to work correctly, SPNs must be set up for the SQL Server service account.  We'll cover common issues with SPNs in the Troubleshooting section.
7. Group Policy Objects (GPOs): GPOs in Active Directory enable centralized configuration and management of policies across networked computers. For SQL Server, GPOs can enforce security settings, configure password policies, and control other aspects of the server environment, contributing to the overall security and compliance.  GPOs can be used to maintain password length and history compliance within SQL Server.
8. Trusts: Trust relationships in AD allow users in one domain to access resources in another domain. In SQL Server, this facilitates cross-domain authentication, enabling users from different parts of the organization to access databases securely based on trust relationships.
9. Global Catalog: It’s a distributed data repository in AD containing information about every object in the domain. For SQL Server, users and resources can be efficiently located and managed across multiple domains, improving scalability and performance.
10. Forest: A forest is a collection of one or more domains in AD. For SQL Server, a forest provides a security boundary and a scope for replication. It allows SQL Server instances in different domains to be managed under a common AD configuration.
11. Schema: The AD schema defines objects and attributes stored in the directory. For SQL Server, custom schema extensions can be used for specialized configurations or integration requirements.
12. LDAP (Lightweight Directory Access Protocol): LDAP is used by AD to communicate. SQL Server can use LDAP to query AD information, like validating user credentials or retrieving group membership details.

Active Directory's role in managing SQL Server involves centralized control over user access, security policies, and administrative tasks.  

Step-by-Step Guide: Integrating SQL Server with Active Directory:

Integrating SQL Server with Active Directory (AD) mainly involves configuring your SQL Server instance to use Windows Authentication (the default), which leverages Active Directory for user authentication and management.  Let's review each step.

Prepare Your Environment:

You can gain hands-on experience by trying these steps out in your sandbox.  Check out this DEMO for a walk-through of the steps below.

• Ensure AD Setup: Confirm that Active Directory is properly set up and that your SQL Server machine is joined to the domain.  
• Check Permissions: Ensure you have administrative privileges on the SQL Server and adequate permissions in Active Directory to perform configurations.

Configure SQL Server to Use Windows Authentication:

SQL Server uses Windows Authentication by default.  This authentication method cannot be disabled. However, look at the steps below for how you can verify this and enable Mixed Mode if required.

• Open SQL Server Management Studio (SSMS): Connect to your SQL Server instance.
• Server Properties: Right-click the server name in Object Explorer, then select "Properties."
• Set Authentication Mode: Go to the "Security" page.  The default is Windows Authentication Mode.  You also have the option to enable Mixed Mode (SQL Server authentication and Windows authentication).  No changes are required here.

Create and Organize AD User Accounts and Groups:

• In Active Directory: Use Active Directory Users and Computers to create user accounts for individuals who need access to SQL Server.
• Create Groups: Organize these users into groups based on their roles and access requirements to SQL Server databases (e.g., sysadmins, readers).

Map AD Users and Groups to SQL Server Logins:

• You can use the GUI here but I prefer to use the following transact sql.
  • USE MASTER;
GO;
    CREATE LOGIN [YourDomain\DomainAccountOrGroup] FROM WINDOWS;
    GO;
• Add the login to the appropriate server role.

Assign Database-Level Permissions:

•  Here again, you can use the GUI but I prefer to use transact sql.  In this example, we'll create a database user from the login we created above and add it to the db_datareader database role.
  • USE YourExampleDatabase;
    GO;
    CREATE USER [YourDomain\DomainAccountOrGroup] FROM LOGIN [YourDomain\DomainAccountOrGroup];
    GO;
    ALTER ROLE db_datareader ADD MEMBER [YourDomain\DomainAccountOrGroup];
    GO;

Testing Authentication:

• Test with an AD Account: Log into a machine with an AD account that has been granted access.  Use SSMS or another SQL client tool to connect to the SQL Server instance using Windows Authentication.  Verify the connection and accessible databases.

Configure Advanced Options

• Implementing SPNs for Kerberos: If using Kerberos (recommended), configure Service Principal Names (SPNs) for the SQL Server service account, which is especially important in multi-server environments.  It's common that you, the DBA, won't have access to this, and it will require you to work with the domain admin to create these.  However, in the video, we'll set up a SPN in your sandbox.

Regular Maintenance and Auditing

• Review AD Configurations: Regularly check AD user accounts, groups, and permissions for changes in access requirements.
• Monitor SQL Server Error Logs: Monitor authentication and error logs for potential issues.

Troubleshooting:

Service Principal Names (SPNs)

A common issue with SQL Server is the following errors reported within the SQL Server error log when using an AD account as the SQL Server service account.  

• SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server Service.
• Kerberos authentication will not be possible until a SPN is registered for the SQL Server Service.
• The SQL Server Network Interface Library could not register the Service Principal Name (SPN).

Another common error, usually encountered on the client side, is "Target principal name is incorrect." 

Usually, this can be remedied by manually adding the SPN using the setspn -S command.  Within the demo, I walk through this common issue and how to correct it.  Keep in mind though, this will likely need to be performed by a domain admin.  

Conclusion:

If you want to dive deeper into Active Directory, check out the YouTube videos below. 

Learn Microsoft Active Directory in 30mins (Andy Malone)

Active Directory Domain Service Deep Dive (John Savill)

If you haven't done so, I invite you to join the weekly DBA Challenges newsletter -  DBA Challenges - Newsletter (automatesql.com)

That's it for today.  Look for more daily DBA Challenges on LinkedIn.  Have a great week.

Thanks!