Как импортировать конфигурацию Windows Firewall в SQL Server. Часть 1.

Article
06/10/2011

Это достаточно практическая задача, на примере которой можно к тому же поупражняться в различных SQL и XML-запросах. Идею подсказал следующий пост на Технете с точки зрения аудита текушей конфигурации файрвола. Из стандартного интерфейса Windows Firewall with Advanced Security из Administrative Tools (Start -> Run -> WF.msc):

Рис.1

непросто, например, отыскать все правила с одинаковыми именами и сравнить их между собой, чтобы понять, это дубликаты или несмотря на одинаковые имена они обозначают разные действия. Можно вывести конфигурацию файрвола на консоль (netsh advfirewall firewall show rule name=all verbose):

Рис.2

но это не облегчает задачу. Хотелось бы сохранить ее в формате, поддерживающем язык запросов. Например, в XML (XQuery) или в виде реляционной таблицы (SQL). Этим я и предлагаю заняться в данном посте. Сохраняем выдачу Рис.2 в текстовый файл:

exec sp_configure @configname = 'show advanced', @configvalue = 1

reconfigure

exec sp_configure @configname = 'xp_cmdshell', @configvalue = 1

reconfigure

exec xp_cmdshell 'netsh advfirewall firewall show rule name=all verbose > c:\temp\aaa.txt'

Скрипт 1

который теперь надо каким-либо образом получить на стороне SQL Server. Для начала я собираюсь получить его в виде таблицы строк как они есть на Рис.2 за исключением пустых и разделителей из минусов. Для этого я воспользуюсь функцией T-SQL openrowset(bulk, ...). Эта функция может засосать содержимое файла в виде varbinary(max) / (n)varchar(max), либо попытаться разделить это содержимое на строки и колонки на основе передаваемого ей форматного файла. Я изобразил следующий формат:

declare @x xml = N'

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

Скрипт 2

который должен объяснить bulk-провайдеру, что табличный результат будет состоять из одной колонки nvarchar(1000), в которую помещаются строки Рис.2, а разделителем строк выступает символ новой строки.

К сожалению, вплоть до текущей версии (2008 R2) функция openrowset не воспринимает форматирование в виде переменной типа XML. Этот XML для нее необходимо сохранить в файл. Вплоть до текущей версии в T-SQL не имеется простых средств для экспорта данных из SQL Server в файл, подобных функции openrowset(bulk, ...) для импорта. Требуется привлекать утилиту командной строки bcp, либо изображать что-нибудь на CLR. Ни то, ни другое я делать не хочу, поэтому для записи переменной @x (Скрипт 2) в файл поступлю со всей сермяжной простотой:

declare @s nvarchar(4000)

set @s = '<?xml version="1.0"?>' + CAST(@x as nvarchar(max))

set @s = REPLACE(@s, '<', '^<'); set @s = REPLACE(@s, '>', '^>') --эскапируем XMLные скобки, т.к. они вызывают желание у командного интерпретатора сделать редирект

set @s = 'echo ' + @s + ' > c:\temp\fff.xml'

exec xp_cmdshell @s

Скрипт 3

Эстеты могут почитать статью «Как экспортнуть XML в файл» из раннего меня. Теперь все готово, чтобы вызвать функцию openrowset и получить конфигурацию файрвола в виде массива строк:

if OBJECT_ID('tempdb.dbo.#t', 'U') is not null drop table #t

select IDENTITY(int, 1, 1) as id, NULL as parent_id, s as value into #t from openrowset(bulk 'c:\temp\aaa.txt', formatfile = 'c:\temp\fff.xml') t(s) where CHARINDEX(':', s, 1) <> 0 --удаляются пустые строки, строки-разделители вида ----------------------------------------------------------------------, строка Ok. в конце и т.д.

select * from #t

Скрипт 4

Все вместе в сборе:

Рис.3

Прекрасно. Первоначально у меня была идея сразу разбить выдачу Рис.2 на колонки RuleName, Enabled, Direction и т.д. Я даже написал для этого замечательный файл форматирования:

<?xml version="1.0" encoding="UTF-8"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

Скрипт 5

Засада состоит в том, что элементы внутри Rule Name не являются обязательными. Например, для некоторых правил могут отсутствовать LocalPort и RemotePort. Нет, что бы поставить LocalPort: <пустота>, они вообще опущены внутри правила L Формат Скрипт 5 предполагает жесткую структуру, и когда он чего-то не находит, все последующее форматирование съезжает. Поэтому я решил превратить выдачу Рис.2 в XML. Он будет состоять из родительских элементов Rule с атрибутом Name. Хотя Name можно сделать подэлементом - дело вкуса. Внутри каждого элемента Rule будут располагаться его дочерние элементы Enabled, Direction и все остальные, какие у него есть. Для начала в таблице #t (Рис.3) стоит установить явную связь между каждой родительской строкой, начинающейся с Rule, и идущими за ней ее дочерними строками вплоть до нового правила (следующей родительской строки, начинающейся с Rule).

with cteParent as (select * from #t where value like 'Rule Name%'),

cteChild as (select * from #t where value not like 'Rule Name%'),

cteFindClosestParent(id_1, parent_id_1, value_1, id_2, parent_id_2, value_2, i) as (select *, ROW_NUMBER() over (partition by cteChild.id order by cteParent.id desc) i from cteChild join cteParent on cteParent.id < cteChild.id)

update cteFindClosestParent set parent_id_1 = id_2 where i = 1

Скрипт 6

Дочерние строки в поле parent_id получают id своей верхней родительской строки Rule:

Рис.4

Теперь я добавлю в таблицу колонку name и поделю значение колонки value по принципу: все, что в ней находится слева от символа :, отправляется в колонку name, а все, что справа - остается в колонке value. Поскольку колонка name вскорости должна превратиться в имена XML-элементов, недопустимый для имени элемента пробел заменяется на корыто (подчеркивание).

alter table #t add name varchar(50)

update #t set name = left(value, CHARINDEX(':', value, 1) - 1), value = ltrim(substring(value, CHARINDEX(':', value, 1) + 1, len(value)))

update #t set name = replace(name, ' ', '_')

select * from #t

Скрипт 7

Рис.5

Превосходно. Осталось превратить результат Рис.5 в какой-нибудь такой XML

<WF>

<Profiles>Public</Profiles>

<Edge_traversal>No</Edge_traversal>

<Program>C:\Program Files (x86)\Microsoft Lync\communicator.exe</Program>

<Security>NotRequired</Security>

<Rule_source>Local Setting</Rule_source>

<Action>Allow</Action>

</Rule>

<Profiles>Public</Profiles>

<Edge_traversal>No</Edge_traversal>

<Program>C:\Program Files (x86)\Microsoft Lync\communicator.exe</Program>

<Security>NotRequired</Security>

<Rule_source>Local Setting</Rule_source>

<Action>Allow</Action>

</Rule>

...

</WF>

Скрипт 8

Проблема состоит в том, что конструкция SELECT … FOR XML PATH ориентирована, в основном, на статические правила именования XML-узлов, когда названия элементов и атрибутов заранее известны и фиксированы, т.е. берутся, скажем, из колонок таблицы, а не из ячеек этих колонок в разных строках. Аналогично ведет себя конструкция SELECT … FOR XML EXPLICIT, хотя здесь, например, ее пытались под дурачка пропихнуть в качестве решения, а когда этот номер не прошел, отвечающий стал отстраненно философствовать, что де настоящий XML должен иметь строгую структуру. Как говорится, слив засчитан. Тем не менее, проблема осталась. Мне известны два относительно честных способа ее решения.

Первый - это транспонировать таблицу при помощи оператора PIVOT, превратив повторяющиеся строчки Rule_Name, Description, Enabled, … в колонки, а на результат натравить SELECT … FOR XML.

if OBJECT_ID('FirewallRules', 'U') is not null drop table FirewallRules

select * into FirewallRules from (select ISNULL(parent_id, id) group_id, name, value from #t) t

pivot (min(value) for name in (Rule_Name, [Enabled], Direction, Profiles, [Grouping], LocalIP, RemoteIP, Protocol, LocalPort, RemotePort, Edge_traversal, Program, InterfaceTypes, Security, Rule_source, Action)) p

select * from FirewallRules

Скрипт 9

Оператор PIVOT устроен очень просто. Он берет все поля из его первого select’a:

Рис.6

Поля попадают в одну из трех ролей. 1) Пивотное поле (в данном случае name - for name in (…)). Значения из этой колонки разворачиваются вширь в виде новых колонок. 2) Следующее поле - это значения пивотных (новых) колонок (в данном случае поле value). Оно фигурирует в кач-ве аргумента агрегатной функции (min(value)). 3) Все остальные поля (group_id). По ним происходит неявный group_by. В качестве признака группы использовалось id правила, проставленное в parent_id составляющих это правило записей в Скрипте 6. В результате имеем:

Рис.7

В принципе, мы уже худо-бедно добились поставленной цели, т.к. таблица FirewallRules содержит все правила, определенные в текущей конфигурации файрвола и, чтобы, например, найти, какие из них открывают наружу ТСР-порт 1433, кому и для кого, можно написать:

select * from FirewallRuls where Enabled = 'Yes' and Direction = 'In' and Protocol = 'TCP' and Action = 'Allow' and (LocalPort in ('Any', '1433') or LocalPort is null)

Скрипт 10

Но я все-таки предлагаю (довершить начатое) экспортом в XML, а потом пойти другим путем: сначала получить XML и превратить его затем в таблицу. Этими упражнениями мы займемся в следующей серии.

Алексей Шуленин

Share via

Как импортировать конфигурацию Windows Firewall в SQL Server. Часть 1.

Additional resources