Co-author: Krishnababu Polanati

Windows Authentication:
Windows Authentication is used to verify that the information comes from a trusted source, whether from a person or computer object, such as another computer.

SQL Server supports two authentication modes, Windows authentication mode and mixed mode. Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. 

Why is Windows authentication the preferred authentication method?

By centralizing user authentication via Active Directory, Windows Authentication makes IT administration easier. With only one set of login credentials, administrators can now manage access to various apps such as Microsoft 365, SQL Server, or other applications you can enable with third-party software.

Windows authentication in SQL Server allows users to connect to the database using their existing Windows credentials, leveraging the operating system's security mechanisms for authentication. It's the default and recommended mode because it's more secure and simplifies user management compared to SQL Server authentication

Here’s to a more detailed step-by-step guide on configuring Windows Authentication for SQL Server.

Step-by-Step Guide: Configuring Windows Authentication for SQL Server:

Windows Authentication is a secure and seamless way to connect SQL Server to Active Directory (AD) environments. In this guide, you’ll learn how to:

• Install Active Directory Domain Services (AD DS) and Remote Server Administration Tools (RSAT).

• Configure AD DS and promote your server to a domain controller.

• Set up SQL Server for Windows Authentication.

Part 1: Install AD DS and RSAT

1. Open Server Manager

• Press Win + R, type servermanager, and press Enter.

• Or open Start Menu, search for Server Manager, and launch it.

2. Add Roles and Features

• In Server Manager, click Add roles and features from the dashboard or Manage menu.

3. Follow the Wizard

• Click Next until you reach the Select Installation Type page.

• Choose Role-based or feature-based installation, then click Next.

4. Select Destination Server

• Select your local server (or the target server) and click Next.

5. Add Server Roles

• In Server Roles, locate Active Directory Domain Services (AD DS).

• Check the box. When prompted, click Add Features to include required components.

• Click Next.

6. Add Features

• In the Features section, ensure Remote Server Administration Tools (RSAT) is selected.

• Expand RSAT → Role Administration Tools.

• Ensure AD DS and AD LDS Tools is checked (includes Active Directory Users and Computers).

7. Confirm and Install

• Review your selections, click Next, and then click Install.

• Wait for the installation to complete.

8. Verify Installation

• In Server Manager, go to the Tools menu and verify that Active Directory Users and Computers is available.

Part 2: Configure AD DS and Promote the Server

After installing AD DS, configure the server as a domain controller:

1. Open AD DS Configuration Wizard

• In Server Manager, a yellow flag notification appears. Click it and choose Promote this server to a domain controller.

2. Select Deployment Configuration

• Choose one of the following:

• Add a domain controller to an existing domain

• Add a new domain to an existing forest

• For a new setup, select Add a new forest, specify a Root domain name (e.g., example.com), and click Next.

3. Configure Domain Controller Options

• Set Forest functional level and Domain functional level (choose latest unless legacy compatibility is required).

• Leave DNS server and Global Catalog (GC) selected.

• Set a Directory Services Restore Mode (DSRM) password.

4. Additional Options

• Ignore the DNS delegation warning (normal for the first DNS server).

5. Set Paths

• Accept defaults (C:\Windows\NTDS) or choose custom locations. Click Next.

6. Review and Install

• Review the settings, click Next, and run the prerequisites check.

• Resolve any issues or accept warnings, then click Install.

7. Reboot and Verify

• The server reboots after promotion.

• Log in with the domain administrator account.

• In Server Manager, go to Tools → Active Directory Users and Computers to verify.

• Optionally, verify DNS using DNS Manager.

Part 3: Configure SQL Server for Windows Authentication

Pre-Requisites

• Ensure SQL Server is installed and running under a domain account (Administrator).

Steps

1. Join the SQL Server Machine to the Domain

• On the SQL Server machine:

• Right-click This PC → Properties → Change settings.

• Click Change, set Member of domain, and enter your domain (e.g., example.com).

• Enter domain credentials when prompted and reboot the server.

2. Enable Windows Authentication Mode in SQL Server

• Open SQL Server Management Studio (SSMS) and connect.

• Right-click the server → Properties → Security.

• Select Windows Authentication mode if not already selected, click OK, and restart SQL Server.

3. Verify Domain Account in SQL Server

• In SSMS, go to Security → Logins.

• Verify the Administrator instance is listed.

• Right-click Administrator → Properties:

• Under Server Roles, keep defaults.

• Under User Mapping, ensure db_owner and public are selected. Click OK.

4. Configure Firewall

• Ensure port 1433 is open in Windows Firewall for SQL Server communication.

5. Test the Configuration

• From a client machine joined to the domain:

• Open SSMS and connect using Windows Authentication.

How to Join a Windows VM to Active Directory Domain ?

Joining a Windows VM to an Active Directory (AD) domain requires some essential configurations to ensure seamless integration. This guide walks you through all prerequisites, step-by-step instructions, and how to handle common issues like duplicate SIDs and Sysprep preparation.

Prerequisites:

Before starting, ensure the following requirements are met:

• Static IP Address
  Configure your Windows VM to use a static IP address or ensure it has a DHCP reservation.

• Network Connectivity
  Confirm the Windows VM can communicate with the Domain Controller (DC).

• DNS Configuration
  Set the Preferred DNS on the Windows VM to the IP address of the Active Directory DNS server.

• Time Synchronization
  Verify that the VM’s time settings are synchronized with the Domain Controller.

• Domain Administrator Account
  You’ll need credentials for a domain administrator account to authenticate during the join process.

Step-by-Step Process

1. Set DNS to the Domain Controller

1. Open Control Panel → Network and Internet → Network and Sharing Center.

2. Click Change adapter settings.

3. Right-click your active network adapter and select Properties.

4. Highlight Internet Protocol Version 4 (TCP/IPv4) and click Properties.

5. Under Preferred DNS server, enter the IP address of your AD Domain Controller.

6. Click OK and close the settings.

2. Change Workgroup to Domain

1. Open Server Manager and navigate to the Local Server tab.

2. In the Properties section, click Workgroup.

3. In the System Properties window, click Change.

4. In the Computer Name/Domain Changes window:

• Select Domain and enter your domain name (e.g., p8domain.com).

5. Click OK.

3. Resolve Duplicate SID and Sysprep Issues

When adding the VM to the domain, you may encounter an error related to Duplicate SID or Sysprep Problems. Follow these steps to resolve:

a) Raise a Ticket

• Contact the IBM DevIT team for assistance.

• Reference ticket: https://oneticket.devit.ibm.com/tickets/

• Additional details: https://w3.ibm.com/w3publisher/devit/faqs/dcs-in-fyre

b) Prepare the VM

• Run this command in Windows PowerShell:

  pgsql

  CopyEdit

  Get-AppxPackage -Name Microsoft.MicrosoftEdge.Stable | Remove-AppxPackage

c) Execute Sysprep

• Open Command Prompt and run:

  bash

  CopyEdit

  cd %windir%\System32\Sysprep sysprep /generalize /oobe /reboot

d) Rename the Computer

After running Sysprep, the computer name may reset to a random value.

• Rename it back to the original name if prompted and restart the VM.

Tip: Create a VM snapshot before running Sysprep to allow easy rollback if needed.

4. Authenticate with Domain Admin

1. When joining the domain, a Windows Security prompt appears.

2. Enter the credentials as:

   pgsql

   CopyEdit

   DOMAIN\Administrator

   (or another domain admin account) and password.

3. Click OK to join the domain.

Important Notes

• The domain join process automatically registers the VM as a computer object in Active Directory. You don’t need to create this manually.

• If you want the computer object in a specific Organizational Unit (OU), specify it during the join or move it later using Active Directory Users and Computers.

• Once this process is complete, do not use the system to create a stencil.

To better understand the usage, let’s look at how Windows Authentication can be configured for Microsoft SQL Server using the Content Platform Engine (CPE) Swing CMUI.

1. Once you have created a CMUI profile, navigate to the Configure GCD JDBC DataSource task and select Microsoft JDBC Driver from the JDBC Driver Name dropdown.

   

2. After selecting the Microsoft JDBC Driver, an SQL task is created with the corresponding fields, as shown in the image below. A checkbox named Enable Windows Authentication for MSSQL Server will be visible. When this checkbox is selected, the Database username and Database password fields are disabled.

   

    

3. Enter the required SQL database details in the respective fields and click the Test Connection button to verify the connection between the SQL database and the application server (WAS).

4. To create the SQL database datasource in the application server, click the Run Task button. The console below will display the result. Once the task runs successfully, verify the created datasource through the WebSphere Application Server Console to confirm the connection.

   

5. To connect to a remote SQL Server, provide the corresponding database server name in the Database Server Name field.

Key Takeaways:

• Windows Authentication is the default and recommended mode in SQL Server because it integrates tightly with Active Directory.

• It eliminates the need to manage multiple sets of credentials, simplifying administration.

• Security is enhanced by relying on trusted Windows user accounts and centralized policies.

• SQL Server and applications (like the CPE Swing CMUI) can connect seamlessly without requiring database usernames or passwords.

• Once configured, it provides a secure, scalable, and user-friendly authentication model suitable for enterprise environments.