How to populate a SQL Server 2005 database by using information from Active Directory (319716)

SUMMARY

This step-by-step article describes how to populate a Microsoft SQL Server 2005 database by using information from Active Directory.

Requirements

• Install SQL Server 2005. For more information about SQL Server 2005, visit the following Microsoft Web site:

  http://www.microsoft.com/sql

• Install Microsoft Windows 2000 Server or Windows Server 2003 and enable Active Directory.
  
  For more information about Active Directory in Windows 2000 Server, visit the following Microsoft Web site:

  http://www.microsoft.com/technet/prodtechnol/windows2000serv/technologies/activedirectory/default.mspx

  For more information about Active Directory in Windows Server 2003, visit the following Microsoft Web site:

  http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/directory/activedirectory/stepbystep/default.mspx

SQL Server database table

For security considerations, we recommend that you encrypt the data when you obtain the Active Directory information. To encrypt the data, we recommend that you use the key management infrastructure in SQL Server 2005. SQL Server 2005 provides the following mechanisms for encryption:

• Certificates
• Asymmetric keys
• Symmetric keys

For more information about encryption hierarchy in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site:The following sample code demonstrates how to create a table that contains a column of encrypted data by using symmetric encryption. The sample code in this article inserts user information in a table that is named Employee in a database that is named Employees.

Note Using Advanced Encryption Standard (AES) encryption for a symmetric key is not supported on Microsoft Windows XP or on Windows 2000 Server. Before you run the sample code on a system that does not support AES encryption, you should change the text AES_256 in the code to DES.

USE master;
GO
IF DB_ID (N'Employees') IS NOT NULL
DROP DATABASE Employees;
GO
CREATE DATABASE Employees;
GO

USE  Employees;
GO
CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'YourPassword'

CREATE CERTIFICATE ActiveDirectoryInfo
   WITH SUBJECT = 'Active Directory information';
GO

CREATE SYMMETRIC KEY SKey_AD

    WITH ALGORITHM = AES_256

    ENCRYPTION BY CERTIFICATE ActiveDirectoryInfo;

GO

CREATE TABLE [Employee] (
	[UserId] [int] IDENTITY (1, 1) NOT NULL ,
	[Username] [varbinary] (128),
	[FullName] [nvarchar] (1000),
	[Description] [nvarchar] (1000),
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE PROCEDURE sp_adinfo
@Username nvarchar(128),
@Fullname nvarchar(1000),
@Description nvarchar(1000)
AS
BEGIN
	OPEN SYMMETRIC KEY SKey_AD
	DECRYPTION BY CERTIFICATE ActiveDirectoryInfo;

	INSERT INTO [Employee] ([Username], [FullName], [Description]) 
	VALUES(EncryptByKey(Key_GUID('SKey_AD'), @Username)
	,@Fullname,	@Description)
END
GO

Create the Microsoft Visual Basic script by using Active Directory Service Interfaces

1. In a text editor such as Notepad, type the following script:

   Option Explicit
   
   Dim sDomain
   Dim oDomain
   Dim sFilter
   Dim oADobject 
   Dim MyConnection
   Dim MyCommand
   Dim param1
   Dim param2
   Dim param3
   
   sDomain 	= "some_domain"
   sFilter 	= "User"
   
   'Connect to the domain.
   Set oDomain 	= GetObject("WinNT://" & sDomain)
   oDomain.Filter 	= Array( sFilter )
   
   Set MyConnection = CreateObject("ADODB.Connection")
   'The following is the SQL connection string.
   MyConnection.Open "Driver={SQL Server};server=(local);database=Employees;uid=some_username;pwd=some_password;"
   
   Set MyCommand = CreateObject("ADODB.Command")
   Set MyCommand.ActiveConnection = MyConnection
   
   Set param1 = MyCommand.CreateParameter("@Username", 202, 1, 128)
   Set param2 = MyCommand.CreateParameter("@Fullname", 202, 1, 1000)
   Set param3 = MyCommand.CreateParameter("@Description", 202, 1, 1000)
   
   MyCommand.CommandText = "sp_adinfo"
   MyCommand.CommandType = 4
   
   MyCommand.Parameters.Append param1
   MyCommand.Parameters.Append param2
   MyCommand.Parameters.Append param3
   
   
   For Each oADobject In oDomain
   	param1.Value = oADobject.Name
   	param2.Value = oADobject.FullName
   	param3.Value = oADobject.Description
      	MyCommand.Execute
   Next
   
   MyConnection.Close()
   

   This script connects to a domain that is named "some domain" to obtain user information. Then, this script inserts the user information in a table that is named Employee. This table is located in a database that is named Employees. Modify the Active Directory domain name properties and the SQL connection string that is set by the MyConnection object.
2. Save the file as AdSqlUsers.vbs.
3. Double-click AdSqlUsers.vbs to run the file.
4. The Visual Basic script will execute, and the SQL Server database will be populated with the Active Directory information.

Verify the SQL Server data

1. Open Microsoft SQL Server Management Studio.
2. In Object Explorer, locate the Employees database that contains the Employee table, and then click New Query in the toolbar.
3. In the editor window, run the following Transact-SQL statement to see the encrypted data:

   SELECT * from Employee
   					

   The query returns encrypted data that contains the Active Directory user name.
4. To see the decrypted data, run the following Transact-SQL statements:

   OPEN SYMMETRIC KEY SKey_AD
      DECRYPTION BY CERTIFICATE ActiveDirectoryInfo;
   GO
   
   SELECT
   CONVERT(nvarchar, DecryptByKey([Username])) 
   AS 'Decrypted Username',
   [FullName], [Description] from [Employee]
   GO