• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Active Directory via SQL Server issues

I want to access some data via Active Directory.
To do this I had to run
sp_configure 'show advanced options',1
reconfigue
sp_configure 'Ad Hoc Distributed Queries',1
I will have to ask my DBA to run this on the live SQL Server and I hope there will be no problem in running this. Is there any good reason he would say no?
Anyway after running that, I have to run;
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
Now onto the query.
I have found that when I query the who Directory, I get a maximum of 1000 rows returned.
There are more rows than that in AD, so I have decided I want a UDF that will take a parameter and return the row I want.
I have found that to use parameters in queries that use OPENROWSET, I have to use the EXEC statement. However I cannot get it to work. So the main question is, what is wrong with the EXEC statement?
DECLARE @StaffNumber AS VARCHAR(256)
SET @StaffNumber = '4008724'
 
--DECLARE @Ldap AS VARCHAR(256)
--SET @Ldap = 'LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local'
 
DECLARE @sql AS VARCHAR(1000)
SET @sql = 'SELECT mail, name, employeeId, displayName
 FROM ''LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local'' 
 where objectClass = ''User'' AND employeeId = ''' + @StaffNumber + ''
 
PRINT @sql
 
PRINT '--------------------------------'
 
SET @sql = 'select [name] AS Username, mail, employeeId, displayName  FROM OPENROWSET(''ADSDSOObject'',
 ''adsdatasource;'', ''SELECT mail, name, employeeId, displayName
 FROM ''LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local'' 
 where objectClass = ''User'' AND employeeId = ''' + @StaffNumber + ''')'
 
PRINT @sql
 
EXEC('select [name] AS Username, mail, employeeId, displayName  FROM OPENROWSET(''ADSDSOObject'',
 ''adsdatasource;'', ''SELECT mail, name, employeeId, displayName
 FROM ''LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local'' 
 where objectClass = ''User'' AND employeeId = ''' + @StaffNumber + ''')' )
 
Results from the above are;
 
SELECT mail, name, employeeId, displayName
 FROM 'LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local' 
 where objectClass = 'User' AND employeeId = '4008724
--------------------------------
select [name] AS Username, mail, employeeId, displayName  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT mail, name, employeeId, displayName
 FROM 'LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local' 
 where objectClass = 'User' AND employeeId = '4008724')
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'LDAP'.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ')'.

Open in new window

0
hijiki7777
Asked:
hijiki7777
  • 3
  • 3
1 Solution
 
mvgeertruyenCommented:
You have some syntax errors... I gave it a quick go and I think you should be able to simplify and just paste the below in a query editor. (run from the server, with the needed permissions)
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,''
,'SELECT givenName,sn, displayName, employeeId FROM
''LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local'' where objectCategory=''person'' AND employeeId = ''4008724 ''')

Open in new window

0
 
hijiki7777Author Commented:
May I answer my own question?
Getting the quotation marks correct is hard work, but I did it in the end.
I had to put the results in a temporary table.
However I only want 1 row back, so if there is a better way of getting the results, let me know.
DECLARE @TempUsers TABLE(
Username VARCHAR(256)
, Email VARCHAR(256)
, StaffNumber VARCHAR(256)
, DisplayName VARCHAR(256)
)
 
INSERT INTO @TempUsers (Username, Email, StaffNumber, DisplayName)
EXEC('select [name], mail, employeeId, displayName  FROM OPENROWSET(''ADSDSOObject'',
 ''adsdatasource;'', ''SELECT mail, name, employeeId, displayName
 FROM ''''LDAP://OU=Users,OU=Data Management,DC=uk,DC=corp,DC=local'''' 
 where objectClass = ''''User'''' AND employeeId = ''''' + @StaffNumber + ''''''')' )

Open in new window

0
 
hijiki7777Author Commented:
mvgeertruyen, just seen your reply.
The difficulty I was having was putting in a parameter, and I notice that you have hard coded yours in.
Try finding the solution without hardcoding.
Actually I have done that now for you.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mvgeertruyenCommented:
The above example will return one row and honor the 1000 limit (because the where clause is actually passed on the AD rather then getting everything from AD and then filtering in SQL)

Rgds
0
 
hijiki7777Author Commented:
You are right, but in your solution you hard coded the parameter, ie

AND employeeId = ''4008724 '''

But I had already previous done that, so I was asking a different question.
I was asking how to put in a parameter without hard coding, that was the tricky part. How do you make employeeId = @StaffNumber?
The solution I reached was much harder to work out for that reason.
Try your solution without the hard coding and you will see what I mean.
0
 
mvgeertruyenCommented:
You're right - missed that part of the question while testing. Indeed; the Exec is an option to avoid the hard coding.

Good luck with your app!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now