Ограничение на клиентский IP

Надысь обсуждали с Андреем Бешковым задачу ограничения доступа пользователя на SQL Server в зависимости от IP машины, с которой он пытается установить соединение. Условно говоря, есть пул доверенных IPшников, которым разрешается коннектиться к SQL Server, остальные в сад. Предположим, по адресу 192.168.0.1 сидит какой-нибудь матерый гад alexejs, которого ни под каким видом нельзя допускать на SQL Server. Андрюха как человек ITProшный немедленно полез конфигурить файрвол и изобразил примерно такую команду:

netsh advfirewall firewall add rule name="No SQL Connection from 192.168.0.1" dir=in action=block protocol=TCP localport=1433 service=MSSQLSERVER enable=yes profile=ANY localip=192.168.0.1

Скрипт 1

 

image001

рис.1

Я в ней все равно ничего не понимаю, а читать netsh advfirewall firewall add rule /? мне лень.

После этого действительно с машины 192.168.0.1 приконнектиться к SQL Serverу на машине, где было сконфигурировано это правило на файрволе, стало нельзя:

image003

рис.2

image005

рис.3

Проверка. Если задисейблить это правило (Start -> Administrative Tools -> Windows Firewall with Advanced Security)

 

image007

рис.4

коннект к серверу проходит нормально:

 

image009

рис.5

Стоит отметить, что правило ограничения в том виде, как мы его сформулировали в Скрипте 1, распространяется только на подключения по TCP/IP:

 

image011

рис.6

Если указать в строке соединения другую сетевую библиотеку (см. http://msdn.microsoft.com/ru-ru/library/system.data.sqlclient.sqlconnection.connectionstring.aspx), запрет удастся обойти. Например,

$cnn = New-Object System.Data.SqlClient.SqlConnection

$cnn.ConnectionString = "server=192.168.0.208;Database=tempdb;User ID=sa;Password=AbraCadabra;Network Library=dbnmpntw"

$cnn.Open();

Скрипт 2

image013

рис.7

И мы видим, что соединение через Named Pipes появилось в списке соединений:

image015

рис.8

image017

рис.9

Затея с файрволом, на мой взгляд, выглядит негибко потому, что по фиксированному порту ТСР 1433 слушает только дефолтный инстанс SQL Servera (см. пост "Именованый экземпляр по умолчанию"). Именованые экземпляры слушают по, вообще говоря, случайному порту, который динамически выбирается в момент старта экземпляра. Посмотреть фактический порт можно в ErrorLoge:

image019

рис.10

Также там показывается, по какому ТСР-порту в данный момент можно подключиться к DACу (он тоже назначается динамически). К слову сказать, эти две вещи:

image021 image023

рис.11

абсолютно эквивалентны.

При коннекте к дефолтному инстансу клиент коннектится к TCP-порту 1433. При коннекте к именованому инстансу клиент коннектится к сервису SQLBrowser по UDP-порту 1434. Ему он сообщает имя инстанса, с каким хотел бы пообщаться, а SQL Server Browser сервис в ответ на это говорит номер ТСР-порта, по которому этот инстанс в данный момент слушает. После этого клиент коннектится к инстансу уже напрямую.

Можно перевести именованый инстанс на фиксированый порт. Для этого надо открыть SQL Configuration Manager, выбрать секцию IPAll, оставить строчку TCP Dynamic Рorts пустой и вбить желаемый порт в следующую - TCP Port.

image025

рис.12

Вся эта байда хранится в реджистри по адресу HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll:

image027

рис.13

После рестарта экземпляра его статический порт появится в TcpPort. Если используется динамический порт, то в TcpDynamicPorts можно посмотреть, какой реальный порт использовался в прошлый раз. Кстати, здесь - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp, TcpDynamicPorts можно посмотреть порт для DACa. Дальнейшее чтиво на эту тему находится здесь ­- КВ823938 "How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port", а мы двинемся дальше.

Из-за динамического назначения портов именованым инстансам вариант с закрытием SQL Serverа недоверенным IP-адресам через файрвол представляется не очень удобным, а переходить на статическую привязку портов не всегда возможно. Когда Microsoft еще располагался на Чапаевском, а семинары Russian SQL Server User Group проходили в Талгаре, Ян как раз делал доклад на эту тему, и мы решили, что наиболее секьюрный вариант – это загасить SQLBrowser, а номер порта после каждого рестарта под большим секретом сообщается доверенным лицам J.

Существует масса сценариев, которые не охватываются файрвольным решением. Файрвол умеет либо открыть дырку к SQL Server, либо ее закрыть. Жизнь устроена хитрее. Например, если с какой-то неизвестной тачки идет попытка логина под sa, это повод насторожиться. Члены серверной роли sysadmin известны наперечет, равно как и набор машин, с которых они обычно заходят, так что незачем допускать их логон откуда попало. В пределе сценарий может выглядеть так. Имеется таблица, где для каждого SQL Serverного логина перечислены адреса, откуда он может заходить на SQL Server. Всякий раз при установлении соединения проверяем, имеется ли такая связка логин – IP в этой таблице и если нет, соединения не допускаем. Внешние инструменты, подобные Windows Firewall, для такого решения не подойдут. Я бы попробовал использовать триггер на логон, тем более, что не так давно мы его разбирали - http://blogs.msdn.com/alexejs/archive/2009/07/14/p20090714_5F00_2.aspx. Что называется, в жилу пришлось. Для начала стоит понять, предоставляет ли функция EventData() необходимую информацию. Я создал табличку из единственного поля типа XML, куда сваливаю все содержимое результата функции при каждом логоне:

use tempdb

if exists(select 1 from sys.tables where name = 'LogonLog') drop table LogonLog

create table LogonLog (x xml)

if exists(select 1 from sys.server_triggers where name = 'tr1') drop trigger tr1 on all server

go

create trigger tr1 on all server for logon as

insert tempdb..LogonLog values (EventData())

commit

go

Скрипт 3

После этого соединился с SQL Server с соседней закладки в SSMS и с внешней машины из Excel (рис.2). Смотрим, что при этом собрала EventData():

select * from LogonLog

image029

рис.14

Это получилось при локальном логоне:

<EVENT_INSTANCE>

  <EventType>LOGON</EventType>

  <PostTime>2009-07-21T18:45:30.700</PostTime>

  <SPID>51</SPID>

  <ServerName>VISTAX86SQL2008</ServerName>

  <LoginName>Vistax86SQL2008\Administrator</LoginName>

  <LoginType>Windows (NT) Login</LoginType>

  <SID>AQUAAAAAAAUVAAAAXK9AlSunRrU26ZM09AEAAA==</SID>

  <ClientHost>&lt;local machine&gt;</ClientHost>

  <IsPooled>0</IsPooled>

</EVENT_INSTANCE>

Скрипт 4

А это – при логоне с внешней машины:

<EVENT_INSTANCE>

  <EventType>LOGON</EventType>

  <PostTime>2009-07-21T18:45:48.657</PostTime>

  <SPID>52</SPID>

  <ServerName>VISTAX86SQL2008</ServerName>

  <LoginName>sa</LoginName>

  <LoginType>SQL Login</LoginType>

  <SID>AQ==</SID>

  < ClientHost>192.168.0.1</ClientHost>

  <IsPooled>0</IsPooled>

</EVENT_INSTANCE>

Скрипт 5

Более полная информация после установления соединения содержится в DMV sys.dm_exec_sessions:

select * from sys.dm_exec_sessions where is_user_process = 1

session_id

login_time

host_name

program_name

host_process_id

client_version

client_interface_name

login_name

...

51

2009-07-21 18:45:31

VISTAX86SQL2008

Microsoft SQL Server Management Studio - Query

3388

6

.Net SqlClient Data Provider

Vistax86SQL2008\Administrator

52

2009-07-21 18:45:49

LESHIK-SQL

2007 Microsoft Office system

5760

4

OLEDB

sa

54

2009-07-18 09:14:56

VISTAX86SQL2008

Microsoft SQL Server Management Studio - Query

3388

6

.Net SqlClient Data Provider

Vistax86SQL2008\Administrator

55

2009-07-18 09:15:00

VISTAX86SQL2008

Microsoft SQL Server Management Studio

3388

6

.Net SqlClient Data Provider

Vistax86SQL2008\Administrator

57

2009-07-18 09:21:21

VISTAX86SQL2008

Microsoft SQL Server Management Studio

3388

6

.Net SqlClient Data Provider

Vistax86SQL2008\Administrator

Табл.1

На момент срабатывания триггера доступны логин и IP (в случае внешней машины). Отлично, это позволяет решить поставленную задачу. Я сейчас не буду замахиваться на таблицу соответствий логин – IP, а реализую в триггере простое правило: члены роли sysadmin должны логиниться сугубо локально (с той же машины, что и SQL Server). Для проверки принадлежности к серверной роли в Т-SQL используется функция IS_SRVROLEMEMBER(), которая приятно отличается от функции IS_MEMBER() для роли уровня базы тем, что ей можно в качестве аргументов подсунуть логина, тогда как последняя тупо работает от текущего пользователя базы. Правда, на этот случай имеется функция IS_ROLEMEMBER('роль', 'пользователь'), но в BOL она не значится.

image031

рис.15

Еще по этому поводу есть разные полезные DMV sys.database_principals

sys.database_role_members, sys.server_principals, sys.server_role_members, но это я снова умничаю.

Пишем триггер:

if exists(select 1 from sys.server_triggers where name = 'tr1') drop trigger tr1 on all server

go

create trigger tr1 on all server for logon as

begin

declare @x xml = EventData()

declare @login sysname = @x.value('(EVENT_INSTANCE/LoginName)[1]', 'sysname')

declare @address nvarchar(25) = @x.value('(EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(25)')

if is_srvrolemember('sysadmin', @login) = 1 and @address <> '<local machine>' rollback

else commit

end

go

Скрипт 6

Убеждаемся, что с локальной машины все заходят прекрасно, тестируем с удаленной. Администраторов она посылает:

 

image033

рис.16

Обычному пользователю заход разрешается:

 

image035 image037

рис.17

Следует отметить, что IP-адрес, будучи атрибутом сетевой библиотеки dbmssocn (TCP/IP), отображается в ClientHost, если соединение происходит посредством этой библиотеки. В случае использования какой-либо другой сетевой библиотеки, например, Named Pipes – см. Скрипт 2, функция EventData() будет возвращать информацию, специфичную для данного способа соединения:

<EVENT_INSTANCE>

  <EventType>LOGON</EventType>

  <PostTime>2009-07-21T19:27:10.807</PostTime>

  <SPID>53</SPID>

  <ServerName>VISTAX86SQL2008</ServerName>

  <LoginName>sa</LoginName>

  <LoginType>SQL Login</LoginType>

  <SID>AQ==</SID>

  < ClientHost > &lt; named pipe&gt;</ClientHost>

  <IsPooled>0</IsPooled>

</EVENT_INSTANCE>

Скрипт 7

select * from sys.dm_exec_connections

session_id

connect_time

net_transport

endpoint_id

encrypt_option

auth_scheme

node_affinity

net_packet_size

client_net_address

51

2009-07-21 18:45:30

Shared memory

2

FALSE

NTLM

0

4096

<local machine>

53

2009-07-21 19:27:10

Named pipe

3

FALSE

SQL

0

8000

<named pipe>

54

2009-07-18 09:14:56

Shared memory

2

FALSE

NTLM

0

4096

<local machine>

55

2009-07-18 09:15:00

Shared memory

2

FALSE

NTLM

0

4096

<local machine>

57

2009-07-18 09:15:04

Shared memory

2

FALSE

NTLM

0

4096

<local machine>

Табл.2