How can I get access to Active Directory through SQL2K?
I have configured a linked server, but I get a syntax error message
(Server: Msg 7321, Level 16, State 2, Line 1 )
when I try and run a command through SQL Query Analyzer.
Here is some background:
I am trying to report off Active Directory through Crystal Reports 8.5.
I have been able to configure an ADO data source directly in CR using the following connection string:
Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=ADSDSOOBJECT;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648
and then passing it a query like this:
Select cn,company,department,description,directreports,
displayName,division,facsimiletelephonenumber,givenName,
homephone,initials,instancetype,ipphone,l,lastlogoff,
lastlogon,mail,manager,memberof,mobile,o,objectcategory,
objectclass,otherfacsimiletelephonenumber,otherhomephone,
otheripphone,othermobile,otherpager,othertelephone,ou,
pager,personaltitle,physicaldeliveryofficename,
postaladdress,postalcode,postofficebox,sn,st,street,
streetaddress,telephonenumber,title,userprincipalname from 'LDAP://DC=XXXXXXX,DC=com' Where ObjectClass='User'
or ObjectClass='Contact'
I get a table (ado) with the fields I want.
(Except for the description field... dunno why!?)
My problem is that this does not seem to be an acceptable data source for Crystal Enterprise 8.0 so I am unable to distribute this report in our environment. Works great for me though!
So I looked around and found some advice to set up a linked server through Query Analyser:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
I can then see it in EM, although I get no data under tables or views.
So back to Query Analyser:
I try and run a simplified version of my above query:
select *
from OpenQuery( ADSI,'SELECT company FROM "LDAP://DC=XXXXXXX,DC=Com"')
If Parses OK, but then returns a:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].
So I figure that there is something wrong with the syntax of my embedded OpenQuery select statement, but I can't get a handle on it! I've been mixing " and ' and trying other arrangements, and I'm stuck. What else can I try? Or am I barking up the wrong tree with this approach? Is there a better way to build a SQL database out of AD data so that I can report off it with CR?
Thanks!
Dgenne
I have configured a linked server, but I get a syntax error message
(Server: Msg 7321, Level 16, State 2, Line 1 )
when I try and run a command through SQL Query Analyzer.
Here is some background:
I am trying to report off Active Directory through Crystal Reports 8.5.
I have been able to configure an ADO data source directly in CR using the following connection string:
Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=ADSDSOOBJECT;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648
and then passing it a query like this:
Select cn,company,department,description,directreports,
displayName,division,facsimiletelephonenumber,givenName,
homephone,initials,instancetype,ipphone,l,lastlogoff,
lastlogon,mail,manager,memberof,mobile,o,objectcategory,
objectclass,otherfacsimiletelephonenumber,otherhomephone,
otheripphone,othermobile,otherpager,othertelephone,ou,
pager,personaltitle,physicaldeliveryofficename,
postaladdress,postalcode,postofficebox,sn,st,street,
streetaddress,telephonenumber,title,userprincipalname from 'LDAP://DC=XXXXXXX,DC=com' Where ObjectClass='User'
or ObjectClass='Contact'
I get a table (ado) with the fields I want.
(Except for the description field... dunno why!?)
My problem is that this does not seem to be an acceptable data source for Crystal Enterprise 8.0 so I am unable to distribute this report in our environment. Works great for me though!
So I looked around and found some advice to set up a linked server through Query Analyser:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
I can then see it in EM, although I get no data under tables or views.
So back to Query Analyser:
I try and run a simplified version of my above query:
select *
from OpenQuery( ADSI,'SELECT company FROM "LDAP://DC=XXXXXXX,DC=Com"')
If Parses OK, but then returns a:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:
repare returned 0x80040e14].So I figure that there is something wrong with the syntax of my embedded OpenQuery select statement, but I can't get a handle on it! I've been mixing " and ' and trying other arrangements, and I'm stuck. What else can I try? Or am I barking up the wrong tree with this approach? Is there a better way to build a SQL database out of AD data so that I can report off it with CR?
Thanks!
Dgenne
