В большинстве сценариев Windows PowerShell я использую объекты управления SQL Server (SMO) для доступа к свойствам SQL Server с целью выяснения текущего состояния или получения другой информации, имеющей отношение к роли DBA. Обращаться к данным мне приходится часто. Модуль SQLPS имеет команду Invoke-Sqlcmd, которая заметно облегчает получение доступа. Однако во многих случаях для доступа к данным я применяю ADO.NET, например если создаваемый сценарий будет работать как регулярное плановое задание, и особенно если сценарий предназначается для пользователей, которые не знают, что прежде чем можно будет задействовать Invoke-SQLCMD, необходимо загрузить модуль SQLPS. Чтобы в сценарии PowerShell организовать подключение к базе данных с помощью ADO.NET, создается объект SqlConnection. Самым важным свойством этого объекта является ConnectionString. Строка подключения состоит из набора пар «ключ-значение», разделенных точкой с запятой. Строка содержит всю необходимую информацию для успешного подключения к базе данных SQL Server в ходе выполнения сценария.
Создание строки подключения
Для строки подключения сначала создается пара «ключ-значение», несущая необходимую информацию о сервере. Ключевым словом может быть Server, Data Source, Address, Addr или Network Address, а значением – имя сервера, имя экземпляра или адрес. Если соединение устанавливается через порт с нестандартным номером, то нужно указать этот номер порта после запятой. Вот несколько примеров:
Data Source=WS12SQL; Server=SQLTBWS\INST01; Server=10.1.1.118,10291; Server=tcp:dijewiq84u.database.windows.net;
В последнем примере реализуется подключение к базе данных Windows Azure SQL. Запись tcp: перед значением указывает на то, что для соединения будет использоваться TCP/IP. В случае подключения к серверу, на котором выполняется сценарий, в качестве значения можно вводить (local). Аналогично, в случае подключения к локальному серверу по TCP/IP, в качестве значения можно использовать localhost.
Затем создается пара «ключ-значение», задающая базу данных, к которой предполагается подключаться. Ключевым словом может быть Database или Initial Catalog, а значением – имя базы данных.
Следующая и наиболее важная пара «ключ-значение» определяет контекст безопасности, используемый при подключении к серверу. Ключевым словом может быть Integrated Security или Trusted_Connection, а значениями – SSPI, True, False, Yes и No. Я предпочитаю SSPI, это означает, что при установлении соединения будет применяться проверка подлинности Windows с использованием Kerberos (в рамках текущего контекста безопасности). Похоже, что Yes или True дают тот же результат, что и SSPI, хотя в документации об этом явно не говорится. Если указать False или No, то для получения доступа к серверу необходимо будет добавить учетные данные.
Если проверка подлинности Windows не используется, необходимо указать учетные данные, используя ключевые слова User ID и Password (или Pwd) и соответствующие значения. Этот вариант сомнителен, так как жесткое программирование имен пользователей и паролей в сценариях – не слишком удачная идея с точки зрения безопасности. Существуют методы снижения риска, например передача значений SecureString посредством переменной, но они не столь безопасны, как проверка подлинности Windows.
В строку подключения еще можно включить пару «ключ-значение» Encrypt=True;. Например, строка подключения к базе данных Azure SQL может выглядеть так:
Server=tcp:dijewiq84u.database.windows.net;Database=Northwind; User ID=DBAdmin@$azsvnm;Password=P@ssw0rd1; Trusted_Connection=False;Encrypt=True;
Включение этой пары «ключ-значение» обеспечивает применение шифрования к строке подключения при отправке на сервер, если на сервере есть установленный сертификат. Такой подход предпочтителен, ибо он позволяет исключить возможность перехвата сведений о доступе процессами, прослушивающими сеть.
Еще одна полезная пара «ключ-значение» состоит из ключевого слова Application Name и значения, каковым является имя приложения или сценария, устанавливающего соединение. Включение этой пары значительно облегчает диагностику ошибок подключения, которые могут возникнуть, когда несколько приложений используют один сервер или когда на сервере выполняется несколько сценариев. Кроме того, каждый, кто наблюдает за сеансами SQL Server, будет знать, что делает данный сценарий.
Использование строки подключения
Созданную строку подключения можно поместить в переменную строки и передать в качестве параметра при создании объекта SqlConnection:
$cstr = «Server=WS12SQL;Database=Northwind;Integrated Security=SSPI;Application Name=SetupNorthwind»; $cn = new-object system.data.SqlClient.SqlConnection($cstr) $nwddl = Get-Content. \NorthwindDDL.sql $cmd = New-Object System.Data.SqlClient.SqlCommand($nwddl,$cn) $cn.Open() $cmd.ExecuteNonQuery() $cn.Close()
В этом примере подключение к существующей базе данных Northwind на сервере WS12SQL реализуется с использованием проверки подлинности Windows. После установления соединения выполняется загрузка сценария NorthwindDDL.sql (предназначенного для добавления таблицы в базу данных) в текущий каталог и его запуск применительно к базе данных Northwind. Обратите внимание на «позднее открытие» и «раннее закрытие» подключения. Этот метод гарантирует работу сценария даже после перехода на другой сервер в отказоустойчивом кластере или группе доступности AlwaysOn.
Определите доступ
Доступ к SQL Server определяют строки подключения. Для получения более подробной информации о них посетите веб-страницу MSDN, посвященную свойству Connection String объекта SqlConnection (msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.90%29.aspx). Ознакомиться с примерами строк подключения можно на сайте www.connectionstrings.com.