Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Retrieving Users from Active directory using linked server ADSI with parameters

Posted on 2008-10-13
17
Medium Priority
?
3,785 Views
Last Modified: 2013-12-24
I've linked my Active Directory Server to the SQL Server. When I issue the command blow I only recieve 1000 records:

select count(saMAccountname)
from openquery
(
ADSI,'SELECT saMAccountname
FROM ''LDAP://MyADServer''
WHERE objectCategory = ''Person'' AND objectClass = ''user''

')

Since there are quite a bit more than 1000 I trid to supply a paremeter in a stored procuder, @UserID, to filter the query.

declare @SQLStr nvarchar(500)
set @SQLStr = 'OPENQUERY(ADSI, SELECT saMAccountname FROM ''LDAP://MyADServer''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
AND saMAccountname = ''' + @Userid + '*'''

SELECT     saMAccountname AS userid
FROM       OPENQUERY(ADSI, @SQLStr)

This doesn't work though. I might be off here since I' not the best in SQL Syntax. Any suggestions?

Best Regards

Tomas
0
Comment
Question by:ekenman
[X]
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
  • 9
  • 8
17 Comments
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701070

Hi Tomas,

I have no idea how you might do this in SQL, but you need to enable Paging for the query.

The current limit is defined in AD to prevent searches swamping the system, enabling Paging will return multiple sets of 1000 results (per page), the client will read that as a single larger data-set.

Chris
0
 

Author Comment

by:ekenman
ID: 22701162
Hi Chris,

I've tried ROW_NUMBER() OVER (saMAccountname)  but this results in an error preparing the question. Perhaps it is possible to page in a different way? I have no previous knowledge of LDAP....

Although the solution I would prefer is if it was possible to send parts of the userid as a parameter and receive a set of users from that. This to minimize the rescourses needed.

/Tomas
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701185

Hmmm it may be that there is no way to enable Paging at all:

http://support.microsoft.com/kb/299410

It's a bit of an old article but I can't find an update that states otherwise.

Onto the sAMAccountName filter because that would be better:

> saMAccountname = ''' + @Userid + '*'''

It looks like you have an extra quote mark. Or does this fail as well?

aMAccountname = ''' + @Userid + *'''

Or have you tried LIKE?

Chris
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:ekenman
ID: 22701229
Hi Chris,

No paging then...  

I think the quote marks are all right. The string looks allright if I do a SELET SQLStr, cut and paste it and run a new query. The thing is to pass the parameter inside the OPENQUERY() statement. It might not be doable????

I've tried LIKE, but that gives an error. The % wild card is not OK within the OPENQUERY either so it seems you have to use LDAP Syntax where I'm totaly lost. But I managed to figure the = and * out to get it to work.

This works perfectly:

select saMAccountname
from openquery
(
ADSI,'SELECT saMAccountname
FROM ''LDAP://MyADServer''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
AND saMAccountname = ''HA*''
')

Now... How to get that parameter to replace the HA....
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701249

Can we use concatenation?

AND sAMAccountName = @Userid + '*'

Chris
0
 

Author Comment

by:ekenman
ID: 22701317
No, tried that too... Incorrect syntax near +

Declare @Username nvarchar(50)
set @Username = 'HA'
select saMAccountname
from openquery
(
ADSI,'SELECT saMAccountname
FROM ''LDAP://MyADServer'
WHERE objectCategory = ''Person'' AND objectClass = ''user''
AND saMAccountname = ''' + @Username + '*''
')

0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701337

Shouldn't it be:

Declare @Username nvarchar(50)
set @Username = 'HA'
select saMAccountname
from openquery
(
ADSI,'SELECT saMAccountname
FROM ''LDAP://MyADServer''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
AND saMAccountname = @Username + ''*''
')

Shouldn't need the quotes for the parameter, should we? I assume the double-quoting is to escape it as it would normally be the end of the query string?

Sorry this is a bit vague, I didn't want to test it on my SQL server, it's a bit busy ;)

Chris
0
 

Author Comment

by:ekenman
ID: 22701373
No, it doesn't work :(. After the "OPENQUERY(ADSI," the first Open Quotation mark for the whole SQL query starts, and ends just before the last bracket. And there seems to be no logic for parameters within theese. At least not with the Syntax we're trying...
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701386

Hmm that's really quite annoying. I'll see if I can get an SQL server running that won't mind if I add in the ADSI pieces.

Chris
0
 

Author Comment

by:ekenman
ID: 22701403
Chris, It's very much appriciated!!
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701456

Odd, I can't use parameters at all in the query. Does it work if the user name is correct in the parameter?

Chris
0
 

Author Comment

by:ekenman
ID: 22701473
You have to add a linked server to the AD-server first.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

The first parameter, ADSI, is the name you'll use in querying the linked server. Active Directory Services 2.5 is the product name, ADSDSOObject is the provider, and adsdatasource is the built-in data source name
0
 

Author Comment

by:ekenman
ID: 22701477
Oh... I missunderstood. You got it
0
 

Author Comment

by:ekenman
ID: 22701485
No, I get the Error near +. I tried & also, any other suggestions?
0
 
LVL 71

Accepted Solution

by:
Chris Dent earned 2000 total points
ID: 22701507

Yeah, this one, it's messy, but it works. Need to be pretty careful with the apostrophe's in there, hell of a lot of them.

Chris
Declare @Username nvarchar(50)
set @Username = 'HA'
 
Declare @Query nvarchar(2000)
 
SET @Query = 'SELECT * FROM 
OPENQUERY(ADSI, 
''SELECT samAccountName, givenName, sn, legacyExchangeDN 
FROM ''''LDAP://DC=corp,DC=monitise,DC=net'''' 
WHERE objectClass=''''Person'''' 
AND objectClass = ''''User'''' 
AND sAMAccountName = ''''' + @Username + '*'''''') 
WHERE givenName IS NOT NULL'
 
EXEC(@Query)

Open in new window

0
 

Author Comment

by:ekenman
ID: 22701527
Chris, You're the man!!! Thank you!!!!
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22701531

Glad I could help :)

Chris
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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