Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
Головченко Артем
Member

Откуда: Днепр
Сообщений: 9
Хочу поделиться скриптом. Думаю некоторым он будет очень полезен (сам искал в интернете - находил только рекомендации по использованию платного продукта Microsoft Identity Integration Server (MIIS). Но иногда это и дорого и не нужно в принципе.)

Была поставлена следующая задача. В офисе есть программа для отдела кадров(Штатное расписание) в которой есть список сотрудников с личной и рабочей информацией(через нее же заводят новых сотрудников в Active Directory. ). Программа хранит всю информацию в базе данных Microsoft SQL. Т.к. инфрмация о сотрудниках меняется ее необходимо синхронизировать с данными в Active Directory. Собственно это и есть главная задача этого скрипта.

Скрипт достаточно прост и хорошо комментирован. Любой сможет переделать его под свои нужды.

PS: единственное, что заметил (на одном компьютере) - при открытии скрипта в обычном текстовом редакторе - он совершенно не читабелен. Я скрипт редактировал в программе notepad++ (http://notepad-plus-plus.org/download/v6.2.3.html) у меня никаких проблем не возникало.

К сообщению приложен файл (Sync from SQL to AD v2.2.vbs - 16Kb) cкачать
16 янв 13, 01:47    [13775650]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
Zoria
Member

Откуда:
Сообщений: 15266
туда что, даже кто-то уже нажимал??
16 янв 13, 17:49    [13780235]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
st_st
Member

Откуда: Tasm 5.0
Сообщений: 2392
Zoria
туда что, даже кто-то уже нажимал??


Скучали :)
16 янв 13, 18:09    [13780370]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
Головченко Артем
Member

Откуда: Днепр
Сообщений: 9
Извеняюсь за vbs-расширение (сам понимаю что страшно запускать чужой скрипт.) Вот текст скрипта



'********************************************************************************
'Script Name: Sync from SQL to AD
'Date: 11.01.2013
'Author: GolovchenkoAO
'Version: 2.2
'
'Edit with: Notepad++ link: http://notepad-plus-plus.org/download/v6.2.3.html
' Замечания: 1) 'Адрес - st - Область,край (в поле вписано Черновицкое отделение) - не нашел описания ограничения символов для этого поля
' 2) Не нашел в AD поля otherMailbox (в ADSI Edit - все показывает)
'Добавлено: Обработка ошибок и отправление уведомления на почту (без авторизации на smtp)
'
'Описание
'Скрипт подключается к базе данных Microsoft SQL сервера и делает SELECT атрибутов из таблицы с пользователями.
'Далее подключается по LDAP к Active Directory и вносит изменения в учетную запись каждого пользователя
'
'
'Главные поля в таблице - это "CN" и "OU". Скрипт находит пользователя путем соединения этих двух полей и
'присоединение третьей части - это домен пользователя.
'Все это делается в разделе: Подключение к LDAP и поиск объекта (пользователя)
'
'Для поиска полей в которые необходимо вносить изменения в AD - можно использовать ADSI Edit
' example http://gallery.technet.microsoft.com/scriptcenter/a6a9d2a4-705f-4e37-86db-33caca7473cb

' Links:
' Attributes Descriptions: http://msdn.microsoft.com/en-us/library/ms675090(VS.85).aspx
' КАК Использовать ADSI для задания атрибутов каталога LDAP http://support.microsoft.com/kb/260251
'
On Error Resume Next

'ForTest send Error Email
'Dim Rate
'Dim PeriodicRate
'Dim Periods
'Rate = 18
'Periods = 0
'PeriodicRate = Rate / Periods
'***********************

Const ADS_PROPERTY_UPDATE = 2 'обновление AD
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

'Указываем компьютер и базу к которой подключаемся
'Source - имя компьютера,Catalog - имя БД
objConnection.Open "Provider=SQLOLEDB;Data Source=IT-36;" & "Trusted_Connection=Yes;Initial Catalog=db1;"

'Делаем запрос к базе данных
objRecordSet.Open "SELECT * FROM [db1].[dbo].[AD_users]", objConnection, adOpenStatic, adLockOptimistic

'for debug
'WScript.Echo "Connect to DB is Done"
'http://www.myitforum.com/forums/VBscript-to-run-SQL-Query-m207976.aspx

objRecordSet.MoveFirst

'**************************************************
'Начинаем цикл по записи данных в Active Directory
'**************************************************
Do Until objRecordSet.EOF

'********************************* Поля в Штатном расписании и AD*****************************************
'(max characters) ODB - ADSI - AD
'(256)ФИО - displayName - Выводимое имя
'(64 - 2003, 128 - 2008)Должность - title - Должность
'(256)Логин - sAMAccountName
'(128)Город - l - Город (на msdn искать по Locality-Name)
'Адрес - st - Область,край (в поле вписано Черновицкое отделение) - не нашел описания ограничения символов для этого поля
'(64)Внутр.Телефон - otherIpPhone - Доп.Телеофн
'(64)Ip-телефон - ipPhone - Ip-телефон
'(64)Мобильный телефон - mobile - Мобильный
'(64)Городской телефон - telephoneNumber - Номер Телефона
'(64)Факс - facsimileTelephoneNumber - Факс
'(256)e-mail(основной) - mail - Эл.Почта
'(256)e-mail(дополнительный) - otherMailbox - ???
'(64)Подразделение - department - Отдел
'distinguishedName - DN (искать по Obj-Dist-Name) - ограничение не указано
'***********************************************************************************************************


'**************************Порядок столбцов в моей таблице***************************************************
'CREATE TABLE [dbo].[AD_users](
' [displayName] [varchar](256) NULL, --Выводимое дело 0
' [title] [varchar](128) NULL, --Должность 1
' [sAMAccountName] [varchar](20) NULL, --sam\login 2
' [l] [varchar](128) NULL, --Город 3
' [otherIpPhone] [varchar](64) NULL, --внутренний 4
' [ipPhone] [varchar](64) NULL, --ip-телефон 5
' [mobile] [varchar](64) NULL, --мобильный 6
' [telephoneNumber] [varchar](64) NULL, --Городской телефон 7
' [facsimileTelephoneNumber] [varchar](64) NULL, --факс 8
' [mail] [varchar](256) NULL, --E-mail (основной) 9
' [otherMailbox] [varchar](256) NULL, --E-mail (дополнительный) 10
' [department] [varchar](64) NULL, 11
' [OU] [varchar] (max) NULL -- Путь OU 12
' [CN] [varchar](256) NULL --имя которое сразу видно в AD. которое необходимо для LDAP - идет перед OU 13
') ON [PRIMARY]
'***************************************************************************************************************

'Заносим информацию по логину и OU в переменные которые будут применены в коде ниже (при подключении к LDAP
Dim strName,StrContainer
strName = objRecordSet.Fields.Item(13)
strContainer = objRecordSet.Fields.Item(12)

'for debug
'WScript.Echo strContainer
'WScript.Echo objRecordSet.Fields.Item(4)

'***********************************************
'* Подключение к LDAP и поиск объекта (пользователя) *
'***********************************************
'for debug
'WScript.Echo "LDAP://cn=" & strName & "," & strContainer' & "," objRootDSE.Get("defaultNamingContext")

Set objRootDSE = GetObject("LDAP://rootDSE")
If strContainer = "" Then
Set objItem = GetObject("LDAP://" & _
objRootDSE.Get("defaultNamingContext"))
Else
Set objItem = GetObject("LDAP://cn=" & strName & "," & strContainer & "," & _
objRootDSE.Get("defaultNamingContext"))
End If


'For debug
'WScript.Echo "Изменение данных пользователя: " & strName & "," & strContainer
'WScript.Echo VbCrLf & "** General Properties Page**"
'WScript.Echo objRecordSet.Fields.Item(6)




'***************************************************************************************************
'*Цикл по записи данных в Active Directory. Сравниваем данные хранящиеся в SQL с данными в AD. Если данные отличаются заменяем их данными из SQL
'***************************************************************************************************
'Подпроцедуры в конце скрипта

Call ad_displayName_diff
Call ad_title_diff
Call ad_sAMAccountName_diff
Call ad_l_diff
Call ad_otherIpPhone_diff
Call ad_IpPhone_diff
Call ad_mobile_diff
Call ad_telephoneNumber_diff
Call ad_facsimileTelephoneNumber_diff
Call ad_mail_diff
Call ad_otherMailbox_diff
Call ad_department_diff
'for debug

'Cint - Конвертирует Ложь\Истина в 0\-1
'WScript.Echo "User: " & objRecordSet.Fields.Item(0) & "Mail is Empty: " & Cint(IsNull(objRecordSet.Fields.Item(2)))

For n = 0 To objRecordSet.Fields.Count - 1

Next
objRecordSet.MoveNext
loop
objConnection.Close
'**************************************************
'Заканчиваем цикл по записи данных в Active Directory
'**************************************************

'При возникновении ошибки высылаем письмо администратору
If Err.number <> 0 Then
Call send_mail(Err.number, Err.description, Err.source)
End if



WScript.Echo "Sync Finish"


'*************************************************Конец********************************************************







'*******************************************Subroutines(Подпроцедуры) и Функции********************************************
'Подпроцедура сравнения поля Выводимое имя
Sub ad_displayName_diff()
if objItem.displayName = max256(objRecordSet.Fields.Item(0)) Then
Elseif (objRecordSet.Fields.Item(0) = "" or Cint(IsNull(objRecordSet.Fields.Item(0))) = "-1") Then ' Empty or NULL
objItem.Put "displayName", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(0))) = "0" Then 'not NULL
objItem.Put "displayName", CStr(max256(objRecordSet.Fields.Item(0)))
objItem.SetInfo
End if
End Sub


'Подпроцедура сравнения поля Должность
Sub ad_title_diff()
if objItem.title = max64(objRecordSet.Fields.Item(1)) Then
Elseif (objRecordSet.Fields.Item(1) = "" or Cint(IsNull(objRecordSet.Fields.Item(1))) = "-1") Then ' Empty or NULL
objItem.Put "title", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(1))) = "0" Then 'not NULL
objItem.Put "title", CStr(max64(objRecordSet.Fields.Item(1)))
objItem.SetInfo
End if
End Sub

'Подпроцедура сравнения поля Sam\Login
Sub ad_sAMAccountName_diff()
if objItem.sAMAccountName = max20(objRecordSet.Fields.Item(2)) Then
Elseif (objRecordSet.Fields.Item(2) = "" or Cint(IsNull(objRecordSet.Fields.Item(2))) = "-1") Then ' Empty or NULL
objItem.Put "sAMAccountName", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(2))) = "0" Then 'not NULL
objItem.Put "sAMAccountName", CStr(max20(objRecordSet.Fields.Item(2)))
objItem.SetInfo
End if
End Sub

'Подпроцедура сравнения поля Город
Sub ad_l_diff()
if objItem.l = max128(objRecordSet.Fields.Item(3)) Then
Elseif (objRecordSet.Fields.Item(3) = "" or Cint(IsNull(objRecordSet.Fields.Item(3))) = "-1") Then ' Empty or NULL
objItem.Put "l", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(3))) = "0" Then 'not NULL
objItem.Put "l", CStr(max128(objRecordSet.Fields.Item(3)))
objItem.SetInfo
End if
End Sub

'Подпроцедура сравнения поля внутренний
Sub ad_otherIpPhone_diff()
if objItem.otherIpPhone = max64(objRecordSet.Fields.Item(4)) Then
Elseif (objRecordSet.Fields.Item(4) = "" or Cint(IsNull(objRecordSet.Fields.Item(4))) = "-1") Then ' Empty or NULL
objItem.Put "otherIpPhone", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(4))) = "0" Then 'not NULL
objItem.Put "otherIpPhone", CStr(max64(objRecordSet.Fields.Item(4)))
objItem.SetInfo
End if
End Sub

'Подпроцедура сравнения поля ip-телефон
Sub ad_ipPhone_diff()
if objItem.ipPhone = max64(objRecordSet.Fields.Item(5)) Then
Elseif (objRecordSet.Fields.Item(5) = "" or Cint(IsNull(objRecordSet.Fields.Item(5))) = "-1") Then ' Empty or NULL
objItem.Put "IpPhone", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(5))) = "0" Then 'not NULL
objItem.Put "IpPhone", CStr(max64(objRecordSet.Fields.Item(5)))
objItem.SetInfo
End if
End Sub

'мобильный
Sub ad_mobile_diff()
if objItem.mobile = max64(objRecordSet.Fields.Item(6)) Then
Elseif (objRecordSet.Fields.Item(6) = "" or Cint(IsNull(objRecordSet.Fields.Item(6))) = "-1") Then ' Empty or NULL
objItem.Put "mobile", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(6))) = "0" Then 'not NULL
objItem.Put "mobile", CStr(max64(objRecordSet.Fields.Item(6)))
objItem.SetInfo
End if
End Sub

'Подпроцедура сравнения поля "Телефон"

Sub ad_telephoneNumber_diff()
if objItem.telephoneNumber = max64(objRecordSet.Fields.Item(7)) Then
Elseif (objRecordSet.Fields.Item(7) = "" or Cint(IsNull(objRecordSet.Fields.Item(7))) = "-1") Then ' Empty or NULL
objItem.Put "telephoneNumber", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(7))) = "0" Then 'not NULL
objItem.Put "telephoneNumber", CStr(max64(objRecordSet.Fields.Item(7)))
objItem.SetInfo
End if
End Sub


'Подпроцедура сравнения поля факс

Sub ad_facsimileTelephoneNumber_diff()
if objItem.facsimileTelephoneNumber = max64(objRecordSet.Fields.Item(8)) Then
Elseif (objRecordSet.Fields.Item(8) = "" or Cint(IsNull(objRecordSet.Fields.Item(8))) = "-1") Then ' Empty or NULL
objItem.Put "facsimileTelephoneNumber", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(8))) = "0" Then 'not NULL
objItem.Put "facsimileTelephoneNumber", CStr(max64(objRecordSet.Fields.Item(8)))
objItem.SetInfo
End if
End Sub

'Подпроцедура сравнения поля "Почта"
Sub ad_mail_diff()
'Сравниваем почту
if objItem.mail = max256(objRecordSet.Fields.Item(9)) Then
'WScript.Echo objRecordSet.Fields.Item(0) & " Mail is match"
Elseif (objRecordSet.Fields.Item(9) = "" or Cint(IsNull(objRecordSet.Fields.Item(2))) = "-1") Then ' Empty or NULL
'WScript.Echo "User: " & objRecordSet.Fields.Item(0) & "Clear MAIL attribute in AD"
'objItem.Put "mail", "field is empty or NULL "
'objItem.PutEx ADS_PROPERTY_UPDATE, "mail", 0
objItem.Put "mail", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(9))) = "0" Then 'not NULL
'WScript.Echo objRecordSet.Fields.Item(0) & "Mail is NOT match" & "In SQL: " & objRecordSet.Fields.Item(2) & "In AD: " & objItem.mail
objItem.Put "mail", CStr(max256(objRecordSet.Fields.Item(9)))
objItem.SetInfo
End if
End Sub



'Процедура сравнения поля "Дополнительный E-mail"
Sub ad_otherMailbox_diff()
if objItem.otherMailbox = max256(objRecordSet.Fields.Item(10)) Then
Elseif (objRecordSet.Fields.Item(10) = "" or Cint(IsNull(objRecordSet.Fields.Item(10))) = "-1") Then ' Empty or NULL
objItem.Put "otherMailbox", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(10))) = "0" Then 'not NULL
objItem.Put "otherMailbox", CStr(max256(objRecordSet.Fields.Item(10)))
objItem.SetInfo
End if
End Sub


'Процедура сравнения поля Отделение
Sub ad_department_diff()
if objItem.department = max64(objRecordSet.Fields.Item(11)) Then
Elseif (objRecordSet.Fields.Item(11) = "" or Cint(IsNull(objRecordSet.Fields.Item(11))) = "-1") Then ' Empty or NULL
objItem.Put "department", " "
objItem.SetInfo
elseif Cint(IsNull(objRecordSet.Fields.Item(11))) = "0" Then 'not NULL
objItem.Put "department", CStr(max64(objRecordSet.Fields.Item(11)))
objItem.SetInfo
End if
End Sub





Sub send_mail(num, desc, src)
'Microsoft desc
'http://msdn.microsoft.com/en-us/library/ms526318(v=exchg.10).aspx
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "from@domain.com"
objEmail.To = "to@domain.com"
objEmail.Subject = "Error Script: Sync from SQL to AD"
objEmail.Textbody = "Error Script: Sync from SQL to AD" &" "& num & desc & src

'SMTP-сервер и порт для отправки почты
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"smtp.domain.com"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
End Sub






Function max20(str)
max20 = Left(str,20)
End Function

Function max64(str)
max64 = Left(str,64)
End Function

Function max128(str)
max128 = Left(str,128)
End Function

Function max256(str)
max256 = Left(str,256)
End Function

К сообщению приложен файл (Sync from SQL to AD v2.2.txt - 13Kb) cкачать
16 янв 13, 19:29    [13780767]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
pokladsemen
Member

Откуда:
Сообщений: 6
Этот скрипт только обновляет данные. А добавление/удаление пользователей не подскажите как сделать?

Модератор: Тема перенесена из форума "HTML, JavaScript, VBScript, CSS".


Сообщение было отредактировано: 16 май 13, 16:35
16 май 13, 16:09    [14306481]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Боже VB. Когда же он сдохнет.
Не T-SQL не PowerShell, а вижуалсраньгосподня.

Головченко Артем, вы понимаете что если это написать по нормальному, на нормальном языке то оно будет занимать как минимум в 20 раз меньше. И тогда люди начнут это читать.

Фтопку месиво копипасты. Стыдно должно быть.
На форуме 100500 раз обсуждалась эта банальщина.
16 май 13, 23:27    [14308739]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
Головченко Артем
Member

Откуда: Днепр
Сообщений: 9
Если у вас есть ссылки на готовые скрипты которые выполняют тот же самый функционал - поделитесь.
На момент когда мне поставили задачу - я смог это реализовать только таким способом. Уверен что данный скрипт можно было написать поизящней.
Перед тем как самому написать такой скрипт - я потратил кучу времени но готового решения не нашел. А вот желающих такое осуществить на просторах интернета достаточно.

Когда закончил - решил поделиться. Кому надо будет - потратит своё драгоценное время и прочитает и при необходимости переделает под себя.
27 июл 14, 00:25    [16362126]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
aleks2
Guest
Головченко Артем
я потратил кучу времени но готового решения не нашел. А вот желающих такое осуществить на просторах интернета достаточно.

Когда закончил - решил поделиться. Кому надо будет - потратит своё драгоценное время и прочитает и при необходимости переделает под себя.


Даже если ты поратил на это фсю жисть - говнокод остается говнокодом.
Достаточно выполнить запрос к LDAP прямо из MS SQL. Будет быстрее и проще.
begin tran LDAP
	if OBJECT_ID('dbo.[LDAP_SAMName_to_DisplayName]') is not null 
--		TRUNCATE TABLE  dbo.[LDAP_SAMName_to_DisplayName]
		DELETE dbo.[LDAP_SAMName_to_DisplayName]
	else begin
		CREATE TABLE dbo.[LDAP_SAMName_to_DisplayName] (
			[DisplayName] [nvarchar] (256) COLLATE Cyrillic_General_CI_AS NOT NULL ,
			[AccountName] [nvarchar] (256) COLLATE Cyrillic_General_CI_AS NOT NULL ,
			[SAMAccountName] [nvarchar] (128) COLLATE Cyrillic_General_CI_AS NOT NULL ,
			CONSTRAINT [PK_LDAP_SAMName_to_DisplayName] PRIMARY KEY  CLUSTERED ([AccountName]) 
		)	
		CREATE  UNIQUE  INDEX [IX_LDAP_SAMName_to_DisplayName] ON [dbo].[LDAP_SAMName_to_DisplayName]([SAMAccountName]) 
          end

	declare @sql_template nvarchar(4000), @sql nvarchar(4000), @fqdn nvarchar(128)

	select @fqdn=str FROM dbo.[isa2006 Глобальные параметры] WHERE ID=2
	if @fqdn is null  SET @fqdn=N''

	set @sql_template='SELECT Name DisplayName, ''' + dbo.LDAP_DomainName()+'\''+ SAMAccountName [AccountName], SAMAccountName '
		+' FROM OPENQUERY(ADSI, ''select Name, sAMAccountName from ''''LDAP://'
		+dbo.[LDAP_FQDN->DCs](@fqdn)
		+''''' where objectClass = ''''User'''' and objectCategory=''''Person'''' <<replace>> ORDER BY SAMAccountName ASC '')'
	declare @rc int, @name nvarchar(256)
	set @rc=1
	set @sql=REPLACE(@sql_template, N'<<replace>>', N'')
	while @rc>0 begin
		INSERT INTO dbo.[LDAP_SAMName_to_DisplayName] exec(@sql)
		set @rc=@@rowcount
		if @rc>0 begin
			select top 1 @name = SAMAccountName FROM dbo.[LDAP_SAMName_to_DisplayName]  ORDER BY SAMAccountName DESC
			set @sql=REPLACE(@sql_template, N'<<replace>>', N' AND SAMAccountName>'''''+@name+''''' ')
		end
	end
commit tran LDAP

--	select * from dbo.[LDAP_SAMName_to_DisplayName]
27 июл 14, 10:38    [16362388]     Ответить | Цитировать Сообщить модератору
 Re: Скрипт синхронизации пользователей из Microsoft SQL в Active Directory  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
автор
FROM OPENQUERY(ADSI,

Предварительно требуется создать LINKED SERVER с именем ADSI типа Active Directory Services.

Кстати, вопрос - как решить проблему дефолтного разбиения на страницы по 1000 записей без перенастройки AD?
28 июл 14, 12:00    [16365327]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить