Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Setting Criteria on an Active Directory search from SQL Server

Posted on 2004-10-06
10
770 Views
Last Modified: 2008-01-09
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>;(&(objectClass=user)(objectCategory=Person));name, 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>;(&(objectClass=user)(objectCategory=Person));name, 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>;(&(objectClass=user)(objectCategory=Person)(sAMAccountName=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?
0
Comment
Question by:ian_r
  • 5
  • 4
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12240683
If you're using ASP, build the connection string in VBS and then pass that:

Dim sSQL
Dim sAccountName
sAccountName="donait"

sSql="SELECT     *
FROM         OPENQUERY(ADSI,
                        '<LDAP://blah.blah.blah.uk>;(&(objectClass=user)(objectCategory=Person)"
sSql=sSql & "(sAMAccountName=" & sAccountName
sSql=sSql & "));name, mail, sAMAccountName, userPrincipalName, title, department')"

set rs=connection.execute(sSql)

0
 
LVL 34

Expert Comment

by:arbert
ID: 12240693
Ooops, it wrapped, but you get the idea:

sSql="SELECT     * FROM         OPENQUERY(ADSI, '<LDAP://blah.blah.blah.uk>;(&(objectClass=user)(objectCategory=Person)"
sSql=sSql & "(sAMAccountName=" & sAccountName
sSql=sSql & "));name, mail, sAMAccountName, userPrincipalName, title, department')"
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12240758
probably you need to specify domainname\username
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:ian_r
ID: 12241253
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?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12241303
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>;(&(objectClass=user)(objectCategory=Person));name, 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...
0
 

Author Comment

by:ian_r
ID: 12241583
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>;(&(objectClass=user)(objectCategory=Person)(sAMAccountName=' + @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

0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 12241687
Close, you have to generate the string and then use EXEC(yourstring) to run.

Haven't tested, but something like this:

declare @sql varchar(2000)

select @sql='SELECT     * FROM         OPENQUERY(ADSI, ' + char(39) + '<LDAP://blah.blah.blah.uk>;(&(objectClass=user)(objectCategory=Person)(sAMAccountName=' + @username + '));name, mail, sAMAccountName, userPrincipalName, title, department' + char(39) + ')'

exec(@sql)
0
 

Author Comment

by:ian_r
ID: 12241737
Cheers arbert, that looks great.  I'll try it tomorrow and tell you how I go on.
0
 

Author Comment

by:ian_r
ID: 12246455
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 :-).
0
 
LVL 34

Expert Comment

by:arbert
ID: 12251331
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
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL convert date to string 4 34
TSQL XML Namespaces 7 22
Compare a numeric value to a varchar w/o getting an error 4 20
Run Stored Procedure uisng ADO 5 20
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question