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

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.

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.