В нашем децентрализованном мире нужные данные редко находятся в одном месте и прямо у нас «под носом». Нередко, и, совсем чуть-чуть преувеличивая, даже зачастую мы вынуждены куда-то «сходить» за исходными данными ну или за частью таких данных. И лишь заполучив таковые на свой локальный сервер погружаться в сладостный мир их обработки. По счастью, если платформа нашего решения SQL Server (а я надеюсь что у читателей данных строк так оно и есть) то указанная специфика мироустройства не является для нас сколь-нибудь заметной проблемой. Широчайший круг внешних источников данных (data sources) поддерживаемых сервером, относительная простота процесса связи с ними, однотипная работа с точки зрения исходного кода извлекающего данные вне зависимости от конкретного источника — все это позволяет SQL-администраторам и разработчикам уверенно решать сегодняшние задачи да и в будущее смотреть с оптимизмом.
Как это известно подавляющему числу читателей данного блога, центральный механизм обеспечивающий весь этот «оптимизм» носит название связанных серверов, они же linked servers. Сама по себе это довольно большая тема, хотя, на фоне прочих SQL-«топиков», одна из самых прямолинейных и бесхитростных. Написать свой первый распределенный запрос (distributed query), а выполнение таковых и есть финальная цель создания linked servers, можно буквально через 20 минут после того как вы впервые услышали термин «связанный сервер». Однако наиболее «заковыристым» разделом этой «большой и несложной» темы является вопрос безопасности при связывании серверов. Собственно, security традиционно является наиболее проблемным для постижения и правильной реализации компонентом практически любой IT-системы. Наши связанные сервера исключением не стали.
Итак, тема сегодняшнего материала — понимание и настройка безопасных подключений при работе с внешними источниками данных. Linked сервера как таковые (в целом) остаются темой для будущих заметок или, что даже предпочтительней, для самостоятельного изучения читателем. Повторю, что в целом вопрос относительно прост, за исключением того его сегмента на котором мы сфокусируем свое внимание.
Как уже было отмечено круг потенциальных поставщиков данных для нашего SQL сервера приятно удивляет своей широтой. Тут вам и непременный Excel, и Access, и plain-text файл, и Oracle с IBM DB2, ну а уж с привлечением корректного драйвера odbc список поставщиков может стать поистине безразмерным. Однако мы сосредоточимся на варианте когда в качестве обсуждаемого поставщика выступает снова SQL Server. Во-первых, это довольно частый сценарий реальной жизни, и, должно быть, именно он интересен большинству читателей. Во-вторых, при такой архитектуре тестовое окружение для проверки своих идей и знаний по обсуждаемому вопросу элементарно собирается на компьютере типа desktop из двух отдельных экземпляров (instances) SQL Server. Кстати автор, при подготовке материалов данной статьи, так и поступил. Так что все эксперименты и опыты излагаемые им далее вы сможете весьма запросто воспроизвести у себя дома/на работе. Ну и в третьих, что не маловажно, при связке SQL Server↔SQL Server мы сможем опробовать все варианты безопасного подключения к внешнему источнику данных. А таковых (не источников, а вариантов подключения) целых 5(!) штук, на минуточку. Если же в интересующем вас сценарии поставщиком будет не SQL сервер, а иная(иной) СУБД/приложение/файл, то вы будете работать либо с теми же 5-ю вариантами, либо и их поднабором. Иными словами разобравшись с безопасностью связывания SQL Server↔SQL Server, прочие варианты поставщиков будут для вас уже сущими «семечками», в обсуждаемом аспекте.
Тестовое окружение.
Для повторения излагаемых далее шагов и выполнения приводимых там же скриптов вам нужно не более чем 2 экземпляра SQL сервера установленных на ваш локальный компьютер. Лучше если это будут экземпляры сервера версии SQL Server 2008R2, хотя для целей и задач данной статьи наличие (а равно отсутствие) суффикса R2 в имени версии не оказывает ровным счетом никакого влияния. Большинство шагов сработают и на сервере 2005-м, хотя тут уже будут «нюансы», специальное выделение и обзор которых в намерения автора совершенно не входят.
Говоря языком строгим, сухим и техническим все описываемое далее случилось на машине такой конфигурации:
- OS — Windows Server 2008R2
- Имя компьютера — WINR2
- Имя первого экземпляра SQL Server — WINR2 (т.е. «дефолтный» инстанс)
- Имя второго экземпляра SQL Server — WINR2\MSSQL2 (т.е. именованный инстанс)
Изначально мы, как и положено, работаем под учетной записью локального администратора (если ваш desktop компьютер в домене, то вариант администратора доменного не повредит, хотя и не является обязательным). На обоих экземплярах (и том что по умолчанию, и на именованном) мы, конечно же, так же являемся администраторами, то есть наше имя входа включено в фиксированную серверную роль sysadmin.
Для завершения процесса подготовки к исследованиям в области безопасности связанных серверов нам необходимо добавить пару локальных пользователей нашего компьютера (на уровне операционной системы), что можно сделать через оснастку консоли Administrative Tools→Computer Management→Local Users and Groups→Users и пункта New User контекстного меню последнего узла. Например автор добавил такую пару:
- frank
- mary
Никаких особых прав давать этим пользователям не нужно, пусть остаются в своей группе Users. Пароль подберите по своему вкусу, автор указал обоим его любимый — 123. Желающим повторить этот «смертельный номер» придется отключить политику проверки сложности пароля, Administrative Tools→Local Security Policy→Account Policies→Password Policy→Passwords must meet complexity requirements=Disabled. Возможно проще указать пароль чуть более сложный, тем более, повторю, он ровным счетом безразличен для происходящего далее.
И теперь несколько подготовительных действий на самих экземплярах. На экземпляре по умолчанию (а в нашем тестовом окружении он сыграет роль локального сервера) выполним такой скрипт:
1 2 3 4 5 | USE master GO CREATE LOGIN [WINR2\frank] FROM WINDOWS CREATE LOGIN [WINR2\mary] FROM WINDOWS GO |
Полагаю комментарии излишни, все и так поняли что и к чему. На экземпляре же именованном (а ему уготована роль сервера удаленного, т.е. как раз того самого источника внешних данных) выполним такой набор команд:
1 2 3 4 5 6 7 8 9 | USE master GO CREATE DATABASE DB1 GO USE DB1 GO create proc usp1 AS SELECT SUSER_NAME() AS LoginName, USER_NAME() AS DB_UserName; GO |
Элементарная база и элементарная процедура. Цель последней — показать имя пользователя на уровне сервера (т.е. его login) и на уровне базы (т.е. его user). Причем и то и другое показывается «глазами», скажем так, движка сервера. Т.е. если наш frank запустит эту процедуру (а собственно только этим он и будет заниматься), не факт что движок «распознает» его именно как WINR2\frank. It, как говорят наши англоязычные коллеги, depends. Что бы увидеть эти самые факты и «не-факты» нам нужно запустить второй экземпляр студии (той что SQL Server Management Studio) именно под этим пользователем что бы у нас был единый источник исполняющий распределенные запросы (хотя в нашем случае таковых будет ровно 1), и что бы мы сами точно знали от кого он исходит на самом деле. Добиться такого «подменного» запуска можно несколькими путями. Самое простое — щелкнуть по иконке студии с предварительно зажатой клавишей Shift правой кнопкой мыши и выбрать в контекстном меню пункт Run as different user (см. иллюстрацию справа, кликабельно). Если вы видите только пункт для запуска от имени администратора, но не произвольного пользователя — вы плохо зажали Shift, давите сильнее. 
В возникшем после выбора указанного пункта окне вводим frank и пароль пользователя — запускается вторая студия. Поскольку мы будем работать с обеими студиями попеременно то договоримся так: тот ее вариант где мы работаем «под собою» (т.е. администраторами системы) я буду называть admin-студия, а второй, где мы уже «не мы», а frank я буду называть frank-студия. Так вот сейчас именно во второй, в frank-студии, открываем новую вкладку редактора, подключаемся к экземпляру SQL Server по умолчанию (проверьте правильность подключения!), и впечатываем наш единственный распределенный запрос:
1 | EXEC [WINR2\MSSQL2].DB1.dbo.usp1 |
Таким образом все попытки нашего условного Фрэнка будут сводиться к тому, что бы находясь на сервере локальном выполнить хранимую процедуру размещенную на сервере удаленном. Ну и проанализировать результаты ею возвращаемые, конечно, правда при условии что описанное выполнение вообще будет успешным. А это, как мы увидим, случится далеко не всегда. Например прямо сейчас запускать последний скрипт (я буду называть его тест-скриптом) нет ровным счетом никакого резона, у нас еще нет связанного сервера по имени WINR2\MSSQL2. Давайте его создадим.
Для этого возвращаемся в admin-студию, подключаем (если это еще не сделано) ее окно Object Explorer к экземпляру по умолчанию, перемещаемся к узлу Server Objects→Linked Servers, открываем контекстное меню последнего и щелкаем пункт New Linked Server. Открывается одноименное окно, состоящее из трех закладок (или страниц) перечисленных в левой панели окна: General, Security, Server Options. Если вы предположите, что целью нашей станет пристальное изучение всего одной из этих страниц и причем второй, то будете совершенно правы, именно ее мы и собираемся разобрать «по косточкам». Страницы же 1-ю и 3-ю мы почти рассматривать не будем внеся в них лишь самые необходимые данные, а именно:
- на странице General:
- в поле Linked server вводим WINR2\MSSQL2, поскольку именно такое имя имеет наш «удаленный» сервер;
- кнопку Server type переводим в положение SQL Server, поскольку тот же сервер является именно MS SQL сервером, а никаким-то иным.
- на странице Server Options:
- для опции RPC Out устанавливаем значение True, поскольку наша цель именно вызов и именно хранимой процедуры с удаленного сервера.
И, наконец, переключаемся на центральную для задач данного материала страницу того же окна — Security. На ней собственно, с помощью радио-кнопок перечислены те самые режимы безопасных подключений к удаленным серверам о которых автор упоминал в самом начале статьи и изучение которых и является нашей главной задачей.
Изучать эти режимы мы будем не в том порядке как они перечислены на данной странице, а в порядке их востребованности. То есть в том порядке в котором их и рекомендуют рассматривать когда мы размышляем над вопросом как мы будем (с точки зрения безопасности) подключаться к data source, особенно если в качестве такового выступает SQL Server. Так вот при таком сценарии «best practice» проста и понятна: выбирайте опцию Be made using the login's current security context. Ее и выбираем.
Щелкаем OK — наш linked server настроен и готов к испытаниям первой, самой «разумной», опции.