SQL Active Directory Linked Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Active Directory Linked Server

Author

Topic

dhilling
Starting Member

3 Posts

Posted - 2010-04-28 : 11:05:39

I’m a bit new @ using sql 2008 openquery to return Active Directory information about users. But, when I create the following Linked Server ADSI (linked to AD) then create a stored Procedure to query it. (below)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_AD_GetUsers]
(
@ou nvarchar(100),
@domain nvarchar(100),
@com nvarchar(100)
)
as
declare @ldap as nvarchar(2000)
declare @sql as nvarchar(2000)
declare @user as nvarchar(10) = 'user'
declare @computer as nvarchar(10)='computer'

SET @ldap = 'LDAP://DC=' + @domain +',DC='+@com +''

set @sql = 'SELECT samAccountName As WinNT_ID
,sn As Last_Name
,givenName As First_Name
,displayName AS Display_Name
,mail As Email_Address
,ISNULL(manager, ''N/A'') As Manager
,ISNULL(title, ''N/A'') As Job_Title
,ISNULL(telephonenumber, ''N/A'') As Phone_Number
,ISNULL(mobile, ''N/A'') As Mobile_Number
,ISNULL(facsimiletelephonenumber, ''N/A'') As Fax_Number
,ISNULL(physicalDeliveryOfficeName, ''N/A'') As Office
,ISNULL(department, ''N/A'') AS Department
,streetAddress As Stree_Address
,l As City
,st As State
,postalCode As Zip_Code
FROM OPENQUERY(ADSI, ''SELECT samAccountName, sn, givenName, title, displayName
,department,mail, manager,telephonenumber, mobile, facsimiletelephonenumber
,physicalDeliveryOfficeName,l,streetAddress,st,postalCode
FROM ' + char(39) + char(39) + @ldap + char(39) + char(39) + '
WHERE objectClass=' + char(39) + char(39) + @user + char(39) + char(39) + ' AND objectClass<>' + char(39) + char(39) + @computer + char(39) + char(39) + '
'') AS tblADSI
WHERE sn IS NOT NULL
AND mail IS NOT NULL
ORDER BY sn'

EXECUTE sp_executesql @sql

If I run this on the actual server in my webapp or just from SQL itself it works great! But if I try to run this on my local machine while connected to the SQL box it doesn’t work as well as my webapp that calls it. I’ve tried using the impersonate sql account to AD, be made using this context: (ad account…even tried serveradmin…the same account that’s used on the local SQL) nothing seems to work. I get this error that must be a double hop situation. Sorry this is so long, but I’ve been busting my brain trying to get this to work.

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samAccountName, sn, givenName, title, displayName
,department,mail, manager,telephonenumber, mobile, facsimiletelephonenumber
,physicalDeliveryOfficeName,l,streetAddress,st,postalCode
FROM 'LDAP://DC=domainname,DC=com'
WHERE objectClass='user' AND objectClass<>'computer'
" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources