We have a need to pull some attributes from Active Directory and display it in a dashboard report. We added a linked server ADSI to our SQL server. created a query (see below) which runs fine and pulls what we need when executed from SQL. Though when I put it as a Dashboard data provider it generated an error. SQL connection is using NT Authentication, In Dashboard I was using Current Session.
1. ARe there better ways to bring AD info into dashboard reports?
2. what ID should have access?
3. please share your experience working with AD data
Exception of type 'Hyland.Public.ServiceException' was thrown. ---> Source: msodbcsql13.dll
TargetSite: Void Execute(System.Func`1, System.Data.Common.DbCommand, Hyland.Data.Database.DatabaseProperties, Boolean, T ByRef)
Exception: ERROR [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
SELECT * into #ADRep FROM OpenQuery
(
ADSI,
'SELECT company, title, displayName, telephoneNumber, sAMAccountName, sAMAccountType,
mail, mobile, department, co,c,
physicalDeliveryOfficeName, givenname, l , st, postOfficeBox, postalAddress, postalCode,directReports,manager, ou, enabled, distinguishedname
FROM ''LDAP://DC=xxx,DC=com''
WHERE objectClass = ''User'' and company = ''xxx'' '
) AS tblADSI
ORDER BY displayname
Select * from #ADRep
drop table #ADRep
