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

Article
07/22/2009

Надысь обсуждали с Андреем Бешковым задачу ограничения доступа пользователя на 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

рис.1

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

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

рис.2

рис.3

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

рис.4

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

рис.5

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

рис.6

Если указать в строке соединения другую сетевую библиотеку (см. https://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

рис.7

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

рис.8

рис.9

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

рис.10

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

рис.11

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

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

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

рис.12

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

рис.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, для такого решения не подойдут. Я бы попробовал использовать триггер на логон, тем более, что не так давно мы его разбирали - https://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

create trigger tr1 on all server for logon as

insert tempdb..LogonLog values (EventData())

commit

Скрипт 3

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

select * from LogonLog

рис.14

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

<EVENT_INSTANCE>

<EventType>LOGON</EventType>

<ServerName>VISTAX86SQL2008</ServerName>

<LoginName>Vistax86SQL2008\Administrator</LoginName>

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

<SID>AQUAAAAAAAUVAAAAXK9AlSunRrU26ZM09AEAAA==</SID>

</EVENT_INSTANCE>

Скрипт 4

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

<EVENT_INSTANCE>

<EventType>LOGON</EventType>

<ServerName>VISTAX86SQL2008</ServerName>

<LoginType>SQL Login</LoginType>

< ClientHost>192.168.0.1</ClientHost>

</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 она не значится.

рис.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

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

Скрипт 6

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

рис.16

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

рис.17

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

<EVENT_INSTANCE>

<EventType>LOGON</EventType>

<ServerName>VISTAX86SQL2008</ServerName>

<LoginType>SQL Login</LoginType>

< ClientHost > < named pipe></ClientHost>

</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

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

Additional resources