Tuesday, August 2, 2016

Execute .NET Code under SQL Server


----------Create one database anmed TestingCLR-----------------------

--ALTER DATABASE TestingCLR SET TRUSTWORTHY ON
--GO


-----Start---Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

------using System;
------using System.Collections.Generic;
------using System.Text;
------using System.IO;
------using System.Data.SqlTypes;
------using System.Data.SqlClient;
------using Microsoft.SqlServer.Server;

------namespace ManagedCodeAndSQLServer
------{
------    public class BaseFunctionClass
------    {
------        #region "Default Constructor"
------        public BaseFunctionClass()
------        {

------        }
------        #endregion

------        #region "Welcome Function"
------        ///
------        /// This function will be called from the SQL Stored Procedure.
------        ///
------        /// Name
------        /// Welcome Message
------        [SqlProcedure]
------        public static void GetMessage(SqlString strName, out SqlString
------        strMessge)
------        {
------            strMessge = "Welcome," + strName + ", " + "your code is executed under CLR !";
------        }

------        #endregion
------    }
------}


-----End------Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

--sp_configure 'clr enable', 1
--GO
--RECONFIGURE
--GO

--CREATE ASSEMBLY ManagedCodeAndSQLServer
--AUTHORIZATION dbo
--FROM 'C:\Dev\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll'
--WITH PERMISSION_SET = UNSAFE
--GO

--CREATE ASSEMBLY Messaging
--AUTHORIZATION dbo
--FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
--WITH PERMISSION_SET = UNSAFE
--GO


--CREATE PROCEDURE usp_UseHelloDotNetAssembly
--@name nvarchar(200),
--@msg nvarchar(MAX)OUTPUT
--AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage
--GO



--DECLARE @msg varchar(MAX)
--EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output
--PRINT @msg



-----Ref : http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server

SQL Column level Encryption / Decryption


----------- Prerequisites to be done --------------------Start----------
--SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';


--CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';


--CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data';


--CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1;

----------- Prerequisites to be done --------------------End----------



OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;

Declare @Card_number as varchar(25)
set @Card_number = '1234'

select @Card_number OrginalVale, EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number) Encryptedvalue, CONVERT(varchar, DecryptByKey(EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number))) Decryptedvalue

CLOSE SYMMETRIC KEY SymmetricKey1;