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

Back to the top

Requirements

• Install SQL Server 2005. For more information about SQL Server 2005, visit the following Microsoft Web site:
  http://www.microsoft.com/sql (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://technet.microsoft.com/en-us/windowsserver/2000/bb735360.aspx (http://technet.microsoft.com/en-us/windowsserver/2000/bb735360.aspx)
  For more information about Active Directory in Windows Server 2003, visit the following Microsoft Web site:
  http://www.microsoft.com/windowsserver2003/technologies/directory/activedirectory/default.mspx (http://www.microsoft.com/windowsserver2003/technologies/directory/activedirectory/default.mspx)

Back to the top

SQL Server database table

• Certificates
• Asymmetric keys
• Symmetric keys

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

Back to the top

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.

Back to the top

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
   

Back to the top

• "Encryption hierarchy"
• "Choosing an encryption algorithm"
• "Security considerations for databases and database applications"
• "CREATE CERTIFICATE (Transact-SQL)"
• "CREATE SYMMETRIC KEY (Transact-SQL)"

Back to the top