Home / server / Questions / 177964
Accepted
Eldergriffon
Asked: 2010-09-04 17:20:41 +0800 CST

Querying the Active Directory domain of a Windows 2008 host in SQL

  • 772

There is code in our shop that must query a SQL Server 2008 server, determine the Active Directory domain that the host belongs to, and, in SQL, create Windows login principals based on this information. Under Windows 2003 server, it was possible to query the domain's name through SQL Server like so:

DECLARE @Domain nvarchar(255) 
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon', N'CachePrimaryDomain',@Domain OUTPUT 

SELECT @Domain AS Domain

However, this no longer works in Windows 2008 ('CachePrimaryDomain' registry key doesn't exist anymore). Anyone know if there is a registry key that reliably reports the Active Directory domain a Windows 2008 server belongs to? Better yet, is there an entirely different way of handling this that makes more sense? Thanks.

windows-server-2008 sql-server active-directory

2 Answers

  1. Best Answer

    First be sure the machine is on a domain and not part of a workgroup.

    Then you can find the "Domain" key here:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

    You may need to use T-SQL string functions SUBSTRING and CHARINDEX if you are only looking for the left half of the domain before the '.'

    If you are looking for another way to do this without the registry, consider a SQLCLR project or potentially a PowerShell script that uses the Domain.GetComputerDomain() .NET method.

    • 2

  2. Thanks for your help, anyone interested can use the following.

    DECLARE @strDomain  VARCHAR(255),  
  @strServer  VARCHAR(255),  
  @strSubject  VARCHAR(255)  

EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',   
   'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters',   
   'Domain',   
   @strDomain output 

   SELECT @strDomain

   select LEFT(@strDomain,charindex('.',@strDomain,1)-1)
    • 1