asked on

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

mvgeertruyen

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

ASKER CERTIFIED SOLUTION

hijiki7777

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hijiki7777

ASKER

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.

mvgeertruyen

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

hijiki7777

ASKER

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.

mvgeertruyen

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!