Querying Active Directory Through Oracle

It’s usually easy to tell when a muskie fisherman in another boat sees a good sized fish.  He’ll usually use the trolling motor to slowly move around a small area, picking it apart from different angles with his casts.  He may stay in an area of 100 square yards for a couple hours, trying to find the right presentation and angle to get the follower to bite.  Seeing a big fish follow your bait to the boat is exciting, but if you want to catch more of these followers, you need to be ready to change go at them with something different.  If bucktail gets them to follow lazily, try a jerkbait.  If casting from shallow water brings them in too late, try casting from the deep.

In my previous post, I showed how to query Active Directory from SQL Server.  This was nice and easy to do; since they’re both Microsoft products, they integrate really easily.  However, what if you need to do it in Oracle?  Fortunately, Oracle realized this would be a need and included a package called DBMS_LDAP to help us get at this information.  Here’s how to use this package to retrieve data from Active Directory into Oracle:

The first step is to add a table to hold the data.  I created a table called ACTIVEDIRECTORY:

CREATE TABLE "ACTIVEDIRECTORY"
  (
    "DISPLAYNAME"VARCHAR2(255 BYTE),
    "EMAIL"      VARCHAR2(255 BYTE),
    "EXTENSION"  VARCHAR2(10 BYTE),
    "DIRECTDIAL" VARCHAR2(25 BYTE),
    "MOBILE"     VARCHAR2(25 BYTE),
    "RTYPE"      CHAR(1 BYTE)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "TS_TABLESPACE_PD";
COMMENT ON COLUMN "ACTIVEDIRECTORY""RTYPE"
IS
  'E=Employee, R=Room, G=Group, V=Vendor';
  ALTER TABLE "ACTIVEDIRECTORY"MODIFY ("RTYPE"NOT NULL ENABLE);
  ALTER TABLE "ACTIVEDIRECTORY"MODIFY ("DISPLAYNAME"NOT NULL ENABLE);

Nothing too special about this table, I am going to be capturing the Displayname, Email, Extension, DirectDial, and Mobile phone numbers from Active Directory. I create a Type field that I am going to be manually adding to differentiate the type of records there. All rows should have a Displayname and a Type.

Next I need to create a stored procedure which will capture the data and insert it into the table.  I had to Google some examples to get the code working.  The most helpful example I found was at Oracle-Base.

CREATE OR REPLACE PROCEDURE Refresh_ActiveDirectory
IS
  l_ldap_host   VARCHAR2(256) := 'domaincontroller.domain.com';
  l_ldap_port   VARCHAR2(256) := '389'; --Default
  l_ldap_user   VARCHAR2(256) := domain\username;
  l_ldap_passwd VARCHAR2(256) := ********;
  l_ldap_base   VARCHAR2(256) := 'OU=Users,OU=ougroup,dc=dcgroup,dc=com';
  dispname      VARCHAR2(255);
  email         VARCHAR2(255);
  Extension     VARCHAR2(10);
  DirectDial    VARCHAR2(25);
  Mobile        VARCHAR2(25);
  RType         CHAR(1) := 'E';
  l_retval PLS_INTEGER;
  l_session DBMS_LDAP.session;
  l_attrs DBMS_LDAP.string_collection;
  l_message DBMS_LDAP.MESSAGE;
  l_entry DBMS_LDAP.MESSAGE;
  l_attr_name VARCHAR2(256);
  l_ber_element DBMS_LDAP.ber_element;
  l_vals DBMS_LDAP.string_collection;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ACTIVEDIRECTORY';
  -- Choose to raise exceptions.
  DBMS_LDAP.USE_EXCEPTION := TRUE;
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host, portnum => l_ldap_port);
  l_retval  := DBMS_LDAP.simple_bind_s(ld => l_session, dn => l_ldap_user, passwd => l_ldap_passwd);
  -- Get all attributes
  l_attrs(1)                                                   := 'displayname';
  l_attrs(2)                                                   := 'mail';
  l_attrs(3)                                                   := 'mobile';
  l_attrs(4)                                                   := 'homephone';
  l_attrs(5)                                                   := 'ipphone';
  l_retval                                                     := DBMS_LDAP.search_s(ld => l_session, base => l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, filter => '(&(&(objectclass=user)(!(physicaldeliveryofficename=no phone)))(!(useraccountcontrol=514)))', attrs => l_attrs, attronly => 0, res => l_message);
  IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
    -- Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);
    << entry_loop >>
    WHILE l_entry IS NOT NULL
    LOOP
      -- Get all the attributes for this entry.
      l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
      --Clear variables
      dispname   := NULL;
      email      := NULL;
      Extension  := NULL;
      DirectDial := NULL;
      Mobile     := NULL;
      << attributes_loop >>
      WHILE l_attr_name IS NOT NULL
      LOOP
        -- Get all the values for this attribute.
        l_vals := DBMS_LDAP.get_values (ld => l_session, ldapentry => l_entry, attr => l_attr_name);
        << values_loop >>
        FOR i IN l_vals.FIRST .. l_vals.LAST
        LOOP
          IF l_attr_name    = 'displayName' THEN
            dispname       := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'ipPhone' THEN
            DirectDial     := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'mail' THEN
            email          := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'homePhone' THEN
            Extension      := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'mobile' THEN
            Mobile         := SUBSTR(l_vals(i),1,200);
          END IF ;
        END LOOP values_loop;
        l_attr_name := DBMS_LDAP.next_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
      END LOOP attibutes_loop;
      INSERT
      INTO ACTIVEDIRECTORY
        (
          DISPLAYNAME,
          EMAIL,
          EXTENSION,
          DIRECTDIAL,
          MOBILE,
          RTYPE
        )
        VALUES
        (
          dispname,
          email,
          Extension,
          DirectDial,
          Mobile,
          RType
        );
      l_entry := DBMS_LDAP.next_entry(ld => l_session, msg => l_entry);
    END LOOP entry_loop;
  END IF;
  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  --Delete non-person users
  DELETE
  FROM ACTIVEDIRECTORY
  WHERE DisplayName IN('Laptop','Report User','huffman_01','time test','Time Clock','manager','VPN Test','Tanya VPN');
  
END;

An important thing to note about this stored procedure is the letter case. We keep our SQL Server collation such that case sensitivity doesn’t matter, but in Oracle it does. Pay attention to your collation settings to determine whether or not you need to worry about capitalization.
At this point I had the code working properly in the SQL Developer window, but when I called the stored procedure I got an error:

ORA-24247: network access denied by access control list (ACL).

Oracle uses access control lists to enforce security on network locations. To get around this, I created an access control list that allowed access to my domain controller:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'ldap_access.xml',
    description  => 'Permissions to access LDAP servers.',
    principal    => 'dbname',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'ldap_access.xml',
    host         => 'domaincontroller.domain.com',
    lower_port   => 389,
    upper_port   => 389
    );
   COMMIT;
END;

You can see access control lists in your database with these queries:

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

20150115 Oracle LDAP
20150115 Oracle LDAP 2

The last step is to set up a job to run the stored procedure to refresh the table. I’ve chosen a job that runs daily.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB ( job_name => 'Refresh_ActiveDirectory', 
                              job_type => 'STORED_PROCEDURE', 
                              job_action => 'REFRESH_ACTIVEDIRECTORY', 
                              start_date => '13-JAN-15 11:00:00 PM', 
                              repeat_interval => 'FREQ=DAILY');
END;

Now I have data from Active Directory flowing into my database daily.

Advertisements

Querying Active Directory Through SQL Server

One great thing about fishing is being out in nature. It’s great seeing bald eagles cruising around looking for food, hearing the eerie call of a loon, and smelling the air that seems so much fresher than the air on the land. Observing the tranquility of nature can be very relaxing and very fulfilling.
SQL Server is great because it’s a Microsoft product and that helps it to easily “observe” the data in other Microsoft projects. I was recently asked to import some data from Active Directory into a SQL Server table. This data was going to be used as a feed for our company directory. A quick Google search showed I had two options. Both involve using OPENQUERY(), but the first has you create a linked server whereas the second queries the data source with the definition included. The second method requires Ad Hoc Distributed Queries to be enabled on the server, and my server does not have that enabled, so I went ahead and created a linked server:

USE [master]
GO

EXEC MASTER.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DomainName\ServiceAccount',@rmtpassword='########'

GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=NULL
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'

My next step was to create a table to hold the information I wanted. There are a TON of fields from Active Directory that can be captured, but I was only interested in a few. I also added a ‘Type’ field at the end so I could put in some other data and differentiate it with the employee data.

CREATE TABLE [dbo].[LW_ActiveDirectory](
   [DisplayName] [VARCHAR](255) NOT NULL,
   [Email] [VARCHAR](355) NULL,
   [Extension] [VARCHAR](10) NULL,
   [DirectDial] [VARCHAR](25) NULL,
   [Mobile] [VARCHAR](25) NULL,
   [Type] [CHAR](1) NOT NULL
) ON [PRIMARY]

Next I created a stored procedure that captures the data using the Linked Server, and inserts it into my new table:

CREATE PROCEDURE [dbo].[RefreshADTable]

AS
BEGIN

   SET NOCOUNT ON;

   --Clear the table first
   TRUNCATE TABLE DatabaseName.dbo.LW_ActiveDirectory

   --Latest employee information
   INSERT  INTO DatabaseName.dbo.LW_ActiveDirectory
           ( DisplayName ,
             Email ,
             Extension ,
             DirectDial ,
             Mobile ,
             TYPE
           )
           SELECT  displayname ,
                   mail ,
                   homephone ,
                   ipphone ,
                   mobile ,
                   'E' AS TYPE
           FROM    OPENQUERY(ADSI,
                             'SELECT displayName, mail, mobile, homephone,ipphone
	  FROM  ''LDAP://ldapurl.com/OU=Users,OU=MyOU,DC=DC1,DC=com'' 
	  WHERE objectClass =  ''User''
	  AND useraccountcontrol <> 514
	  AND physicaldeliveryofficename <> ''no phone''
	  ') AS tblADSI

   --Delete non-person users
   DELETE
   FROM DatabaseName.dbo.LW_ActiveDirectory
   WHERE DisplayName IN('Laptop','Report User','time test','manager')

   
END

A few notes on this code:

  1. The LDAP tree is navigated from right to left.  This means I was looking for ldaprul.com/DC1/MyOU/Users.
  2. I excluded a useraccountcontrol value to get rid of inactive employees.  This value could be different for you.
  3. There were several employees with no phone information.  I had to go into Active Directory and put “no phone” into an unused field [Office] to exclude these from my extract.
  4. I also had to delete a few legacy users that couldn’t be deleted from Active Directory, such as Laptop and manager.

Next I create a SQL Agent job to run this stored procedure once a day.  We don’t need data more up-to-date than that for our purposes.

The last step was to go into Active Directory and add any missing information to each user.  This was a pain, but worth the effort.  When new employees are added the HR person here will enter this information.  Now we have a employee directory that will have the latest information in the database each day.  We used a reporting piece to create a printable directory sheet.

An upcoming blog post will show how to do this same thing in Oracle.  Stay tuned!