null

SSHFS-WIN подключаем SFTP раздел в MS SQL SERVER

11 August 2022 0 Maxim Zhukov

Этот пост является частью трилогии извращений по подключению и использованию SFTP с авторизацией по ключам в среде Windows.

Предыдущие части:

  1. Монтируем раздел SFTP/SSHFS в Windows с авторизацией по ключам
  2. SSHFS-win неочевидная ошибка прав доступа


Возможно, эта история превратиться в тетралогию, и читатель, как и некоторые из моих коллег, спросит : " ну зачем вообще это нужно? есть же CIFS, SMB, нативные для WINDOWS".

Отвечу, что как минимум есть несколько сценариев, когда такие извращения могут применяться в жизни:

  1. Существующая система хранения не поддерживает "Виндовс-френдли" протоколы.
  2. "Виндовс-френдли" протоколы выключены по соображениям безопасности
  3. "Виндовс-френдли" протоколы выключены по политике ИТ
  4. Требование политики безопасности к доступу по авторизации по ключам
  5. Параноидальные мысли как заказчика, так и исполнителя

Ну и более того, в конкретно данной заметке, посвященной монтированию в MS SQL SERVER, я расскажу об особенностях переменных окружения и сессий для MS SQL SERVER и MS SQL SERVER AGENT, так как и в аналогичных случаях использование системных или виртуальных учётных записей при использовании своих заданий и вызова сторонных исполняемых файлов могут запутать несведующего в особенностях среды выполнения ИТшника.

Поехали - меняем учетные записи служб MS SQL и MS SQL AGENT

Службы MSSQLSERVER и MSSQLSERVERAgent должны быть запущены от имени одних и тех же пользовательских учётных данных (НЕ системных и Не виртуальных по причине различия переменных окружения).

Изменение учетных данных служб требует их перезапуска. Изменение производить строго через оснастку SQL SERVER CONFIGURATION MANAGER

 

В примере на созданную ПОЛЬЗОВАТЕЛЬСКУЮ учётную запись DB-BACKUP

Создаем скрипты и рабочий каталог

Далее создаем каталог ( в примере c:\.backupstorage\ ) куда помещаем :

  1. скрипт PowerShell с расширением .ps1 - в примере c:\.backupstorage\mount.ps1
  2. Ключ доступа к SFTP - в примере совпадает с именем хоста $HOSTNAME=hostname

Сам скрипт для кастомизации Вами с основными положениями:

$CERTIFICATE=hostname # в моём примере имя сертификата совпадает с именем узла
$BPATCH= ‘c:\\.backupstorage’ # рабочий каталог
$USERNAME="StorageUSER" #тут имя пользователя от которого подключаемся к хранилищу

set PATH="C:\Program Files\SSHFS-Win\bin"

cd  'C:\Program Files\SSHFS-Win\bin\'

.\sshfs-win svc \sshfs.k\$USERNAME@STORAGEIP\. T: random_text -o create_file_umask=0000 -o IdentityFile="$BPATCH\\$CERTIFICATE"

# STORAGEIP - адрес сервера хранилища с SFTP
# T: - литера точки монтирования раздела, выбирайте любую свободную, в помощь Вам net use

Создаём задачу в MS SQL SERVER

состоящую из подготовки:

EXEC sp_configure ''xp_cmdshell'',1
GO
RECONFIGURE

и выполнения нашего PowerShell скрипта

 

Для упрощения я прилагаю TransactSQL скрипт делающий всё разом

USE [msdb]
GO

/****** Object:  Job [task sshfs mount backup]    Script Date: 15.06.2022 13:22:52 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 15.06.2022 13:22:52 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'task sshfs mount backup', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Подключение SFTP хранилища для резервного копирования', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sql_admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Preparation]    Script Date: 15.06.2022 13:22:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Preparation', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC sp_configure ''show advanced options'', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure ''xp_cmdshell'',1
GO
RECONFIGURE
GO
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [sshfs mount]    Script Date: 15.06.2022 13:22:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sshfs mount', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\.backupstorage\mount.ps1', 
		@flags=40
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'StartUP', 
		@enabled=1, 
		@freq_type=64, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20220615, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'94f493a3-865d-45e6-b749-df26c5ea0a76'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Запускаем

Запустить задачу

task sshfs mount backup

 

Проверяем

 

Проверить, наличие диска

EXEC XP_CMDSHELL 'Dir T:'