首页 > 解决方案 > SQL LDAP 查询问题

问题描述

最近受命查看此问题,但无法弄清楚为什么它不起作用。这是在我之前由其他人创建的,我不是经验丰富的 dba。无论如何,问题是我们的联系人目录中没有更新一些用户的详细信息,而其他的则根本不存在。我做了一些挖掘,发现了一个使用 ldap 查询来命中 AD 并将用户拉回来的 SP。这些用户都存在于 AD 中,并且看不到他们没有通过的任何其他问题。proc 语法的一部分是使用 ADSI 作为链接服务器的 openquery,我知道它的 1000 行限制。我将在下面发布proc代码,如果有人能看到任何明显的东西,请赐教。

USE [Web_Repository]
GO
/****** Object:  StoredProcedure [dbo].[get_activedirectory]    Script Date: 08/03/2020 11:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[get_activedirectory]
as
--GET ACTIVE DIRECTORY USERS OF ENABLED ACOUNTS
declare @i integer, @accname varchar(1000), @sql varchar(8000)

if exists (select * from sysobjects where name = 'activedirectory_temp' ) Drop Table activedirectory_temp

CREATE TABLE [activedirectory_temp] (
    [sn] [varchar] (256)   NULL ,
    [GivenName] [varchar] (256)   NULL ,
    [displayName] varchar (256) NULL,
    [Department] [varchar] (256)   NULL ,
    [Title] [varchar] (256)   NULL ,
    [SAMAccountName] [varchar] (256)   NULL ,
    [Company] [varchar] (256)   NULL ,
    [mail] [varchar] (256)   NULL ,
    [physicalDeliveryOfficeName] [varchar] (256)   NULL ,
    [enabled] bit NULL, 
    [wWWHomePage] [varchar] (256) NULL
) ON [PRIMARY]

set @sql = 'insert into dbname.dbo.activedirectory_temp select top 901 sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName,0 enabled, wWWHomePage from openquery (ADSI,''SELECT sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName, wWWHomePage FROM ''''LDAP://DC=Group,DC=Net'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND (UserAccountControl = ''''512'''' OR UserAccountControl = ''''640'''' or company = ''''zeroc'''' or SAMAccountName = ''''137499'''' or SAMAccountName = ''''157067'''' or SAMAccountName = ''''WebIE10test'''') ORDER BY SAMAccountName'')'
set @sql = replace(@sql,'dbname',db_name())
exec(@sql)

set @i = @@rowcount

while @i <> 0
begin
    set @accname = (select max(SAMAccountName) from activedirectory_temp)
    --OU=Group Less Restricted
    set @sql = 'insert into dbname.dbo.activedirectory_temp select top 901 sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName,0 enabled, wWWHomePage from openquery (ADSI,''SELECT sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName, wWWHomePage FROM ''''LDAP://DC=Group,DC=Net'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND (UserAccountControl = ''''512'''' OR UserAccountControl = ''''640'''' or company = ''''zeroc'''' or SAMAccountName = ''''137499'''' or SAMAccountName = ''''157067'''' or SAMAccountName = ''''WebIE10test'''') AND SAMAccountName > ''''####'''' ORDER BY SAMAccountName'')'
    set @sql = replace(@sql,'dbname',db_name())
    set @sql = replace(@sql,'####',@accname)
    exec(@sql)

    set @i = @@rowcount
end

--REMOVE ALL ADMIN & DUMMY ACCOUNTS
update activedirectory_temp
set enabled = 1
where sn > ''
and isnull(givenname,'') > ''
and isnull(title,'') not in ('Resource','Additional')
and isnull(sn,'') <> 'Template'
--and mail is not null
--and left(mail,1) <> '_'

delete from activedirectory_temp
where enabled <> 1

--LOAD DATA INTO LIVE TABLE IF SUCCESSFULL
set @i = (select count(*) from activedirectory_temp)


/*  MN 23/08/2016
    Insert changed to a select distinct to remove duplicate problem for sort order difference between LDAP and SQL
    ie SQL orders local variable @accname (SAMAccountName) natpaint -> N-E-H, whilst LDAP orders N-E-H -> natpaint 
    This causes a duplicate when selecting 901 rows into the temp table  */

if @i > 0
begin
    truncate table activedirectory

    insert into activedirectory (sn,GivenName,displayName,Department,Title,SAMAccountName,Company,mail,physicalDeliveryOfficeName, wWWHomePage)
    select distinct sn,GivenName,displayName,Department,Title,SAMAccountName,Company,mail,physicalDeliveryOfficeName, wWWHomePage
    from activedirectory_temp
end

if exists (select * from sysobjects where name = 'activedirectory_temp' ) Drop Table activedirectory_temp

标签: sqlstored-proceduresldaplinked-server

解决方案


经过进一步调查,我们发现这是一个单独的进程/作业导致了问题。


推荐阅读