SQLPS.exe
This is a utility that you should be able to open by typing it in the run prompt (Start > Run). A second option, right-click a node under Object Explorer, within SQL Server Management Studio (SSMS), and select “Start PowerShell”. The SQLPS utility’s main access point is using the provider “SQLSERVER:\” to browse SQL Server like a file directory. With that, based on the node you open SQLPS from will place you within that path of the provider. Under each “folder” you are in for the provider offers properties to read or set, and some methods to use for administration.
One thing you must remember when using this utility is the version of PS you operate under is 2.0, an obvious weakness. Example: I use on Windows 10 Operating System (OS) with PS version 5.0 and SQL Server 2012:
It is very important distinction to remember because a cmdlet (pronounced command-let) under version 5.0 may not show up at all in SQLPS.exe, or be missing new features that were added.
This method to accessing a SQL Server instance is for those one-liner situations, but will admit that SQLPS is rather slow to work with at times, so don’t get in too big of a rush. As an example, a common one-liner I use SQLPS utility for is when I am refreshing development environment and just want to set all the databases to SIMPLE recovery mode:
| 1 | Get-ChildItem SQLSERVER\SQL\LOCALHOST\SQL12\Databases|foreach{$_.RecoveryModel=“SIMPLE”;$_.Alter()} |
Using T-SQL
This section talks about using T-SQL to create Column Master Key and Column Encryption Key along with creating encrypted columns in a table. The following are the three tables that are very important to get the required key information to create Master and Column encryption keys.
Step 1: Create Column Master Key
To get the provider name and the key path details, query the system view sys.column_master_keys
|
1 |
Name, key_store_provider_name KeyStore, key_path KeyPath
FROM sys.column_master_keys |
Use the create column master key DDL to define the SQLShackAECMK master key
|
1 |
CREATE COLUMN MASTER KEY SQLShackAECMK WITH( KEY_STORE_PROVIDER_NAME=’MSSQL_CERTIFICATE_STORE’, KEY_PATH=’Current User/my/96B46286C49BEC6EC6D7CD31DBEE3B5A3B57E3E7′ );
|
Step 2: Create Column Encryption Key
To get encrypted_value and algorithm details, query the following system views sys.column_encryption_key_values and sys.column_encryption_keys
|
1 |
NAME, ENCRYPTED_VALUE, ENCRYPTION_ALGORITHM_NAME ALGORITHM FROM sys.column_encryption_key_values CEKV inner join sys.column_encryption_keys CEK ON CEKV.column_encryption_key_id=CEK.column_encryption_key_id WHERE NAME=’SQLShack_CEK_1′
|
Use the create column encryption key DDL to create the SQLShackAECEK CEK key
|
1 |
CREATE COLUMN ENCRYPTION KEY SQLShackAECEK WITH VALUES ( COLUMN_MASTER_KEY=SQLShackAECMK, ALGORITHM=’RSA_OAEP’, ENCRYPTED_VALUE= 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0039003600620034003 );
|
Step 3: Create Encrypted Columns table
Use the create table DDL with a few additional configurations in the column definition
|
1 |
CREATE TABLE tbl_CustomerSQLShackDemoAE( custName nvarchar(30) COLLATE Latin1_General_BIN2 ENCRYPTED WITH( ENCRYPTION_TYPE=RANDOMIZED, ALGORITHM=’AEAD_AES_256_CBC_HMAC_SHA_256′, COLUMN_ENCRYPTION_KEY=SQLShackAECEK), custSSN varchar(11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH( ENCRYPTION_TYPE=DETERMINISTIC, ALGORITHM=’AEAD_AES_256_CBC_HMAC_SHA_256′, COLUMN_ENCRYPTION_KEY=SQLShackAECEK), custBirthDatedate ENCRYPTED WITH(ENCRYPTION_TYPE=RANDOMIZED, ALGORITHM=’AEAD_AES_256_CBC_HMAC_SHA_256′, COLUMN_ENCRYPTION_KEY=SQLShackAECEK)NOTNULL, CustAge intNULL, );
|
That’s all.
Запуск PowerShell из агента SQL Server
Есть несколько типов шагов заданий агента SQL Server. Каждый тип связан с некоторой подсистемой, в которой реализуется определенная среда, например агент репликации или среда командной строки. Можно создавать скрипты Windows PowerShell, а затем использовать агент SQL Server, чтобы включить скрипты в задания, которые выполняются в запланированное время или в ответ на события агента SQL Server. Скрипты Windows PowerShell можно запускать либо с помощью шагов задания командной строки, либо с помощью шагов задания PowerShell.
-
Используйте шаги задания PowerShell для запуска подсистемой агента SQL Server служебной программы sqlps, запускающей PowerShell и импортирующей модуль sqlps. Если вы используете SQL Server 2019 или более поздней версии, мы рекомендуем использовать модуль в шаге задания Агента SQL.
-
Используйте шаг задания командной строки для запуска PowerShell.exe и укажите скрипт, импортирующий модуль sqlps .
Предупреждение, касающееся потребления памяти
Каждый шаг задания агента SQL Server, запускающий PowerShell с модулем sqlps, запускает процесс, которому требуется приблизительно 20 МБ памяти. Одновременный запуск большого числа шагов задания Windows PowerShell может иметь негативное влияние на производительность.
Задачи поставщика SQL Server
| Описание задачи | Статья |
|---|---|
| Описано, как использовать командлеты Windows PowerShell для перехода по узлам пути и получения в каждом узле списка объектов этого узла. | Перемещение путей SQL Server PowerShell |
| Описано, как использовать методы и свойства объектов SMO для получения отчета и выполнения работы над объектом, представленным узлом пути. Кроме того, описано, как получить список методов и свойств объектов SMO для этого узла. | Работа с путями SQL Server PowerShell |
| Описывает, как преобразовать универсальное имя ресурса объекта SMO в путь поставщика SQL Server. | Преобразование URNs в пути поставщика SQL Server |
| Описано, как открывать соединения проверки подлинности SQL Server с использованием поставщика SQL Server . По умолчанию поставщик использует соединения проверки подлинности Windows, установленные с помощью учетных данных той учетной записи Windows, которая используется в сеансе Windows PowerShell. | Управление проверкой подлинности в PowerShell ядра СУБД |
Подключение к SQL Server и получение сведений о сервере
Теперь мы используем PowerShell в Windows для подключения к вашему экземпляру SQL Server на Linux и просмотра некоторых свойств сервера.
Скопируйте следующие команды и вставьте их в командную строку PowerShell. При запуске этих команд в PowerShell будут выполнены следующие действия:
- отображение диалогового окна с запросом на ввод имени узла и IP-адреса экземпляра;
- Откройте диалоговое окно Запрос учетных данных Windows PowerShell, в котором необходимо ввести соответствующие сведения. для подключения к экземпляру SQL Server на Linux вы можете использовать свои имя пользователя SQL и пароль SQL;
- использование командлета Get-SqlInstance для подключения к серверу и просмотра некоторых свойств.
При необходимости можно заменить переменную IP-адресом или именем узла вашего экземпляра SQL Server.
В PowerShell должны отображаться данные примерно следующего вида:
Примечание
Если эти значения не отображаются, скорее всего, подключение к целевому экземпляру SQL Server установить не удалось. Убедитесь в том, что эти же данные можно использовать для подключения из SQL Server Management Studio. Затем ознакомьтесь с .
Using SSMS
- Use the Object Explorer to locate the database – SQLShackAlwaysEncrypted
- Go to the Security tab
-
Select the Always Encrypted Keys option
- Right-click and select New Column Master Key….
- Enter the name of the Master Key SQLShackDemoCertificate
- Specify Key store, (Windows Certificates Store in this case) for the current user or local machine certificate store, or the Azure Key Vault and then select a certificate from the list. You can even one by clicking the Generate certificate option.
- Click OK
The above steps create a self-signed certificate and load it into the store.
Now, we need to distribute the certificate to all the client machines by using the Export and Import Certificates method.
Now, select New Column Encryption Keys.
- Enter the name of the column encryption key SQLShackDemoCEK
- Use the drop-down and select the Column Master Key SQLShackDemoCertificate
- Click OK
-
Verify the Always Encrypted Keys
We have successfully completed the configuration. Now, it’s time to apply the encryption settings to the column(s) by browsing the table and selecting the needed column(s) for encryption.
- Browse the Columns tab
- Right-click the column and select Encrypt Column
- Select the Encryption Type: Select either of the available options, since Always Encrypted supports two types of encryption: Randomized and Deterministic
- Use the drop-down, and select the Column Encryption Key, which is already tied with the Column Master Key
-
Click Next
Click Next
Click default Proceed to finish radio button
Verify the summary of settings and click Finish
Validate the results
Query the table and view the encrypted column. In the below screenshot, the Servername column is encrypted.
In order to decrypt the column, the following settings should be enabled in the SSMS client
First, add Column Encryption Setting = Enabled in the Additional Connection Parameters in the SSMS Connect to Server window.
Now, query the table for the encrypted values
And voila!
Использование поставщика SQL Server для PowerShell
В качестве альтернативы для подключения к экземпляру SQL Server можно использовать поставщик SQL Server PowerShell. Этот поставщик позволяет работать с экземпляром SQL Server из командной строки так же, как с древовидной структурой в обозревателе объектов. По умолчанию этот поставщик представлен как диск PSDrive с именем , который можно использовать для подключения к экземплярам SQL Server и навигации по ним, к которым есть доступ у вашей учетной записи домена. Дополнительные сведения о настройке проверки подлинности Active Directory для SQL Server в Linux см. в разделе .
Также вы можете использовать проверку подлинности SQL в поставщике SQL Server PowerShell. Для этого с помощью командлета создайте новый диск PSDrive и укажите учетные данные для подключения.
В приведенном ниже примере показано, как создать новый диск PSDrive с использованием проверки подлинности SQL.
Чтобы убедиться в том, что диск был создан, выполните командлет .
Создав новый диск PSDrive, вы можете начать работу с ним.
Выходные данные могут выглядеть следующим образом: Вы можете заметить, что выходные данные аналогичны тому, что SQL Server Management Studio (SSMS) отобразит в узле «Базы данных». В нем представлены пользовательские, а не системные базы данных.
Чтобы просмотреть все базы данных в вашем экземпляре, можно использовать командлет Get-SqlDatabase.
Selecting Deterministic or Randomized Encryption
Always Encrypted supports two types of encryption: randomized and deterministic
-
Deterministic encryption
- The same encrypted Key for a given value is generated, every time.
- Binary2 sort order collation must be used to setup deterministic encryption on a column.
- Heuristically studying the patterns of the contents of the column could reveal the contents, thereby making it more susceptible to hacking
-
Randomized encryption
- This method is more robust and secure, and the patterns are less likely to be predictable due to its random generation of the key for a given value.
- The limitation with this type of encryption is that searching, join, group and, indexing is not possible
In an age of centralized or remote management of data, it is important that the enterprises add an abstraction layer to their data. This way, those who manage the data on a day-to-day basis, such as database administrators are not able to view or use the data. At the same time, those in the enterprise who own the data, have complete access to the data, even though they may not necessarily manage it.
Apart from being the layer of abstraction, Always Encrypted also ensures encryption of data during transit, thereby protecting it from sniffers—typically those involved in attacks such as Man in the Middle.
Установка или обновление модуля SqlServer
Чтобы установить модуль SqlServer из коллекции PowerShell, запустите сеанс PowerShell и выполните команду .
При работе с Windows PowerShell можно выполнить , чтобы установить модуль только для текущего пользователя без необходимости повышения прав.
Установка модуля SqlServer для всех пользователей
Чтобы установить модуль SqlServer для всех пользователей, выполните приведенную ниже команду в сеансе PowerShell с повышенными правами, запущенном от имени администратора:
Просмотр установленных версий модуля SqlServer
Выполните следующую команду, чтобы просмотреть установленные версии модуля SqlServer.
Просмотр версии модуля SqlServer, загруженного в текущем сеансе:
Перезапись предыдущей версии модуля SqlServer
Вы можете перезаписать предыдущую версию с помощью команды .
Примечание
PowerShell всегда использует последний установленный модуль.
Обновление установленной версии модуля SqlServer
Если доступны обновленные версии модуля SqlServer, можно установить более новую версию с помощью следующей команды:
Вы можете использовать команду , чтобы установить последнюю версию модуля SQLServer PowerShell, но это не приведет к удалению предыдущих версий. Эта команда устанавливает более новую версию параллельно, чтобы вы могли экспериментировать с последней версией, но по-прежнему иметь предыдущие модули.
Однако если вы не хотите сохранять старые версии модуля, можно удалить предыдущие версии с помощью команды .
Если установлено более одной версии, можно вывести их список с помощью следующей команды:
Чтобы удалить предыдущие версии, выполните следующую команду:
Предварительные версии модуля SqlServer
Предварительные (или preview) версии модуля SqlServer могут быть доступны в коллекции PowerShell.
Важно!
Их можно найти и установить с помощью обновленных командлетов Find-Module и Install-Module, которые входят в модуль PowerShellGet, передав параметр -AllowPrerelease. Чтобы использовать эти командлеты, установите модуль PowerShellGet, а затем откройте новый сеанс.
Установка конкретной предварительной версии модуля SqlServer
Чтобы установить конкретную предварительную версию модуля, укажите при установке конкретный номер версии.
Используйте для этого следующую команду:
Создание шага задания командной строки
Создание шага задания CmdExec
Разверните раздел Агент SQL Server, создайте задание или щелкните правой кнопкой мыши существующее задание и выберите Свойства. Дополнительные сведения о создании заданий см. в разделе Создание заданий.
В диалоговом окне Свойства задания выберите страницу Шаги и щелкните Создать.
В диалоговом окне Новый шаг задания введите имя шагазадания.
В списке Тип выберите Операционная система (CmdExec) .
В списке Выполнять как выберите учетную запись-посредник с учетными данными, используемыми в задании. По умолчанию шаги задания CmdExec выполняются под учетной записью службы агента SQL Server.
В поле Код завершения процесса успешной команды введите значение от 0 до 999999.
В поле Команда введите команды, которые начинаются с PowerShell.exe с параметрами, указывающими скрипт PowerShell для запуска. Эти примеры аналогичны синтаксису для выполнения команд PowerShell из командной строки Windows. Все возможные варианты синтаксиса см. можно просмотреть, введя .
Пример 1. Запуск простого командлета.
Пример 2. Выполнение запроса с помощью SQLCmd.exe к текущему серверу (в примере используется замена токена Агента SQL).
Пример 3. Запуск скрипта PowerShell (с использованием , имени исполняемого файла в PowerShell 7.0, который должен быть установлен на сервере)
Обратите внимание, что путь к скрипту является локальным для сервера, на котором работает Агент SQL.
Откройте страницу Дополнительно, чтобы задать следующие параметры шага задания: какие действия будут предприняты в случае успешного или неуспешного выполнения шага задания, сколько раз агенту SQL Server пытаться его выполнить и в какой файл агент SQL Server может записывать результат выполнения шага задания. Только члены предопределенной роли сервера sysadmin могут записывать выходные данные шага задания в файл операционной системы.
Retrieving Your Data
After you do all that you are probably wondering how you output that data so you can see it? The data itself resides in a collection of tables within the Table property of your DataSet object. Now depending on the version of .NET you are working with you might actually need to specify the index of the collection (e.g. Tables), but this is generally only required in older versions below .NET 4.0.
|
1 |
$data.Tables <# or #> $data.Tables |
If the procedure or T-SQL script you are executing contains more than one dataset this collection will only contain the first result set returned by SQL Server.
A visual of the whole command:
The last thing you will want to remember for just proper coding practice is to close the connection once you are done. Just call the Close() method to close out your connection to the SQL Server instance.
Create SQL Server database and assign an owner
Surprisingly, Microsoft does not provide a cmdlet out of the box to create a database, so there are two main routes you can follow to get a database up and running without a prior backup. Both are fairly simple. Creating a new blank database is accomplished with either of the following commands:
Run straight SQL against your instance to create the database:
Alternatively, using SQL Server Management Objects (SMO) objects to do the heavy lifting:
If you do have a database backup you would like to restore rather than creating a blank database, the Restore-SqlDatabase cmdlet is your friend:
Now that your database is in place, let’s change the database owner to :
Иерархия SQL Server в PowerShell
Продукты, в которых модели данных или модели объектов можно представить в иерархическом виде, используют для представления таких иерархий поставщики Windows PowerShell. Иерархия отображается при помощи диска и структуры пути, похожей на ту, которая используется в файловой системе Windows.
Каждый поставщик Windows PowerShell реализует один или несколько дисков. Каждый диск является корневым узлом в иерархии связанных объектов. В поставщике SQL Server PowerShell реализован диск «SQLSERVER:». Поставщик также определяет набор основных папок для диска SQLSERVER:. Каждая папка и вложенные в нее папки представляют набор объектов, к которым можно получить доступ с помощью модели управляющих объектов SQL Server . Если выделена вложенная папка в пути, начинающемся с одной из этих папок, можно использовать методы из связанной объектной модели для выполнения действий с объектом, который представлен вложенной папкой. Папки Windows PowerShell, реализуемые поставщиком SQL Server, перечислены в следующей таблице.
| Папка | Пространство имен объектной модели SQL Server | Объекты |
|---|---|---|
| Microsoft.SqlServer.Management.SmoMicrosoft.SqlServer.Management.Smo.AgentMicrosoft.SqlServer.Management.Smo.BrokerMicrosoft.SqlServer.Management.Smo.Mail | Объекты базы данных, такие как таблицы, представления и хранимые процедуры. | |
| Microsoft.SqlServer.Management.DmfMicrosoft.SqlServer.Management.Facets | Объекты управления на основе политик, такие как политики и аспекты. | |
| Microsoft.SqlServer.Management.RegisteredServersMicrosoft.SqlServer.Management.Smo.RegSvrEnum | Зарегистрированные объекты серверов, такие как группы серверов и зарегистрированные серверы. | |
| Microsoft.SqlServer.Management.Utility | Вспомогательные объекты, такие как управляемые экземпляры компонента Компонент Database Engine. | |
| Microsoft.SqlServer.Management.Dac | Объекты приложения уровня данных, такие как пакеты DAC, и операции, такие как развертывание DAC. | |
| Microsoft.SqlServer.Management.Collector | Объекты сборщика данных, такие как наборы элементов сбора и хранилища конфигураций. | |
| Microsoft.SqlServer.Management.IntegrationServices | Службы Integration Services , как проекты, пакеты и среды. | |
| Microsoft.SqlServer.Management.XEvent | Расширенные события SQL Server | |
| Microsoft.SqlServer.Management.XEventDbScoped | Расширенные события SQL Server | |
| Microsoft.AnalysisServices | Службы Analysis Services , такие как кубы, агрегаты и измерения. |
Например, папку SQLSERVER:\SQL можно использовать, чтобы начинать пути, которые могут представлять любой объект, поддерживаемый объектной моделью SMO. Начальная область пути SQLSERVER:\SQL — SQLSERVER:\SQLComputerName\InstanceName. Узлы после имени экземпляра поочередно указывают коллекции объектов (такие как Базы данных или Представления) и имена объектов (наподобие AdventureWorks2012). Схемы не представляются в качестве классов объектов. Если указывается узел для объекта верхнего уровня в схеме, такого как таблица или представление, необходимо указать имя объекта в формате ИмяСхемы.ИмяОбъекта.
В следующем примере показан путь к таблице «Vendor» в схеме «Purchasing» базы данных AdventureWorks2012 на экземпляре по умолчанию компонента Компонент Database Engine на локальном компьютере.
Дополнительные сведения об иерархии модели объектов SMO см. в разделе SMO Object Model Diagram.
Узлы коллекций в пути связаны с классом коллекций в связанной объектной модели. Узлы имен объектов связаны с классом объектов в связанной модели объектов, как показано в следующей таблице.
| путь | Класс SMO |
|---|---|
| DatabaseCollection | |
| Database |
Соединение с базой данных
Некоторые командлеты постоянного шифрования работают с данными или метаданными в базе данных и требуют сначала выполнить соединение с базой данных. При настройке постоянного шифрования с помощью модуля SqlServer подключиться к базе данных можно двумя рекомендуемыми способами:
- Подключение с помощью командлета Get-SqlDatabase.
- Подключение с помощью поставщика SQL Server PowerShell.
Использование Get-SqlDatabase
Командлет Get-SqlDatabase позволяет подключиться к базе данных в SQL Server или в базе данных SQL Azure. Он возвращает объект базы данных, который затем можно передать с помощью параметра InputObject командлета, который подключается к базе данных.
Использование поставщика SQL Server PowerShell
Поставщик SQL Server PowerShell отображает иерархию объектов SQL Server в виде путей, похожих на пути файловой системы. Используя SQL Server PowerShell, вы можете переходить по путям с помощью псевдонимов Windows PowerShell по аналогии с переходом по путям файловой системы с помощью команд. После перехода к целевому экземпляру и базе данных последующие командлеты будут использоваться в этой базе данных, как показано в следующем примере.
Примечание
Этот способ подключения к базе данных работает только для SQL Server (не поддерживается в базе данных SQL Azure).
Кроме того, можно указать путь к базе данных с помощью универсального параметра Path , а не переходить к базе данных.
Introduction
PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.
There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.
Вопросы безопасности при использовании PowerShell для настройки Always Encrypted
Так как основной задачей функции постоянного шифрования является обеспечение целостности зашифрованных конфиденциальных данных даже в случае нарушения безопасности системы базы данных, выполнение скрипта PowerShell, обрабатывающего ключи или конфиденциальные данные на сервере SQL Server, может снизить или вообще отменить эффект действия функции. Дополнительные рекомендации по безопасности см. в разделе (Вопросы безопасности для управления ключами).
PowerShell можно использовать для управления ключами Always Encrypted с разделением ролей и без разделения ролей, чтобы контролировать пользователей, имеющих доступ к фактическим ключам шифрования в хранилище ключей и доступ к базе данных.
Дополнительные рекомендации по безопасности см. в разделе (Вопросы безопасности для управления ключами).
Справочник по командлетам постоянного шифрования
Для постоянного шифрования доступны приведенные ниже командлеты PowerShell
| Командлет | Описание |
|---|---|
| Add-SqlAzureAuthenticationContext | Выполняет проверку подлинности в Azure и получает маркер проверки подлинности. |
| Add-SqlColumnEncryptionKeyValue | Добавляет новое зашифрованное значение для существующего объекта ключа шифрования столбца в базе данных. |
| Complete-SqlColumnMasterKeyRotation | Завершает смену главного ключа столбца. |
| Get-SqlColumnEncryptionKey | Возвращает все объекты ключа шифрования столбца, определенные в базе данных, или возвращает один объект ключа шифрования столбца с указанным именем. |
| Get-SqlColumnMasterKey | Возвращает объекты главного ключа столбца, определенные в базе данных, или возвращает один объект главного ключа столбца с указанным именем. |
| Invoke-SqlColumnMasterKeyRotation | Инициирует смену главного ключа столбца. |
| New-SqlAzureKeyVaultColumnMasterKeySettings | Создает объект SqlColumnMasterKeySettings, описывающий асимметричный ключ, который хранится в хранилище ключей Azure. |
| New-SqlCngColumnMasterKeySettings | Создает объект SqlColumnMasterKeySettings, описывающий асимметричный ключ, который хранится в хранилище ключей, поддерживающем API CNG. |
| New-SqlColumnEncryptionKey | Создает объект ключа шифрования столбца в базе данных. |
| New-SqlColumnEncryptionKeyEncryptedValue | Выводит зашифрованное значение ключа шифрования столбца. |
| New-SqlColumnEncryptionSettings | Создает объект SqlColumnEncryptionSettings, который инкапсулирует сведения о шифровании одного столбца, включая CEK и тип шифрования. |
| New-SqlColumnMasterKey | Создает объект главного ключа столбца в базе данных. |
| New-SqlColumnMasterKeySettings | Создает объект SqlColumnMasterKeySettings для главного ключа столбца с указанным поставщиком и путем к ключу. |
| New-SqlCspColumnMasterKeySettings | Создает объект SqlColumnMasterKeySettings, описывающий асимметричный ключ, который хранится в хранилище ключей с поставщиком CSP, поддерживающим CAPI. |
| Remove-SqlColumnEncryptionKey | Удаляет объект ключа шифрования столбца из базы данных. |
| Remove-SqlColumnEncryptionKeyValue | Удаляет зашифрованное значение из существующего объекта ключа шифрования столбца в базе данных. |
| Remove-SqlColumnMasterKey | Удаляет объект главного ключа столбца из базы данных. |
| Set-SqlColumnEncryption | Шифрует, расшифровывает или повторно шифрует указанные столбцы в базе данных. |
Overview of the Always Encrypted Feature
Always Encrypted feature is a handshake mechanism used to encrypt and decrypt data. Encryption here is achieved using certificates, and can be done only by users with access to the relevant certificates. To make a database column Always Encrypted, you must specify the encryption algorithm and the cryptographic keys that are used to protect the data. Always Encrypted needs two keys:
- Column Encryption Key (CEK)
- Column Master Key (CMK)
A Column Encryption Key is used to protect and encrypt data in a column. A Column Master Key is used to protect the (one or more) column encryption keys. The information about the Column Master Key is stored in external key stores like:
- Hardware Security Module (HSM): A hardware device specially designed to securely store sensitive data
Configuring Always Encrypted
To set up Always Encrypted, we need to generate the following:
- Key metadata
- Encryption properties of the selected database columns, and/or encrypting the data that may already exist in columns that need to be encrypted.
However, not all of these are supported in T-SQL. Therefore, we need to use client-side tools, such as the SQL Server Management Studio or PowerShell to accomplish these tasks.
| Task | SSMS | PowerShell | T-SQL |
| Prototyping Column Master Key and Column Encryption Key | Yes | Yes | No |
| Registering the Master key and Column Encryption Key metadata | Yes | Yes | Yes |
| Table creation with column encryption | Yes | Yes | Yes |
| Defining column encryption on an existing database columns | Yes | Yes | No |
Создание пользовательского диска
Создание и использование пользовательского диска
-
Используйте New-PSDrive для определения пользовательского диска. Используйте параметр Root для указания пути, представляемого именем пользовательского диска.
-
Используйте имя пользовательского диска в таких командлетах перехода по пути, как Set-Location.
Пример пользовательского диска (PowerShell)
В приведенном ниже примере создается виртуальный диск AWDB, сопоставленный с узлом для развернутой копии образца базы данных AdventureWorks2012. Виртуальный диск затем используется для перехода к таблице в базе данных.
SQL Server Management Objects (SMO)
SMO is a pain to some, but once you learn how to research the SMO namespace in MSDN your eyes can be opened to the possibilities. SMO is most commonly seen used to perform administration task against SQL Server instance(s). You can use this to do a check of the configuration instance where you may do a comparison of the settings to your standard configuration or for audit situations. It offers some flexibility over executing the equivalent T-SQL via Invoke-Sqlcmd, if there is an option via T-SQL.
You can also execute T-SQL through SMO if you wish and there is one benefit using this method over the .NET method. The benefit you get is executing T-SQL statements that may include the “GO” batch separator. Using the .NET method errors when it hits the first “GO” in a script, because it is not true SQL syntax. This can be useful in deployments where someone may have scripted out the objects via SSMS, which will put in the “GO” statement. If you happen to have gotten a long script, or a zipped file that contains hundreds of scripts, utilizing SMO in this situation saves a good bit of headache.
SQLPS Module
Importing the SQLPS module into a PS session provides the same access using the utility does, but allows you to operate in the PS version of the OS you operate under. In SQL Server 2008 and 2008 R2 you will load the SQLPS as a snap-in (Add-PSSnapin), then with SQL Server 2012 and up it is imported (Import-Module).
When you import the module it will load the SQLSERVER:\ provider and change your location to that path. You will also have access to the cmdlets offered in the module, which these are also accessible via the utility. You can get a list of those cmdlets using the Get-Command:
The most commonly known cmdlet out of this module is, Invoke-Sqlcmd. This is generally thought of as a PS replacement for the old sqlcmd command-line utility, that to date is still available in currently supported versions of SQL Server. You utilize this cmdlet to execute any T-SQL query that you want against one or multiple instances. The advantage you get using Invoke-Sqlcmd over the command-line utility is the power of handling output in PS. The output from the cmdlet is created as a DataTable (System.Data.DataRow is the exact type).
Just passing the full command to Get-Member will show the TypeName:
You will also see in the output that if you properly named your columns in your SELECT statement they show up as properties in that array. You can leverage the data conversions available in PS such as ConvertTo-Csv, or even ConvertTo-HTML if you needed to build an HTML report.
