ian_r
asked on
Setting Criteria on an Active Directory search from SQL Server
I've linked my Active Directory to SQL Server as a linked server and I've managed to run queries on the directory.
I can run a query to return all users in the directory, e.g. :
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk >;(&(objec tClass=use r)(objectC ategory=Pe rson));nam e, mail, sAMAccountName, userPrincipalName, title, department')
And I wanted to place this into a view or a stored procedure which our ASP web developers can call from their web pages. However, whenever I try to limit the recordset to only one user by setting a WHERE clause in the statement I get no results returned even though I know the user name I've used is valid, e.g. :
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk >;(&(objec tClass=use r)(objectC ategory=Pe rson));nam e, mail, sAMAccountName, userPrincipalName, title, department')
WHERE sAMAccountName = 'donait'
I've tried this from both an ASP page and from within SQL Query Analyzer itself, but I get no rows returned. If I place the criteria within the LDAP syntax itself, then I do get the user's details returned but I can't use this method because I need to place the value of a passed parameter as the sAMAccountName, e.g.:
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk >;(&(objec tClass=use r)(objectC ategory=Pe rson)(sAMA ccountName =donait)); name, mail, sAMAccountName, userPrincipalName, title, department')
Does anyone know where I'm going wrong, or can you suggest a different way to achieve a solution?
I can run a query to return all users in the directory, e.g. :
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk
And I wanted to place this into a view or a stored procedure which our ASP web developers can call from their web pages. However, whenever I try to limit the recordset to only one user by setting a WHERE clause in the statement I get no results returned even though I know the user name I've used is valid, e.g. :
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk
WHERE sAMAccountName = 'donait'
I've tried this from both an ASP page and from within SQL Query Analyzer itself, but I get no rows returned. If I place the criteria within the LDAP syntax itself, then I do get the user's details returned but I can't use this method because I need to place the value of a passed parameter as the sAMAccountName, e.g.:
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk
Does anyone know where I'm going wrong, or can you suggest a different way to achieve a solution?
Ooops, it wrapped, but you get the idea:
sSql="SELECT * FROM OPENQUERY(ADSI, '<LDAP://blah.blah.blah.uk >;(&(objec tClass=use r)(objectC ategory=Pe rson)"
sSql=sSql & "(sAMAccountName=" & sAccountName
sSql=sSql & "));name, mail, sAMAccountName, userPrincipalName, title, department')"
sSql="SELECT * FROM OPENQUERY(ADSI, '<LDAP://blah.blah.blah.uk
sSql=sSql & "(sAMAccountName=" & sAccountName
sSql=sSql & "));name, mail, sAMAccountName, userPrincipalName, title, department')"
probably you need to specify domainname\username
ASKER
You can run the query directly from the ASP page but I didn't want to do this because it's slower than running it from within a stored procedure and will have a higher impact on the network bandwidth. Also, it would be easier to place this functionality within one stored procedure so I can nest it within others and allow more than one ASP application access to the same procedure without having to cut 'n paste code.
What I'm trying should be possible according to:
I don't think I need to specify the domain name with the username because the domain is part of the LDAP string, and the query works fine if I put the username in next to the "objectCategory=Person" rather than use a WHERE clause (see original post).
I'm really keen to get this working, any suggestions?
What I'm trying should be possible according to:
I don't think I need to specify the domain name with the username because the domain is part of the LDAP string, and the query works fine if I put the username in next to the "objectCategory=Person" rather than use a WHERE clause (see original post).
I'm really keen to get this working, any suggestions?
Have you tried changing the = on the outside to a like to see if there are spaces?
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk >;(&(objec tClass=use r)(objectC ategory=Pe rson));nam e, mail, sAMAccountName, userPrincipalName, title, department')
WHERE sAMAccountName like 'donait%'
You could also use Dynam sql in the stored proc (as a last resort) to put the where on the inside of the linked server. It's actually better there anyway because you KNOW the criteria is applied at AD and not at SQL Server...
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk
WHERE sAMAccountName like 'donait%'
You could also use Dynam sql in the stored proc (as a last resort) to put the where on the inside of the linked server. It's actually better there anyway because you KNOW the criteria is applied at AD and not at SQL Server...
ASKER
Thanks for the comments arbert, I'll try the 'like' when I'm back at work tomorrow.
How do I use dynamic sql within the stored proc? I've tried inserting an input parameter within the string but I get a syntaxt error, e.g. :
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk >;(&(objec tClass=use r)(objectC ategory=Pe rson)(sAMA ccountName =' + @username + '));name, mail, sAMAccountName, userPrincipalName, title, department')
Is that what you mean, or have I got the wrong end of the stick?
The links I missed of the previous post were:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/creating_a_heterogeneous_join_between_sql_server_and_active_directory.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/creating_a_heterogeneous_join_between_sql_server_and_active_directory.asp
How do I use dynamic sql within the stored proc? I've tried inserting an input parameter within the string but I get a syntaxt error, e.g. :
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk
Is that what you mean, or have I got the wrong end of the stick?
The links I missed of the previous post were:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/creating_a_heterogeneous_join_between_sql_server_and_active_directory.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/creating_a_heterogeneous_join_between_sql_server_and_active_directory.asp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers arbert, that looks great. I'll try it tomorrow and tell you how I go on.
ASKER
arbert,
Your last suggestion worked just as you suggested, without me needing to change any of your syntax - who needs testing eh?
Thanks again, I was working on this all day and I'm new to stored procedures, so I was getting pretty frustrated :-).
Your last suggestion worked just as you suggested, without me needing to change any of your syntax - who needs testing eh?
Thanks again, I was working on this all day and I'm new to stored procedures, so I was getting pretty frustrated :-).
Good deal. I wouldn't say that dynamic SQL is ideal, but sometimes there really isn't a way around it. With openquery, if you can pass the criteria on the "inside" you will usually get MUCH better performance than applying it on the outside.
Glad it all worked.
Brett
Glad it all worked.
Brett
Dim sSQL
Dim sAccountName
sAccountName="donait"
sSql="SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://blah.blah.blah.uk
sSql=sSql & "(sAMAccountName=" & sAccountName
sSql=sSql & "));name, mail, sAMAccountName, userPrincipalName, title, department')"
set rs=connection.execute(sSql