April 17, 2015 at 6:26 am
Database Admins, can you please help me out and let me know why my script is not allowing me to DECRYPTBYKEY once I restore my DB from PRODUCTION BACKUP....
-- SET Staging to Single User Mode to be able to RESTORE DB---
-- STEP 1 (Works No Problems Here)
USE master;
GO
ALTER DATABASE Staging
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE Staging
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MyDBRestore.bak' ;
GO
-- STEP 2 - USE ONLY IF THE ABOVE IS UNSUCCESSFUL ||| FAILURE ****** RESTORE RUN THE FOLLOWING SCRIPT -----
--If the above is successful the DB sets itself back to MULTI_USER
--ALTER DATABASE Staging
--SET MULTI_USER;
--GO
--ALTER DATABASE Staging
--SET READ_WRITE
--GO
-- STEP 3 (Works No Problems Here)
--------------- @@@@@@@@@@@@@@ IMPORTANT UNCOMMIT AND RUN @@@@@@@@@@@@@@@@@@@@@@@@@ MANUAL STEPS ----------------------
-- RBD - Recreate Security ID'S AND PERMISSIONS FOR Stored Procedure EXECUTE RIGHTS, because PASSWORDS are different on lower
-- environments
--USE [Staging]
--GO
--/****** Object: User [WebUser] Script Date: 4/13/2015 11:15:51 AM ******/
--DROP USER [WebUser]
--GO
--/****** Object: User [WebUser] Script Date: 4/13/2015 11:15:51 AM ******/
--CREATE USER [IUser] FOR LOGIN [IUser] WITH DEFAULT_SCHEMA=[dbo]
--GO
--USE [Staging]
--GO
--/****** Object: User [Management] Script Date: 4/13/2015 11:16:37 AM ******/
--DROP USER [Management]
--GO
--/****** Object: User [Management] Script Date: 4/13/2015 11:16:37 AM ******/
--CREATE USER [Management] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
--GO
--USE [Staging]
--GO
--/****** Object: User [Transfer_Admin] Script Date: 4/13/2015 11:19:42 AM ******/
--DROP USER [Transfer_Admin]
--GO
--/****** Object: User [Transfer_Admin] Script Date: 4/13/2015 11:19:42 AM ******/
--CREATE USER [Transfer_Admin] FOR LOGIN [Transfer_Admin] WITH DEFAULT_SCHEMA=[dbo]
--GO
--Grants Execute Rights to Staging
--USE [Staging]
--GO
--GRANT EXECUTE ON SCHEMA::[dbo] TO [WebUser]
--GO
--USE [Staging]
--GO
--GRANT EXECUTE ON SCHEMA::[dbo] TO [Transfer_Admin]
--GO
--------------- @@@@@@@@@@@@@@ IMPORTANT RESET KEYS BECAUSE YOU HAVE MOVED TO A NEW SERVER @@@@@@@@@@@@@@@@@@@@@@@@@ ----------------------
-- Apply Scripts for SECURITY ONLY AFTER SUCCESSESFUL RESTORE, I REPEAT ONLY AFTER SUCCESSFUL RESTORE.
--@@@@@@@@@@@@@@@@@MIGHT NOT HAVE TO RUN THESE SCRIPTS @@@@@@@@@@@@@@@@@@@@@@@@@@
USE [Staging]
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\!IMPORTANT KEYS\SQL Encryption\SQLServiceMasterKey'
DECRYPTION BY PASSWORD = 'DEMO_PASSWORD_SERVICE_MASTER' --[FORCE] --- Currently not using force because the script will run
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DEMO_PASSWORD_MASTER'
--ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
USE [Staging]
IF (select Count(*) from sys.symmetric_keys where name like '%SKey%') > 0
BEGIN
DROP SYMMETRIC KEY SKey
END
IF (select Count(certificate_id) from sys.certificates where name = 'MCer') > 0
BEGIN
DROP CERTIFICATE MCer
END
DROP MASTER KEY;
Create Master Key Encryption by Password='DEMO_PASSWORD_MASTER'
create certificate MCer
from file = N'C:\!IMPORTANT KEYS\SQL Encryption\SQLCert'
with private key
( file = N'C:\!IMPORTANT KEYS\SQL Encryption\SQLCertPrivateKey'
, decryption by password = N'DEMO_PASSWORD_MCER'
);
Create Symmetric Key SKey With Algorithm=AES_256 encryption by Certificate MCer
--GRANT RIGHTS TO WebUser for Certificates Permissions
USE Staging;
--GRANT RIGHTS TO WebUser for Symmetric Key Permissions
GRANT CONTROL ON CERTIFICATE::MCer TO WebUser;
GO
GRANT CONTROL ON SYMMETRIC KEY:: SKey TO [WebUser]
GO
-- Uncomment for testing to ensure the encryption was applied correctly
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--!!!!!!!!!!!!!!!!! THIS DOES NOT WORK AFTER THE DB RESTORE AND RUNNING ALL THESE SCRIPTS!!!!!!!!!!!!!!!!!!!
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
OPEN SYMMETRIC KEY SKey DECRYPTION BY CERTIFICATE MCer
SELECT CONVERT([varchar](MAX), DECRYPTBYKEY(PasswordEncrypt)) AS 'UnEncryptedPWD' FROM Users WHERE [User_ID] = 1
CLOSE SYMMETRIC KEY SKey
OPEN SYMMETRIC KEY SKey DECRYPTION BY CERTIFICATE MCer
SELECT CONVERT([varchar](MAX), DECRYPTBYKEY(PasswordEncrypt)) AS 'UnEncryptedPWD' FROM ACCOUNTS WHERE [AccountID] = 1
CLOSE SYMMETRIC KEY SKey
April 17, 2015 at 9:18 am
UPDATE ON THIS RESTORE ISSUE --- HOWEVER, I think by getting the SQL SERVER Service Key and Master Key correct the restore worked correctly as well. I will need to test this theory and will return with results. Bust as gurus know, there is work to be done.
I had an old DB GURU friend provide a script and instead of all this code all I had to run to restore the DB was as follows, but I still think I had to have the KEYS CORRECT. ALL IS WORKING ----
-- SET Staging to Single User Mode to be able to RESTORE DB---
-- STEP 1 (Works No Problems Here)
USE master;
GO
ALTER DATABASE Staging
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE Staging
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MyDBRestore.bak' ;
GO
DB GURU CODE THAT REALIGNS IDs
DECLARE
@username varchar(25)
DECLARE fixusers CURSOR FAST_FORWARD FOR
SELECT UserName = [name] FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)
and suser_sname(sid) IS NULL
ORDER BY [name]
OPEN fixusers
FETCH FROM fixusers INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC sp_change_users_login 'update_one', @username, @username
END TRY
BEGIN CATCH
PRINT 'User ' + @username + ' is a schema, windows login, or a SQL login that does not exist at the instance level. Moving on to next user.'
END CATCH
FETCH NEXT FROM fixusers INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
PRINT 'Remapping complete.'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply