Quick Enrollment from External Database - "Unable to Fetch. Check your query or permission" when using a SQL View that uses an OPENROWSET
I am attempting to setup ADSelfService Plus to fetch data for enrollment from a MSSQL database.
I am connecting to a view I have created in SQL using the following SQL statement in ManageEgine: "Select UserName, Question, Answer from ManageEngineStaffEnrollment;" (ManageEngineStaffEnrollment is the name of view I have created in SQL).
This works until I modify the view in SQL to also retrieve and join data from active directory (So I can get the sAMAccountName as I cannot enroll based on UPN).
The view works as expected from within MS SQL, but when attempting to fetch data from within ADSelfService Plus I get the error "Unable to fetch. Check your Query and Permission."
As I have stated the query works in MS SQL and the query works in ADSelf Service Plus until I modify it to include Active Directory so I do not believe it is an issue with permissions or the query.
I have narrowed this down to the inclusion of the OPENROWSET that retrieves data from Active Directory.
This is the SQL statement:
Select AD.sAMAccountName as UserName, 'What is your Employee ID?' as Question, StaffCode as 'Answer'
from Workers
--Link to Active Directory
join OPENROWSET('ADSDSOObject', 'ADSI Flag=0x11;',
'<LDAP://OU=Stafff,DC=Domain,DC=COM>;(&(objectCategory=user)(objectClass=user));sAMAccountName,userPrincipalName;subtree') as AD
on AD.UserPrincipalName = Workers.WorkEmail
where (StaffCode IS NOT NULL and WorkEmail <> '')
I have also tried hardcoding user credentials into the OPENROWSET connection string as below, but I get the same error:
join OPENROWSET('ADSDSOObject', 'User ID=domain\username;Password=password;ADSI Flag=0x11;',
'<LDAP://OU=Stafff,DC=Domain,DC=COM>;(&(objectCategory=user)(objectClass=user));sAMAccountName,userPrincipalName;subtree') as AD
--on AD.UserPrincipalName = Workers.WorkEmail
where (StaffCode IS NOT NULL and WorkEmail <> '')
As I have stated both of these queries work fine from within MS SQL so I suspect there is some sort of bug or limitation in ManageEngine? Can someone please advise? Is there some way I can see more information on the error than just "Unable to fetch. Check your Query and Permission."?
I am connecting to a view I have created in SQL using the following SQL statement in ManageEgine: "Select UserName, Question, Answer from ManageEngineStaffEnrollment;" (ManageEngineStaffEnrollment is the name of view I have created in SQL).
This works until I modify the view in SQL to also retrieve and join data from active directory (So I can get the sAMAccountName as I cannot enroll based on UPN).
The view works as expected from within MS SQL, but when attempting to fetch data from within ADSelfService Plus I get the error "Unable to fetch. Check your Query and Permission."
As I have stated the query works in MS SQL and the query works in ADSelf Service Plus until I modify it to include Active Directory so I do not believe it is an issue with permissions or the query.
I have narrowed this down to the inclusion of the OPENROWSET that retrieves data from Active Directory.
This is the SQL statement:
Select AD.sAMAccountName as UserName, 'What is your Employee ID?' as Question, StaffCode as 'Answer'
from Workers
--Link to Active Directory
join OPENROWSET('ADSDSOObject', 'ADSI Flag=0x11;',
'<LDAP://OU=Stafff,DC=Domain,DC=COM>;(&(objectCategory=user)(objectClass=user));sAMAccountName,userPrincipalName;subtree') as AD
on AD.UserPrincipalName = Workers.WorkEmail
where (StaffCode IS NOT NULL and WorkEmail <> '')
I have also tried hardcoding user credentials into the OPENROWSET connection string as below, but I get the same error:
join OPENROWSET('ADSDSOObject', 'User ID=domain\username;Password=password;ADSI Flag=0x11;',
'<LDAP://OU=Stafff,DC=Domain,DC=COM>;(&(objectCategory=user)(objectClass=user));sAMAccountName,userPrincipalName;subtree') as AD
--on AD.UserPrincipalName = Workers.WorkEmail
where (StaffCode IS NOT NULL and WorkEmail <> '')
As I have stated both of these queries work fine from within MS SQL so I suspect there is some sort of bug or limitation in ManageEngine? Can someone please advise? Is there some way I can see more information on the error than just "Unable to fetch. Check your Query and Permission."?