script out tables with encrypted triggers
-
I am trying to run a test migration of our application from V8 to V9. To do this I am creating an empty database by running the Tasks -> Script database
Once that is done, I restore it to our dev server and run SQLCompare against it to generate the change scripts
The problem I am facing is that there are a few tables that have encrypted triggers on them and I cannot include them in the generate scripts. I want to take the table structure, including indexes, but I am not interested in the triggers. There are only 5 tables so I could script them by hand, but this means that I cannot automate the process.
As a result of the missing tables SQLCompare will try to generate a CREATE TABLE rather than an ALTER TABLE DDL
Does anyone have any ideas short of dropping the trigger.
The live migration won't be a problem because this will be a database restore and migration - my problem is the CREATE TABLE DDLs in the script.
-
aaron.reese (5/14/2015)
I am trying to run a test migration of our application from V8 to V9. To do this I am creating an empty database by running the Tasks -> Script databaseOnce that is done, I restore it to our dev server and run SQLCompare against it to generate the change scripts
The problem I am facing is that there are a few tables that have encrypted triggers on them and I cannot include them in the generate scripts. I want to take the table structure, including indexes, but I am not interested in the triggers. There are only 5 tables so I could script them by hand, but this means that I cannot automate the process.
As a result of the missing tables SQLCompare will try to generate a CREATE TABLE rather than an ALTER TABLE DDL
Does anyone have any ideas short of dropping the trigger.
The live migration won't be a problem because this will be a database restore and migration - my problem is the CREATE TABLE DDLs in the script.
You could restore a copy of the existing database and then truncate the tables to create an empty database.
-
Do you have other triggers? Can you filter these out? Maybe rename them to be a pattern that can be ignored?
-
if you connect via a Dedicated Admin Connection, i believe encrypted objects are visible as decrypted, specifically so you can debug and review.not quite true, now that i'm testing it.
it just makes it possible to get the a place where you can unencrypt them. not the same. i'll report when i can do it successfully myself without a third party tool.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ok, confirmation.
Step 1: i enabled the DAC in facets on my test server. no restart or anythign required.
Step 2: i created this procedure with encryption.
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: Preview top 100 rows of a given table
--additional modification: fast count of rows if a TABLE or #Temp (no results on views)
--#################################################################################################
CREATE PROCEDURE sp_show
--USAGE: sp_show gmact
@TblName VARCHAR(128),
@Top INT = 100
WITH ENCRYPTION
AS
BEGIN
DECLARE @cmd VARCHAR(MAX)
IF LEFT(@TblName,1) = '#'
BEGIN
SELECT
ps.row_count AS TotalRows
FROM tempdb.sys.indexes AS i
INNER JOIN tempdb.sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID('tempdb.dbo.' + RTRIM(@TblName)) ;
END
ELSE
BEGIN
SELECT
ps.row_count AS TotalRows
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID(RTRIM(@TblName)) ;
END
SET @cmd = 'SELECT TOP ' + CONVERT(VARCHAR,@Top) + ' * FROM ' + QUOTENAME(@TblName) + ' ORDER BY 1 DESC '
EXEC(@cmd)
END
GO
Step 3: I RDP'd to my test server, and conectted via SSMS to the DAC with ADMIN:ServerName and windows crednetials, since i'm a sysadmin on that instance
Step 4:I created the procedure for decrypting that i copied form this web site:
http://sqlity.net/en/1617/decrypting-encrypted-database-objects/
CREATE PROCEDURE dbo.ObjectEncryptionCracker
@object_name NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN
DECLARE @secret VARBINARY(MAX);
DECLARE @known_encrypted VARBINARY(MAX);
DECLARE @known_plain VARBINARY(MAX);
DECLARE @object_type NVARCHAR(MAX);
SELECT @secret = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID(@object_name);
DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = CASE type_desc
WHEN 'SQL_SCALAR_FUNCTION'
THEN 'ALTER FUNCTION ' + @object_name + '()RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 0;END;'
WHEN 'SQL_TABLE_VALUED_FUNCTION'
THEN 'ALTER FUNCTION ' + @object_name + '()RETURNS @r TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END;'
WHEN 'SQL_INLINE_TABLE_VALUED_FUNCTION'
THEN 'ALTER FUNCTION ' + @object_name + '()RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 0 i;'
WHEN 'SQL_STORED_PROCEDURE'
THEN 'ALTER PROCEDURE ' + @object_name + ' WITH ENCRYPTION AS RETURN 0;'
END
FROM sys.objects
WHERE object_id = OBJECT_ID(@object_name);
SELECT @cmd = REPLICATE(CAST(CHAR(32) AS NVARCHAR(MAX)), DATALENGTH(@secret)) + @cmd;
SELECT @known_plain = CAST(@cmd AS VARBINARY(MAX));
BEGIN TRAN;
EXEC(@cmd);
SELECT @known_encrypted = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID(@object_name);
ROLLBACK;
DECLARE @i INT = 0;
DECLARE @plain VARBINARY(MAX) = 0x;
WHILE @i < DATALENGTH(@secret)
BEGIN
SET @plain = @plain
+ CAST(REVERSE(CAST(CAST(SUBSTRING(@secret, @i, 2) AS SMALLINT)
^ CAST(SUBSTRING(@known_plain, @i, 2) AS SMALLINT)
^ CAST(SUBSTRING(@known_encrypted, @i, 2) AS SMALLINT) AS BINARY(2))) AS BINARY(2));
SET @i += 2;
END
SET @cmd = N'SELECT (SELECT ''--''+CHAR(13)+CHAR(10)+''GO''+CHAR(13)+CHAR(10)+'
+ N'CAST(@plain AS NVARCHAR(MAX))+CHAR(13)+CHAR(10)+''GO''+CHAR(13)+CHAR(10)+''--'''
+ N' AS [processing-instruction(sql)] FOR XML PATH(''''),TYPE) AS [object_definition for '
+ REPLACE(@object_name, ']', ']]') + ']';
EXEC sp_executesql @cmd, N'@plain VARBINARY(MAX)', @plain;
END;
for each object i know was encrypted, i could run this command as an example:
EXEC ObjectEncryptionCracker sp_show
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Do you mean Tasks...Generate Scripts?
If so, (and even if not), you can tell that not to script triggers in the Advanced Scripting Options.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);- Forum Etiquette: How to post Reporting Services problems
[/url]- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]- How to Post Performance Problems - by Gail Shaw
[/url] -
SQL Compare can quite happily generate deployment scripts for encrypted triggers (with the full trigger content) if you turn on the "Decrypt encrypted objects" option.
If you want it to ignore the triggers rather than deploying them, you can instead turn on the "Ignore DML triggers" option.
Does that help?
Mike
--
Mike Upton
Project Manager - SQL Compare | SQL Data Compare
Redgate
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply