Расшифровка запароленной храниной процедуры SQL

if exists (select * from sysobjects where id = object_id(N'procDecryptObject2005')
and xtype in (N'P'))
DROP PROCEDURE procDecryptObject2005
GO


CREATE PROCEDURE procDecryptObject2005
(
@Schema NVARCHAR(128)=N'dbo',
@ObjectName NVARCHAR(128)=N'myproc'
) AS
BEGIN

DECLARE @i INT
DECLARE @ObjectDataLength INT
DECLARE @ContentOfEncryptedObject NVARCHAR(MAX)
DECLARE @ContentOfDecryptedObject NVARCHAR(MAX)
DECLARE @ContentOfFakeObject NVARCHAR(MAX)
DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX)
DECLARE @ObjectType NVARCHAR(128)
DECLARE @ObjectID INT

SET NOCOUNT ON

SET @ObjectID = OBJECT_ID('[' + @Schema + '].[' + @ObjectName + ']')

-- Check that the provided object exists in the database.
IF @ObjectID IS NULL
BEGIN
RAISERROR('The object name or schema provided does not exist in the database', 16, 1)
RETURN
END

-- Check that the provided object is encrypted.
IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1)
BEGIN
RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)
RETURN
END

-- Determine the type of the object
IF OBJECT_ID('[' + @Schema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL
SET @ObjectType = 'PROCEDURE'
ELSE
IF OBJECT_ID('[' + @Schema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL
SET @ObjectType = 'TRIGGER'
ELSE
IF OBJECT_ID('[' + @Schema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL
SET @ObjectType = 'VIEW'
ELSE
SET @ObjectType = 'FUNCTION'

-- Get the binary representation of the object- syscomments no longer holds
-- the content of encrypted object.
SELECT TOP 1 @ContentOfEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @Schema + '].[' + @ObjectName + ']')
AND valclass = 1 and subobjid = 1

SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2


-- We need to alter the existing object and make it into a dummy object
-- in order to decrypt its content. This is done in a transaction
-- (which is later rolled back) to ensure that all changes have a minimal
-- impact on the database.
SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @Schema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength BEGIN IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000) ELSE SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) END -- Since we need to alter the object in order to decrypt it, this is done -- in a transaction SET XACT_ABORT OFF BEGIN TRAN EXEC(@ContentOfFakeObject) IF @@ERROR <> 0
ROLLBACK TRAN

-- Get the encrypted content of the new "fake" object.
SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @Schema + '].[' + @ObjectName + ']')
AND valclass = 1 and subobjid = 1

IF @@TRANCOUNT > 0
ROLLBACK TRAN

-- Generate a CREATE script for the dummy object text.
SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @Schema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength BEGIN IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000) ELSE SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) END SET @i = 1 --Fill the variable that holds the decrypted data with a filler character SET @ContentOfDecryptedObject = N'' WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength BEGIN IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000) ELSE SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2)) END WHILE @i <= @ObjectDataLength BEGIN --xor real & fake & fake encrypted SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1, NCHAR( UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^ ( UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1)) ))) SET @i = @i + 1 END -- PRINT the content of the decrypted object PRINT(@ContentOfDecryptedObject) END

Настройка репликации sql 2005

1. Создаем Aliases
Открываем Microsoft SQL Server 2005 \Configuration Tools \SQL Server Configuration Manager.

На всякий случай проверяем, открыты ли именованные каналы – SQL Server 2005 Network Configuration\ Protocols for MSSQLSERVER. Нам нужно, чтобы и Named Pipes и TCP/IP были Enabled. Помимо этого в SQL Native Client Configuration/Client Protocols Named Pipes и TCP/IP также должны быть Enabled.
Обратите внимание, что после этих действий обязательно нужно перезапустить службы: SQL Server 2005 Services – SQL Server (MSSQLSERVER) и SQL Server Agent(MSSQLSERVER).

Заходим на вкладку Native Client Configuration/Aliases. Выбираем New Alias и пишем все так, как показано на рисунке:

2. Создаем Linked Server. Теперь заходим в Microsoft SQL Server Management Studio. Выбираем вкладку – Objects\ Linked Servers. Выбираем New Linked Server

На вкладке General указываем следующие значения:
Linked Server – VPN (имя нашего коннекта)
Server Type – Other data source
Product Name – sql_server (волшебные слова – менять не рекомендуется)
Provider – SQL Native Client
Data Source – х.х.х.х (ip-адрес сервера, к которому мы коннектимся)
Переходим на вкладку – Security. Здесь выбираем галочку «Be made using security context» и указываем
Remote login – sa
With password – пароль sa (в БД, куда реплицируем данные):
И, наконец, переходим на вкладку Server Options. Здесь выставляем значения в True: Rpc, Rpc Out, Use Remote Collation:
Теперь нажимаем “OK” и видим, что в закладке Objects\ Linked Servers появилась новая запись – VPN:
Если сейчас запустить запрос: use master; select * from sys.server - то в качестве результата мы получим две строки: имя нашего сервера и VPN.

Настройка репликации

1. Предварительная подготовка реплицируемых данных. Так как вставка значений в столбцы счетчиков (идентифицирующие столбцы) невозможна, необходимо в таблицах подписчика, участвующих в репликации, установить для таких столбцов значение свойства "Не для репликации" - Да:
Если в вашей БД нет автоинкрементных полей, то данный пункт настройки можно смело пропустить.

2. Проверка правильности имен серверов, участвующих в репликации.
В репликации возможно использование только настоящих имен серверов. Если после установки имя сервера менялось или при попытке настройки репликации возникает ошибка: "В репликации SQL Server необходимо настоящее имя сервера для соединения с ним. Соединение с использованием псевдонима сервера, IP-адреса или любого другого имени не поддерживается. Укажите настоящее имя сервера, "server_name"" необходимо выполнить следующую проверку:

select @@servername

select serverproperty('servername')

Если запросы вернули разные результаты, необходимо выполнить:

exec sp_dropserver 'oldname', 'droplogins'

exec sp_addserver 'newname', 'local'

После чего перезапустить службу SQL Server.

3. Настройка распространителя. Для настройки распространителя необходимо открыть Microsoft SQL Server Management Studio и в контекстном меню пункта Replication выбрать Configure Distibution. Далее необходимо выбрать сервер, который будет использоваться качестве дистрибьютера (тот, с которого будет браться информация):
Теперь указываем каталог моментальных снимков. Каталог должен быть сетевым и на него должны быть настроены соответствующие права. Для этого создаем на диске C:\ папку Repl и делаем ее доступной (shared):
Теперь SQL Server хочет создать базу Distribution. Для этого ему нужно имя и место расположение файлов базы данных распространителя – оставим по-умолчанию:
Нажимаем далее и попадаем в окно, где нужно указать сервер, который будет являться издателем:
И под конец указываем, что «да, создаем БД distribution» и «Да, давайте создадим SQL Script»
Указываем, куда мы будем сохранить файл со скриптом:
И попадаем в окно, где нам показывают все, что мы хотим сделать. Смело нажимаем «Finish», и видим, что действительно все хорошо:
4. Удаление старой репликации. Если вдруг осталась старая репликация (ну, может быть кому-то не захотелось переставлять windows перед установкой SQL Server’a), нужно ее удалить. Для этого выполняем запрос:

Sp_removedbreplication

@db_name = ‘имя вашей БД’

@type = ‘tran’

После того, как нам написали, что все благополучно удалилось продолжаем.

5. Настройка публикации Указываем БД, которая будет использоваться качестве распространителя. Для этого нажимаем правой кнопкой мыши на закладку Replication и выбираем publisher properties. На закладке Publication Databases отмечаем галочкой нужную БД (в столбце Transactional):
6. Создаем публикацию В контекстном подменю закладки Publication\Local Publication выбираем New Publication. Далее указываем БД, которая у нас будет распространять данные:
Во втором окне мастера указываем тип публикации – transactional publication
Теперь указываем объекты, которые будем реплицировать. В нашем случае это будут таблицы и индексированные представления. Создадим отдельные публикации на таблицы и представления.
Для первой публикации отмечаем views: v_kontr_2 и v_kontr_bank_2. (Отметим, что каждый из view's представляет собой выборку из одной таблицы. Используются индексированные вьювы для того чтобы можно было реплицировать часть колонок в определенные таблицы).
Выделим левой кнопкой мыши поочередно каждую из них, нажимаем кнопку Article Properties\Set properties of Highlighted Indexed View Article. В открывшемся окне меняем свойство Destination object name на spr_kontr и kontr_bank соответственно (это таблицы, в которые в конечном итоге ).

В следующем окне выбираем «Tables referenced by views are required» и нажимаем Next. В следующем окне говорим, что мы не хотим создавать расписание для агента моментальных снимков, а хотим запустить его немедленно:
На следующем окне от нас ждут настроек учетной записи, с которой будет выполняться процесс агента моментальных снимков и агента чтения журнала. Нажимаем на Security Settings у Snapshot Agent’a (галочку Use the security settings from the Snapshot Agent не трогаем – пусть будет включена– не зачем вводить дважды одно и то же).
Указываем логин, от которого будет запускаться репликация у нас (исходная БД) и логин sa под которым будет осуществляться коннект к БД-распространителю и Next. В следующем окне ставим обе галочки (да, скрипт нам пригодится, правда только для публикаций с индексированными представлениями, а там, кто знает…) На следуем окне указываем, куда мы хотим сохранить скрипт и отмечаем, что мы хотим перезаписать файл, кодировку не трогаем. В последнем окне вводим имя публикации и читаем, все, что будет сделано. Если все в порядке, то жмем Finish. Если все получилось, то конечная картинка должна быть такой:
А в списке публикаций появилась kontr_publication. Теперь нам нужно удалить ее и пересоздать – это нужно только для views. С таблицами это делать не нужно. Для этого удаляем только что созданную публикацию из MSSQL Management Studio\ Replication\Local Publications, нажав на kontr_publication Правой кнопкой мыши и выбрав Delete. Теперь открываем созданный нами ранее скрипт и изменяем в нем слова indexed view schema only на indexed view logbased. После чего выполняем скрипт. Он ругнется на то что пароли получились пустые. Пускай. Сейчас зайдем в папку Local Publication (нажав на ней F5), выберем вновь появившуюся kontr_publication, зайдем в properties и на закладке Agent Security нажмем на кнопку Create Agent. В появившемся окне вводим windows и sql логины и пароли для доступа к БД-распространителю:
Нажимаем ОК. Все, публикация готова!

7. Создаем подписку
Прежде чем создать подписку необходимо обеспечить доступ SQL Server’a на удаленный SQL Server. Для этого выполняем следующие действия: Заходим на сервере-издателе в папку C:\WINNT\system32\drivers\etc, открываем файл hosts посредством блокнота и добавляем в него IP-адрес сервера, к которому мы коннектимся.

Теперь нажимаем правой кнопкой мыши на только что созданной публикации и выбираем New Subscriptions. В первом окне выбираем созданную ранее публикацию kontr_publication. Во втором окне указываем, что агент распространителя будет запускаться на издателе: В следующем окне выбираем подписчика. Для этого нажимаем кнопку Add Subscriber\Add SQL Server Subscriber, В открывшемся окне Connect to Server в строке server name пишем либо имя сервера, куда будет реплицироваться информация либо его IP-адрес; выбираем SQL Server Authentication и указываем в качестве логина sa, а в качестве пароля – пароль sa в БД, куда будут реплицироваться данные, нажимаем «Connect». Теперь в окошке появилась строка с именем сервера (отмеченная галочкой) и в качестве БД, куда будет реплицироваться информация, указана trade:
Переходим к следующему окну. Здесь от нас хотят, чтобы мы ввели информацию, которая будет использоваться для доступа к БД-подписчику. Нажимаем кнопку:

Поскольку ранее мы указали, что агент будет запускаться на распространителе, то в качестве логина и пароля, под которым будет запускаться агент, указываем имя пользователя, обладающего правами dbo и его пароль в БД-распространителе, а в качестве пароля к подписчику выбираем sa и его пароль в БД-подписчике:
Нажав «ОК», мы увидим, что строка заполнилась:
Нажимаем «Next». В следующем окне мы указываем, что нам не нужно задать расписание для агента и мы предпочитаем, чтобы он запустился сразу же:
На последнем окне мастера, определяющим инициализацию подписок, лучше оставить настройки по умолчанию: подписчик у нас будет инициализироваться тут же, как мы нажмем кнопку «Finish»:
В последнем окне нам показывают все то, что мы ввели при работе мастера создания подписки, дабы мы убедились, что нигде нет ошибки. Жмем «Finish». После некоторого раздумья, SQL Server должен сообщить, что все хорошо:
Список публикаций изменится - в нем появится новая подписка:

Эта же подписка появится и во второй базе.
Сделав изменения в нашей БД в справочнике КА и нажав на публикацию правой кнопкой мыши – View Snapshot Agent Status мы увидим, что все работает:
Аналогичным образом создаем подписку на все перечисленные выше таблички (только без ее удаления и восстановления из скрипта).