Microsoft KB Archive/319716

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

Article ID: 319716

Article Last Modified on 3/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q319716





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:

http://msdn2.microsoft.com/en-us/library/ms189586.aspx

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
  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    = &quot;some_domain&quot; sFilter    = &quot;User&quot;

'Connect to the domain. Set oDomain    = GetObject(&quot;WinNT://&quot; & sDomain) oDomain.Filter = Array( sFilter )

Set MyConnection = CreateObject(&quot;ADODB.Connection&quot;) 'The following is the SQL connection string. MyConnection.Open &quot;Driver={SQL Server};server=(local);database=Employees;uid=some_username;pwd=some_password;&quot;

Set MyCommand = CreateObject(&quot;ADODB.Command&quot;) Set MyCommand.ActiveConnection = MyConnection

Set param1 = MyCommand.CreateParameter(&quot;@Username&quot;, 202, 1, 128) Set param2 = MyCommand.CreateParameter(&quot;@Fullname&quot;, 202, 1, 1000) Set param3 = MyCommand.CreateParameter(&quot;@Description&quot;, 202, 1, 1000)

MyCommand.CommandText = &quot;sp_adinfo&quot; 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 &quot;some domain&quot; 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.  Save the file as AdSqlUsers.vbs. Double-click AdSqlUsers.vbs to run the file. The Visual Basic script will execute, and the SQL Server database will be populated with the Active Directory information.</li></ol>

Verify the SQL Server data
<ol> Open Microsoft SQL Server Management Studio.</li> In Object Explorer, locate the Employees database that contains the Employee table, and then click New Query in the toolbar.</li>  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. </li>  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 </li></ol>

<div class="references_section">