Зеркальное отображение (безопасность на основе сертификатов, следящий сервер).

Сегодня речь пойдет о реализации такого решения высокого уровня доступности как зеркалирование баз данных. Это очень полезное и легкое в развертывании решение, которое может значительно повысить доступность приложений. Когда зеркалирование появилось в MS SQL 2005 его даже стали называть убийцей кластеров за отсутствие сложных административных настроек, легкость в реализации, возможность автоматической отработки отказа. При этом в отличие от кластеров здесь нет общего дискового хранилища. Вместо этого каждый участник сеанса зеркального отображения может располагаться на собственных дисках, что еще больше повышает безопасность. В качестве примера создадим простую базу данных с одной таблицей для хранения документов. В данном решении будут задействованы 3 сервера: основной, к которому могут подключаться приложения, зеркальный с синхронизированной резервной копией данных и следящий, который нужен для автоматической отработки отказа (если работа будет настроена в безопасном режиме).

if db_id ( N'Docs' ) is null
begin
 create database Docs
  on primary
  (
   name  = DocData,
   filename = 'C:\В\DbData\DocData.mdf',
   size  = 10 Mb,
   filegrowth = 10 Mb,
   maxsize  = unlimited
  )
  log on
  (
   name  = DocLog,
   filename = 'C:\В\TranLog\DocLog.ldf',
   size  = 10 Mb,
   filegrowth = 10 Mb,
   maxsize  = unlimited
  )
end
go

alter database Docs set recovery full
go

use Docs
go

create table dbo.Documents
(
 iDocId int identity ( 1, 1 ) not null,
 vcDoc varchar ( 100 )  not null,
 varDoc varbinary ( max ) not null,
 constraint PK_Documents_iDocId primary key clustered ( iDocId asc ) on [primary],
 constraint AK_Documents_vcDoc unique nonclustered ( vcDoc asc ) on [primary]
) on [primary]
go

insert into dbo.Documents ( vcDoc, varDoc )
 values ( 'Test1', 0x )
go

Теперь для подготовки сеанса зеркального отображения необходимо сделать полный бэкап базы и бэкап ее журнала транзакций:

backup database Docs
 to disk = 'C:\В\Docs.bak'
 with format, init, compression, stats = 1, norewind, checksum
go

backup log Docs
 to disk = 'C:\В\Docs.trn'
 with format, init, compression, stats = 1, norewind, checksum
go

Созданные резервные копии необходимо восстановить на зеркальном сервере с опцией norecovery.

if db_id ( N'Docs' ) is null
begin
 restore database Docs
  from disk = 'C:\В\Docs.bak'
  with
   move 'DocData' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\DocData.mdf',
   move 'DocLog' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\DocLog.ldf',
   stats = 1,
   norecovery
 restore log Docs
  from disk = 'C:\В\Docs.trn'
  with
   stats = 1,
   norecovery
end
go

Приступим к настройке объектов для передачи данных между серверами. Понадобятся конечные точки и учетные записи. Безопасность конечных точек лучше настраивать с помощью сертификатов (а не Windows). В этом случае не придется беспокоиться о том под какими учетными записями работают службы сервера (локальными или доменными), входят ли компьютеры в один домен или в разные домены с определенными отношениями (или же находятся вне доменов).

Итак, на основном сервере требуется создать логин для доступа к зеркальному и следящему серверам, настроить сертификат, создать конечную точку, подготовить резервные копии сертификата для восстановления на двух остальных серверах.

use master
go

if suser_id ( N'MainRepl' ) is null
begin
 create login MainRepl
  with
  password  = N'2#$@#!',
  check_policy  = off,
  default_database = master,
  default_language = russian
end
go

use master
go

if database_principal_id ( N'MainReplUser' ) is null
begin
 create user MainReplUser
  from login MainRepl
  with default_schema = dbo
end
go

use master
go

if not exists
(
 select *
 from sys.symmetric_keys
 where name = N'##MS_DatabaseMasterKey##'
)
begin
 create master key
  encryption by password = N'$R#tt5feR#R#D'
end
go

use master
go

if cert_id ( N'MainReplHost' ) is null
begin
 create certificate MainReplHost
  authorization dbo
  with subject = N'Сертификат главного сервера.',
  expiry_date = '2017-12-31',
  start_date = '2016-07-01'
end
go

if not exists
(
 select *
 from sys.endpoints
 where name = N'Mirroring'
)
begin
 create endpoint Mirroring
  state = started
  as tcp
  (
   listener_port = 7024,
   listener_ip = all
  )
  for database_mirroring
  (
   authentication = certificate MainReplHost,
   encryption = required algorithm aes,
   role = all
  )
end
go

backup certificate MainReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\MainReplHost.cer'
go
backup certificate MainReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MainReplHost.cer'
go

В коде, приведенном выше, перед созданием сертификата в master был создан главный ключ БД для защиты сертификата, так как последний не защищен паролем. Для конечной точки прописана безопасность на основе сертификата. Резервная копия сертификата создана дважды: в доступных местах для зеркального и следящего серверов (следящий и зеркальный сервера на моем компьютере работают под локальными учетными записями). Аналогичные действия требуется выполнить на двух оставшихся серверах. Вот код для зеркального сервера:

use master
go

if suser_id ( N'MirrorRepl' ) is null
begin
 create login MirrorRepl
  with
  password  = N'2#$@#!',
  check_policy  = off,
  default_database = master,
  default_language = russian
end
go

use master
go

if database_principal_id ( N'MirrorReplUser' ) is null
begin
 create user MirrorReplUser
  from login MirrorRepl
  with default_schema = dbo
end
go

use master
go

if not exists
(
 select *
 from sys.symmetric_keys
 where name = N'##MS_DatabaseMasterKey##'
)
begin
 create master key
  encryption by password = N'^YgfeHEF$#23'
end
go

use master
go

if cert_id ( N'MirrorReplHost' ) is null
begin
 create certificate MirrorReplHost
  authorization dbo
  with subject = N'Сертификат зеркального сервера.',
  expiry_date = '2017-12-31',
  start_date = '2016-07-01'
end
go

if not exists
(
 select *
 from sys.endpoints
 where name = N'Mirroring'
)
begin
 create endpoint Mirroring
  state = started
  as tcp
  (
   listener_port = 7025,
   listener_ip = all
  )
  for database_mirroring
  (
   authentication = certificate MirrorReplHost,
   encryption = required algorithm aes,
   role = all
  )
end
go

backup certificate MirrorReplHost
 to file = 'C:\В\MirrorReplHost.cer'
go
backup certificate MirrorReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MirrorReplHost.cer'
go

Здесь все аналогично коду для основного сервера, только для конечной точки указан другой порт из-за того что в данном примере все 3 сервера находятся на одном компьютере. Наконец приведем такой же код для следящего сервера (у конечной точки опять-таки порт отличен от портов конечных точек основного и зеркального серверов).

use master
go

if suser_id ( N'WitnessRepl' ) is null
begin
 create login WitnessRepl
  with
  password  = N'erg343grF#$FE',
  check_policy  = off,
  default_database = master,
  default_language = russian
end
go

use master
go

if database_principal_id ( N'WitnessReplUser' ) is null
begin
 create user WitnessReplUser
  from login WitnessRepl
  with default_schema = dbo
end
go

use master
go

if not exists
(
 select *
 from sys.symmetric_keys
 where name = N'##MS_DatabaseMasterKey##'
)
begin
 create master key
  encryption by password = N'we32@#ESe'
end
go

use master
go

if cert_id ( N'WitnessReplHost' ) is null
begin
 create certificate WitnessReplHost
  authorization dbo
  with subject = N'Сертификат следящего сервера.',
  expiry_date = '2017-12-31',
  start_date = '2016-07-01'
end
go

if not exists
(
 select *
 from sys.endpoints
 where name = N'Mirroring'
)
begin
 create endpoint Mirroring
  state = started
  as tcp
  (
   listener_port = 7023,
   listener_ip = all
  )
  for database_mirroring
  (
   authentication = certificate WitnessReplHost,
   encryption = required algorithm aes,
   role = all
  )
end
go

backup certificate WitnessReplHost
 to file = 'C:\В\WitnessReplHost.cer'
go
backup certificate WitnessReplHost
 to file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\WitnessReplHost.cer'
go

После того как все конечные точки созданы необходимо обеспечить доступ с каждого сервера на 2 других сервера. Для этого для сертификата и логина на каждом сервере необходимо на двух других серверах восстановить резервную копию сертификата и создать одноименного пользователя с правами на конечную точку и на сертификат. Так это делается на основном сервере (пароли на каждом сервере не обязаны совпадать).

use master
go

if suser_id ( N'MirrorRepl' ) is null
begin
 create login MirrorRepl
  with
   password  = 'EG#WferWF43edf',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go
if suser_id ( N'WitnessRepl' ) is null
begin
 create login WitnessRepl
  with
   password  = 'rf34ewfFWfdsd#@#$$',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go

use master
go
if database_principal_id ( N'MirrorReplUser' ) is null
begin
 create user MirrorReplUser
  from login MirrorRepl
  with default_schema = dbo
end
go
if database_principal_id ( N'WitnessReplUser' ) is null
begin
 create user WitnessReplUser
  from login WitnessRepl
  with default_schema = dbo
end
go

use master
go
if cert_id ( N'MirrorReplHost' ) is null
begin
 create certificate MirrorReplHost
  authorization MirrorReplUser
  from file = 'C:\В\MirrorReplHost.cer'
end
go
if cert_id ( N'WitnessReplHost' ) is null
begin
 create certificate WitnessReplHost
  authorization WitnessReplUser
  from file = 'C:\В\WitnessReplHost.cer'
end
go

use master
go
grant connect on endpoint::Mirroring to MirrorRepl, WitnessRepl
go

Аналогичный код прогоняется на зеркальном сервере (создаются логины и пользователи для основного и следящего серверов, создаются сертификаты из резервных копий, пользователи делаются владельцами своих сертификатов, логинам предоставляется доступ на подключение к конечной точке).

use master
go

if suser_id ( N'MainRepl' ) is null
begin
 create login MainRepl
  with
   password  = '43#T%Fev',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go
if suser_id ( N'WitnessRepl' ) is null
begin
 create login WitnessRepl
  with
   password  = 'dgf3t4SVSDv',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go

use master
go
if database_principal_id ( N'MainReplUser' ) is null
begin
 create user MainReplUser
  from login MainRepl
  with default_schema = dbo
end
go
if database_principal_id ( N'WitnessReplUser' ) is null
begin
 create user WitnessReplUser
  from login WitnessRepl
  with default_schema = dbo
end
go

use master
go
if cert_id ( N'MainReplHost' ) is null
begin
 create certificate MainReplHost
  authorization MainReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\MainReplHost.cer'
end
go
if cert_id ( N'WitnessReplHost' ) is null
begin
 create certificate WitnessReplHost
  authorization WitnessReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MIRROR\MSSQL\DATA\WitnessReplHost.cer'
end
go

use master
go
grant connect on endpoint::Mirroring to MainRepl, WitnessRepl
go

В последний раз запустим аналогичный код для сервере-свидетеле:

use master
go

if suser_id ( N'MainRepl' ) is null
begin
 create login MainRepl
  with
   password  = 'rfT#$FRged',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go
if suser_id ( N'MirrorRepl' ) is null
begin
 create login MirrorRepl
  with
   password  = 'e3e#Rwd',
   check_policy  = off,
   default_database = master,
   default_language = russian
end
go

use master
go
if database_principal_id ( N'MainReplUser' ) is null
begin
 create user MainReplUser
  from login MainRepl
  with default_schema = dbo
end
go
if database_principal_id ( N'MirrorReplUser' ) is null
begin
 create user MirrorReplUser
  from login MirrorRepl
  with default_schema = dbo
end
go

use master
go
if cert_id ( N'MainReplHost' ) is null
begin
 create certificate MainReplHost
  authorization MainReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MainReplHost.cer'
end
go
if cert_id ( N'MirrorReplHost' ) is null
begin
 create certificate MirrorReplHost
  authorization MirrorReplUser
  from file = 'C:\Program Files\Microsoft SQL Server\MSSQL12.WITNESS\MSSQL\DATA\MirrorReplHost.cer'
end
go

use master
go
grant connect on endpoint::Mirroring to MainRepl, MirrorRepl
go

Все готово к включению сеанса зеркального отображения. Для этого сперва надо на зеркальном сервере указать основной сервер в качестве партнера:

alter database Docs set partner = 'tcp://LAPTOP-LBO51UJL:7024'

Выше в качестве значения свойства partner указывается полное доменное имя и через двоеточие порт конечной точки на другом сервере (впереди пишется "tcp://"). Следующим шагом нужно на основном сервере указать адрес зеркального:

alter database Docs set partner = 'tcp://LAPTOP-LBO51UJL:7025'

Зеркальное отображение заработало. Надо отметить что зеркалирование может работать в одном из двух режимов: режим высокой безопасности либо режим высокой производительности. При втором режиме фиксация транзакции происходит асинхронно относительно передачи данных на зеркальный сервер. Этот режим подходит для серверов, в которых не происходят массовые операции и допустима некоторая потеря данных. В асинхронном режиме недоступна автоматическая отработка отказа. Если же требуется наивысший уровень безопасности и нет массовых операций, то можно использовать зеркалирование с синхронном режиме. Для асинхронного режима требуется выполнить инструкцию на основном сервере:

alter database Docs set partner safety off

Опция full позволяет работать в синхронном режиме:

alter database Docs set partner safety full

На основном или зеркальном сервере можно выполнить запрос:

select db_name ( database_id ), *
from sys.database_mirroring
where  db_name ( database_id ) = 'Docs'

Это позволит проверить роль текущего сервера (основной или зеркальный), состояние сеанса зеркалирования (синхронизировано либо идет прием новых строк журнала транзакций), адрeса партнеров и следящего сервера, состояние соединения со свидетелем и режим зеркалирования.
Для выполнения ручной отработки отказа, нужно на сервере, являющемся на данный момент основным выполнить код (требуется перевод в режим высокой безопасности):

use master alter database Docs set partner failover

В этот момент на сервере, который был основным, база данных Docs перейдет в состояние restoring, а на сервере, который был зеркальным, она станет доступной. Перенос данных уже пойдет в обратную сторону: от зеркального сервера к основному. Эти изменения немедленно отразятся и в представлении sys.database_mirroring. Зеркальное отображение можно приостановить, если в этом есть необходимость, а затем продолжить. Для этого используются соответственно инструкции:

alter database Docs set partner suspend
alter database Docs set partner resume

Надо только помнить, что если сеанс приостановлен, то журнал транзакций в базе на основном сервере не будет усекаться до тех пор пока зеркало не станет синхронизированным с основным. Для удаления сеанса используется значение off для опции partner:

alter database Docs set partner off

Если основной сервер стал недоступным и не произошло отработки отказа (при этом следящего нет либо он подключен к зеркальному), то можно принудительно сделать доступным зеркальный сервер. Это приведет к остановке зеркалирования и возможно потере данных, которые не были реплицированы с основного сервера. Зеркальный сервер делается доступным с помощью такой инструкции:

alter database Docs set partner force_service_allow_data_loss

Подключим теперь следящий сервер к основному для возможности автоматической отработки отказа:

alter database Docs set witness = 'tcp://LAPTOP-LBO51UJL:7023'

Для отключения следящего сервера достаточно выполнить код:

alter database Docs set witness off

На следящем сервере можно делать запросы к представлению sys.database_mirroring_witnesses для просмотра состояния сеанса зеркалирования. Теперь подключения приложений могут автоматически перенаправляться на доступный сервер. Для этого требуется указать дополнительный атрибут в строке подключения. Например, на C# можно так создать экземпляр подключения:

using (SqlConnection cn = new SqlConnection(@"Data Source=LAPTOP-LBO51UJL;Failover Partner=LAPTOP-LBO51UJL\MIRROR;Initial Catalog=Docs;Integrated Security=True;"))

Если в момент отработки отказа соединение будет выполнять какую-либо работу с базой, то активная транзакция откатится, но последующие подключения пройдут без проблем.
То что со свидетелем работает автоматическая отработка отказа, это здорово: повышается доступность приложений. Но хорошо бы получать автоматические уведомления о факте отработки отказа. Приведу пример того как это сделать с помощью уведомлений о событиях. Для удобства на основном и зеркальном серверах создадим базу данных с таблицей, в которой будет храниться лог сообщений о состоянии сеанса зеркалирования. Также понадобится настройка объектов Service Broker и создание уведомления о событии. Итак, код, приведенный ниже, нужно выполнить на основном и зеркальном серверах:

if db_id ( 'CheckData' ) is null
begin
 create database CheckData
end
go
alter database CheckData set enable_broker
go

use CheckData
go
if object_id ( 'dbo.DbMirrorLog', 'U' ) is null
begin
 create table dbo.DbMirrorLog
 (
  dtDate datetime  not null,
  vcLog varchar ( max )  not null,
  vcDb varchar ( 200 )  not null,
  iState int   not null,
  iRowId int identity ( 1, 1 ) not null,
  constraint PK_bMirrorLog primary key clustered ( dtDate asc, iRowId asc ) on [primary]
 ) on [primary]
end
go

if object_id ( 'dbo.LogMirrorState', 'P' ) is null
begin
 exec sp_executesql 'create proc dbo.LogMirrorState as return'
end
go

alter proc dbo.LogMirrorState
as
begin
 declare @xMes xml, @uidHan uniqueidentifier

 ;
 receive top ( 1 ) @xMes = message_body, @uidHan = conversation_handle
 from dbo.MirrorNotify

 if @@rowcount = 0
 begin
  return
 end

 insert into dbo.DbMirrorLog
 (
  dtDate,
  vcLog,
  vcDb,
  iState
 )
 values
 (
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/PostTime)[1]', 'datetime' ), getdate () ),
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/TextData)[1]', 'varchar ( max )' ), '' ),
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar ( max )' ), '' ),
  isnull ( @xMes.value ( '(/EVENT_INSTANCE/State)[1]', 'int' ), -1 )
 )
end
go

if object_id ( 'dbo.MirrorNotify', 'SQ' ) is null
begin
 create queue dbo.MirrorNotify
 with
  status = on,
  activation
  (
   status = on,
   procedure_name = dbo.LogMirrorState,
   max_queue_readers = 4,
   exec as owner
  )
 on [default]
end
go

if not exists
(
 select *
 from sys.services
 where name = 'MirrorLog'
)
begin
 create service MirrorLog
  on queue MirrorNotify ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )
end
go

if not exists
(
 select *
 from sys.server_event_notifications
 where name = 'DbMirrorLog'
)
begin
 create event notification DbMirrorLog
  on server for DATABASE_MIRRORING_STATE_CHANGE
  to service 'MirrorLog', 'current database'
end
go

Можно выполнить несколько мануальных отработок отказа, а затем на основном сервере выполнить инструкцию shutdown для его отключения (либо отключить его в диспетчере конфигураций). При этом следящий сервер сразу определит, что основной недоступен и выполнит отработку отказа на зеркало. Затем нужно включить основной сервер, и он синхронизируется с бывшим зеркальным и сам станет зеркальным. Если сделать запросы к таблице DbMirrorLog, то увидим такой лог:

Если настроить на сервере Database Mail, то в процедуре активации очереди можно вызывать процедуру msdb.dbo.sp_send_dbmail для мгновенного оповещения об отработке отказа.

Исключения в logon-триггерах

Logon-триггеры, которые появились в MS SQL 2005 предоставляют удобный механизм для логирования подключений. Подключения могут протоколироваться и средствами профайлера и другими методами слежения за процессами. Однако Logon-триггеры превратили процесс подключения в транзакцию, которую при желании можно откатить. Например, можно выполнять проверки для разрешения или запрета на подключение к серверу.

Приведем такой пример. Создадим базу данных для хранения настроек и логов:

use master
go

if db_id ( N'LogStore' ) is null
begin
        create database LogStore

        on primary

        (

                name = LogData,

                filename = N'C:\Users\В\Desktop\LogData.mdf',

                size = 100 Mb,

                filegrowth = 100 Mb,

                maxsize = unlimited

        )

        log on

        (
                name = LogDataLog,
                filename = N'C:\Users\В\Desktop\LogDataLog.ldf',
                size = 100 Mb,
                filegrowth = 100 Mb,

                maxsize = unlimited

        )

end

go

Создадим таблицу, где будут перечислены компьютеры, с которых запрещено подключаться к серверу:

use LogStore
go

if object_id ( N'dbo.ExHosts', N'U' ) is null

begin

        create table dbo.ExHosts

        (

                iHostId int identity ( 1, 1 ) not null,

                vcHost nvarchar ( 200 ) not null,

                constraint PK_ExHosts_iHostId primary key clustered ( iHostId asc ) on [primary],

                constraint AK_ExHosts_vcHost unique nonclustered ( vcHost asc ) on [primary]

        )

end

go

Логон-триггер может проверять компьютер соединения и откатывать транзакцию, если окажется, что в таблице есть строка с таким компьютером. Перед созданием триггера надо определиться с тем под какой учетной записью он будет работать (ведь не стоит давать всем доступ на базу и на таблицу). Создадим такую учетную запись и предоставим ей права:

use master

go

if suser_id ( N'LogLogin' ) is null

begin

create login LogLogin

        with

                   password = N'qw3@',

                   check_policy = off,

                   default_database = LogStore,

                   default_language = russian

end

go

use LogStore

go

if database_principal_id ( N'LogUser' ) is null

begin

        create user LogUser

                from login LogLogin

                with default_schema = dbo
end

go

if database_principal_id ( N'LogRole' ) is null

begin

        create role LogRole

                  authorization dbo

end

go

grant select on object::dbo.ExHosts to LogRole

go

alter role LogRole add member LogUser

go

Теперь можно создать триггер на вход:

use master

go

if not exists
(
        select *

        from sys.server_triggers

        where name = N'CheckHost'
)
begin
        exec sp_executesql N'create trigger CheckHost on all server for logon as return'

end

go

alter trigger CheckHost on all server

with exec as 'LogLogin'

for logon

as

begin

        set nocount, xact_abort on

        if exists

        (

                select *

                from LogStore.dbo.ExHosts

                where vcHost = isnull ( host_name (), N'' )

        )

        begin

                ; throw 60000, N'Попытка входа с запрещенного хоста.', 1

                if @@trancount > 0

                begin

                        rollback tran

                end

        end
end
go

При попытке зайти на сервер с запрещенного компьютера произойдет ошибка. Например, если попытаться сделать такое подключение в Management Studio, то появится сообщение об ошибке:

Текста сообщения об ошибке, написанного в коде триггера, здесь не видно. Но такие ошибки попадают в журнал сервера, где их можно обнаружить:

Казалось бы все неплохо. Но при работе с logon-триггерами есть один подводный камень, состоящий в следующем. Код в таких триггерах выполняется в контексте неявной транзакции, опция xact_abort всегда включена. Любое исключение, возникающее из-за процессов, которые не предусмотрел разработчик, приводит к откату этой транзакции и, как следствие, невозможности подключения к серверу. В худшем случае, когда ошибка происходит при любой попытке входа, создается ситуация, когда никто не может подключиться к серверу. Никакая обработка ошибок, например, с помощью try/catch, не поможет, поскольку, как было отмечено выше, транзакция уже откатилась, и ничто не поможет. В этой ситуации можно входить на сервер используя некоторые специальные режимы. Например, режим приоритетного соединения администратора позволяет зайти на сервер, поскольку на такое соединение триггеры не действуют. Для этого требуется ввести в командной строке: sqlcmd -S <имя сервера> -E -A. После чего требуется ввести текст команды: disable trigger CheckHost on all server. Вместо командной строки можно использовать и Management Studio, если в окне подключения перед именем сервера написать: admin:. Возможно, также потребуется произвести эти действия с серверного компьютера (если отключена опция remote admin connection).

Хотел бы предложить подход, который минимизирует вероятность ошибок в работе триггера. Во-первых, логика работы кода должна быть как можно более простой. Пусть это будут простые проверки. Если требуются более сложные способы обработки данных, не связанные с проверкой возможности подключения, то лучше вынести их в другие, нетранзакционные, средства отслеживания событий. Во-вторых, нужно проанализировать все выполняемые в триггере действия по порядку. В нашем случае они такие:

1. Триггер работает от имени логина LogLogin. Пока триггер работает в его контексте удалить логин не получится. Здесь все в порядке.

2. Делается попытка просмотра объекта в базе данных LogStore. Значит, чтобы все было хорошо, требуется, чтобы существовала база данных с таким именем, она должна быть доступной, и текущая учетная запись должна иметь права на подключение к ней.

3. Производится попытка запроса к таблице dbo.ExHosts. Для ее успеха требуется наличие таблицы с таким именем и права на запросы к ней для текущей учетной записи.

4. В запросе к таблице делается условие на столбец. Поэтому требуется удостовериться, что у заданной таблицы существует столбец с таким именем и что его тип данных такой какой мы ожидаем: nvarchar.

Итак, вставим в текст триггера все эти проверки, сделав его работу максимально безопасной. Ниже идет полный код с моими комментариями.

alter trigger CheckHost on all server
with exec as 'LogLogin'
for logon
as
begin
        set nocount, xact_abort on

        -- проверим возможность выполнения запросов к системныму каталогу с перечнем баз данных
    if isnull ( has_perms_by_name ( 'sys.databases', 'object', 'select', null, null ), 0 ) = 0
    begin
        return
    end

    -- существование базы данных с заданным именем в состоянии online
    if not exists
    (
        select *
        from sys.databases
        where name = 'LogStore' and state_desc = 'ONLINE'
    )
    begin
        return
    end

    -- возможность подключения к базе данных
    if isnull ( has_perms_by_name ( 'LogStore', 'database', 'connect', null, null ), 0 ) = 0
    begin
        return
    end

    -- наличие таблицы
    if object_id ( N'LogStore.dbo.ExHosts', N'U' ) is null
    begin
        return
    end

    -- наличие доступов на select к таблице (эта функция корректно учтет наличие несколько доступов и запретов)
    if isnull ( has_perms_by_name ( 'LogStore.dbo.ExHosts', 'object', 'select', null, null ), 0 ) = 0
    begin
        return
    end

    -- проверка существования столбца с определенным именем
    declare @vcSql nvarchar ( max ), @iColId int
    set @vcSql = N'use LogStore set @iColId = columnproperty ( object_id ( N''dbo.ExHosts'', N''U'' ), N''vcHost'', N''ColumnId'' )'
    exec sp_executesql @vcSql, N'@iColId int out', @iColId = @iColId out
    if @iColId is null
    begin
        return
    end

    -- возможность выполнения запросов в системному представлению с перечнем столбцов
    declare @bPerm int
    set @vcSql = N'use LogStore set @bPerm = has_perms_by_name ( ''LogStore.sys.columns'', ''object'', ''select'', null, null )'
    exec sp_executesql @vcSql, N'@bPerm int out', @bPerm = @bPerm out
    if @bPerm = 0
    begin
        return
    end


    -- наличие у столбца нужного типа данных
    if not exists
    (
        select *
        from LogStore.sys.columns
        where
            name = 'vcHost' and
            object_id = object_id ( N'LogStore.dbo.ExHosts', N'U' ) and
            type_name ( system_type_id ) = 'nvarchar' and
            type_name ( user_type_id ) = 'nvarchar' and
            max_length = 400
    )
    begin
        return
    end
    if exists
    (
        select *
        from LogStore.dbo.ExHosts
        where vcHost = isnull ( host_name (), N'' )
    )
    begin
        ; throw 60000, N'Попытка входа с запрещенного компьютера.', 1
        if @@trancount > 0
        begin
            rollback tran
        end
    end
end
go

В коде делается проверка возможности всех действий, которые выполняются при проверке законности входа. Причем, если для проверки возможности действия, происходит обращение к системному объекту, то предварительно делается проверка возможности обращения к системному объекту. Теперь, если, скажем, изменить имя столбца (exec LogStore..sp_rename 'dbo.ExHosts.vcHost', 'vcName', 'column') или сделать запрет для учетной записи на запросы к таблице (exec ( N'use LogStore deny select on object::dbo.ExHosts to LogUser' )) или вообще удалить учетную запись, триггер спокойно продолжит свою работу. В этой ситуации он, конечно, не сможет выполнять свои прямые обязанности по проверке законности подключений. Но в большинстве случаев это лучше чем совсем никого не пускать.