Atropa
asked on
Create a link to Active Directory in Oracle 10g and query the database for user info
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,D C=COM''
WHERE
objectClass = ''user''') AS USERS
Any help would be much appreciated as I am in a bit of a time crunch. Thnx...a
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
WHERE
objectClass = ''user''') AS USERS
Any help would be much appreciated as I am in a bit of a time crunch. Thnx...a
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,D C=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
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
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
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....
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....
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....
Alternatively, convince your new company that SS 2005 is the new 10g....
ASKER
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:
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:
ASKER