Ограничение на клиентский 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


Skip to main content