[SQL]從sql server取得AD資訊

2012-03-21

5857
0
SQL

最近有需求要取得AD最後密碼修改日期

紀錄一下過程

STEP 1: 設定linked server

-- http://technet.microsoft.com/zh-tw/library/ms190479.aspx
-- http://www.sqlservercentral.com/Forums/Topic259587-8-2.aspx
-- http://tsuozoe.pixnet.net/blog/post/22419129-sp_addlinkedsrvlogin-%E3%80%81-sp_droplinkedsrvlogin-%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95-
use master 
go
 
 
--增加linked server
--設定linked server登入身分
 
/****** 物件:  LinkedServer [ADSI]    指令碼日期: 12/18/2008 13:43:56 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'account',@rmtpassword='password'
 
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
 
 
 
--刪除linked server
 
EXEC sp_droplinkedsrvlogin 'ADSI', NULL
sp_dropserver 'ADSI'
 
--查詢server列表
select * from sys.servers

STEP 2:開始跟AD查詢資料

-- http://www.codeproject.com/Articles/37094/Building-a-Hybrid-Active-Directory-and-SQL-Table-D#
-- http://www.sqlservercentral.com/Forums/Topic259587-8-1.aspx
-- http://www.myitforum.com/forums/SCCM-WQL-Query-PwdLastSet-m233388.aspx
-- http://www.sqlteam.com/forums/topic.asp?topic_id=108759
-- http://www.dotblogs.com.tw/angi/archive/2008/11/26/6085.aspx
-- http://www.blueshop.com.tw/board/show.asp?subcde=BRD201003101620505Z5
 
use test_db 
go
 
 
 
--預設情況下，SQL Server不允許 OpenQuery，所以用 show advanced option 打開系統預儲程式進階選項並配合下面的 Ad Hoc Distributed Queries 選項   
sp_configure 'show advanced options', 1   
reconfigure with override   
 
--再使用 Ad Hoc Distributed Queries 打開特定分散式查詢選項   
sp_configure 'Ad Hoc Distributed Queries', 1   
reconfigure   
 
 
 
 
--  要處理時間轉換
 
--  1. 將pwdLastset從長整數轉換為日期
--  法一:
--  cast((cast(pwdLastSet as numeric)/ 864000000000.0 - 109207) AS DATETIME)
--  法二:
--  DECLARE    @SourceValue BIGINT
--  SELECT    @SourceValue = 129145609981852500
--  SELECT    @SourceValue = (@SourceValue - 47966688000000000) / 600000000.0
--  SELECT    DATEADD(MINUTE, @SourceValue, '17530101')
 
--  2. 將轉換後的時間轉為本地時間(UTC to LOCAL)
--  DECLARE @LocalDate DATETIME,@UTCDate DATETIME,@LocalDate2 DATETIME 
--  SET @LocalDate = GETDATE()
--  SET @UTCDate = DATEADD(hour, DATEDIFF(hour,GETDATE(),GETUTCDATE()), @LocalDate);
--  SET @LocalDate2 = DATEADD(hour, DATEDIFF(hour,GETUTCDATE(),GETDATE()), @UTCDate);
--  print @LocalDate
--  print @UTCDate
--  print @LocalDate2
 
-- 查詢AD 狀態(法一 :增加Link Server後,使用OPENQUERY)
declare @USRID varchar(6)
set @USRID='123456';
 
select cn,  samAccountName ,
case when pwdLastSet ='0' then null
else DATEADD(hour, DATEDIFF(hour,GETUTCDATE(),GETDATE()) ,cast((cast(pwdLastSet as numeric)/ 864000000000.0 - 109207) AS DATETIME) )
end as pwdLastSetDate 
from
(SELECT  cn,  samAccountName , pwdLastSet
 
FROM OPENQUERY(ADSI, 
 
'SELECT samAccountName, pwdLastSet, cn
 
FROM ''LDAP://test.com'' where objectCategory = ''Person'' and objectClass = ''User'' ') 
 
AS a 
inner join test_db..EMPLOYEE as b on a.cn collate Chinese_Taiwan_Stroke_BIN = b.STAFF
where b.STATUS=1
) c  where cn=@USRID  ORDER BY cn 
 
 
-- 查詢AD 狀態(法二 :使用OPENROWSET)
declare @USRID varchar(6)
set @USRID='123456';
 
select cn,  samAccountName ,
case when pwdLastSet ='0' then null
else DATEADD(hour, DATEDIFF(hour,GETUTCDATE(),GETDATE()) ,cast((cast(pwdLastSet as numeric)/ 864000000000.0 - 109207) AS DATETIME) )
end as pwdLastSetDate 
from
(SELECT  cn,  samAccountName , pwdLastSet
 
FROM OPENROWSET('ADSDSOObject', 'adsdatasource;', 
 
'SELECT samAccountName, pwdLastSet, cn
 
FROM ''LDAP://test.com'' where objectCategory = ''Person'' and objectClass = ''User'' ') 
 
AS a 
inner join test_db..EMPLOYEE as b on a.cn collate Chinese_Taiwan_Stroke_BIN = b.STAFF
where b.STATUS=1
) c  where cn=@USRID  ORDER BY cn

備註

如果OPENQUERY不能使用請參照下列步驟

-- http://hoolihome.blogspot.com/2010/10/test-sqlncli.html
-- sp_serveroption : http://technet.microsoft.com/zh-tw/library/ms178532.aspx
-- sp_addlinkedserver : http://technet.microsoft.com/zh-tw/library/ms190479.aspx
-- OPENQUERY : http://technet.microsoft.com/zh-tw/library/ms188427.aspx
use master
go
 
--預設data access為false
exec sp_serveroption 'INDBT1','data access',false
 
 
SELECT * FROM OPENQUERY (INDBT1, 'SELECT * FROM
testdb.dbo.test1') 
 
--解決方式 : 將data access打開
exec sp_serveroption 'INDBT1','data access',true 
 
--正常顯示
SELECT * FROM OPENQUERY (INDBT1, 'SELECT * FROM
testdb.dbo.test1')

回首頁