?
Solved

Create a link to Active Directory in Oracle 10g and query the database for user info

Posted on 2007-03-23
6
Medium Priority
?
770 Views
Last Modified: 2013-12-19
I come from a MS SQL background but I am jumping in head first into Oracle 10g.  I know how to created a "linked server" to an Active Directory domain (LDAP) in MS SQL, however I do not know how to do this in Oracle.  I was hoping that someone could offer a few tips and trix for me.  

Here is a sample of a query I have written for MS SQL so I am not even sure if this will work in Oracle or not (I am sure that some of the syntax may differ slightly so any tips on that will be appreciated as well)

SELECT
      sAMAccountName As Login,
      cn,
      givenName,
      sn,
      mail,
      displayName,
      userPrincipalName,
      streetAddress,
      postalCode
FROM
      OPENQUERY(ADSI, 'SELECT
                        sAMAccountName,
                        cn,
                        givenName,
                        sn,
                        mail,
                        displayName,
                        userPrincipalName,
                        streetAddress,
                        postalCode,
                        objectClass
                   FROM
                        ''LDAP://MYDC/OU=Companies,DC=MYCO,DC=COM''
                   WHERE
                        objectClass = ''user''') AS USERS

Any help would be much appreciated as I am in a bit of a time crunch.  Thnx...a
0
Comment
Question by:Atropa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Author Comment

by:Atropa
ID: 18800420
Is this even possible from Oracle?  If it helps any we are also using "Toad".  I believe TOAD is similar to the SQL Admin tool.  I haven't seen in there how to create the connection yet to AD.  Really any help would be appreciated...a
0
 
LVL 18

Accepted Solution

by:
Sham Haque earned 1000 total points
ID: 18806325
hi Atropa

i'm not sure that what you are trying to achieve is possible, at least not the way that you're envisaging.
I assume that you want to be able to "do" something with this data, or at least to read it.

You could use VBScript to accomplish your goals - using recordsets. This may not be the most efficient way to go, but it does provide you with a mechanism to transfer between AD and other data sources (incl Oracle :shock:)

Below is a routine I used for fetching AD recs (names, emails) to a SQL Server table for processing - this should give you something to chew on at least.....in place of the SQL Server connection, just use an Oracle provider, and give the TNS details to the connection.

Paste everything between the '***' lines into notepad, make the changes to suit your needs and environment, and save it with a ".vbs" extension - then run from a DOS prompt (not necessary - but easier to see when it doesn't work...!)

'***********************************************************
'On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = "LDAP://"& objRootDSE.Get("defaultNamingContext")

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.CommandText = "<"& strDomain &">;(objectCategory=person)" & _
";distinguishedName,cn;subtree"
objCommand.Properties("Page Size") = 40000

Set objRecordSet = objCommand.Execute

'alter connection string for your Oracle db - see http://www.connectionstrings.com for guidance
set objSQLConn = CreateObject("ADODB.Connection")
objSQLConn.Open = "Provider=sqloledb;Data Source=rsmd-mbx-a19;Initial Catalog=UnverifiedAssetTool;Integrated Security=SSPI;"
objSQLConn.Execute "TRUNCATE TABLE tmpAddresses2"

set objRS2 = CreateObject("ADODB.Recordset")
objRS2.Open "SELECT * FROM tmpAddresses2",objSQLConn, adOpenStatic, adLockOptimistic

While Not objRecordSet.EOF
strUserDN = objRecordSet.Fields("DistinguishedName")
set objUser = GetObject("LDAP://"& strUserDN)

If instr(objUser.homemdb,"CN=") <> 0 then
if err.number<>0 then
msgbox err.Number & vbCrLf & err.Description
msgbox "Homemdb value is equal to " & objUser.Homemdb & "."
end if


on error resume next
For Each entry in objUser.GetEx("proxyAddresses")

If instr(entry,"X400") = 0 Then
proxy1 = replace(entry,"SMTP:","")
proxy1 = replace(proxy1,"smtp:","")

If instr(proxy1, "MBX:") = 0 Then
if instr(objuser.mailnickname," ") = 0 then

objRS2.AddNew
objRS2.Fields("username").Value = objuser.mailnickname
objRS2.Fields("fullname").Value = objuser.displayname
objRS2.Fields("fname").Value = objuser.givenname
objRS2.Fields("sname").Value = objuser.sn
objRS2.Fields("proxyaddr").Value = proxy1
objRS2.Update

End If
End If
End If

Next
End If

objRecordSet.MoveNext

Wend
objRS2.Close
objConnection.Close
objSQLConn.Close

Set objRootDSE = Nothing
set objRecordSet = Nothing
set objConnection = Nothing
set objCommand = Nothing
set objRS2 = Nothing
set objSQLConn = Nothing
set objUser = Nothing
set objOutputFile = Nothing
set objFSO = Nothing

wscript.Echo "All Done"

'*********************************************************
0
 
LVL 5

Author Comment

by:Atropa
ID: 18826582
I appreciate the code for VB however that I knew how to do!  To give a wee bit more background I used to work for an organization that used only MS SQL Servers (2000 and 2005).  They used MS SQL because I set them up on MS SQL.  I now am with a company that uses only Oracle 10g.  

In MS SQL you could create a "linked server" using a dsn or a or JET or ADO connection to another data source such as another SQL Server, an Oracle Database, an Access DB or even a text file, etc...

To get information from Active Directory I would create a linked server to our domain and write a query like:

Select AD.* From OpenQuery(LinkedAD, 'Select sAMAccountName, displayName, GUID From ''LDAP://MYDC/OU=Companies,DC=MYCO,DC=COM'' WHERE objectClass = ''user''') As AD

Since I have been tasked with changing an application that interacts with an Oracle database I was hoping to modify the database to check my user tables and grab a personal identifier that the infrastructure group has created in AD and let the app know if the user has access to the it or not.  Obviously it gets even more complicated than just that which is precisely why I would like to handle as much of this in the database rather than fully re-writting the application.

So the short version of my question is really just, can I query Active Directory right from Oracle or do I ~HAVE~ to query AD from VB?  Thanks again...a
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Sham Haque
ID: 18835287
hi again atropa
see the links below for establishing a ODBC-DSN data source in Oracle:

Making a connection from Oracle to SQL Server
http://www.databasejournal.com/features/oracle/article.php/3442661

see section 3) Configuring Oracle to use ODBC (and below) in this article
http://oraclebi.blogspot.com/2006/02/using-excel-as-data-source-for.html

In addition, you'll need to create a DSN to connect to your AD....
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18835299
personally speaking, I would rather go down the VB route - it gets awfully messy working in Oracle with non-oracle data sources...
Alternatively, convince your new company that SS 2005 is the new 10g....
0
 
LVL 5

Author Comment

by:Atropa
ID: 18836958
I would try and do that haq but I just learned about the table versioning which is an ENORMOUS benefit over MS SQL.  Had I had something like that over the years without having to create them myself...lets just say the possibilities are endless.

I do agree with you going the VB route, I just ~want~ to play around with it in Oracle as well to see what can be done.

Even with what I said above, I still miss MS SQL )c:
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question