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

Summary

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

Requirements

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

References

For more information about the encryption hierarchy in SQL Server 2005, see the following topics in SQL Server 2005 Books Online:
  • "Encryption hierarchy"
  • "Choosing an encryption algorithm"
  • "Security considerations for databases and database applications"
  • "CREATE CERTIFICATE (Transact-SQL)"
  • "CREATE SYMMETRIC KEY (Transact-SQL)"
For more information about how to encrypt a column of data in SQL Server 2005, visit the following MSDN Web site:For more information, search the following Microsoft Web site for "Active Directory Service Interfaces," for "Active Directory," and for "Visual Basic Script":

Properties

Article ID: 319716 - Last Review: Mar 19, 2009 - Revision: 1

Feedback