Asymmetric Encryption between Replication Partners

• BReuter

  Ten Centuries

  Points: 1325

  More actions

  July 7, 2005 at 10:53 am

  #88071

  Started out posting a question, but found a solution so I'd thought I'd share this example of using SQL 2005 built-in functions to manage certificates and encrypt data.

  I have an environment with many merge replication subscribers which I cannot protect, and a merge replication publisher which is well protected.  The appliction requires that sensitive data (credit card numbers) be recorded on the subscriber, but only viewed from the publisher.

  To solve the problem, I will create a certificate on the publisher (public/private key pair) and distribute the Public Key only to the subscribers.  The example below shows this between two databases on the same server.

   

  --BEGIN SCRIPT

  --create databases to show example

  --CREATE DATABASE PKI_PRIVATE

  --CREATE DATABASE PKI_PUBLIC

  --=============Publisher===========================

  --create a cert with password protected private key

  --end user should manage password to prevent sysadmin access to the data.

  USE PKI_PRIVATE

    CREATE CERTIFICATE CC_PRIVATE AUTHORIZATION dbo

   ENCRYPTION BY PASSWORD = 'somepwd' 

   WITH SUBJECT = 'Cert used to encrypte CC data',

   EXPIRY_DATE = '1/1/2100'

  --backup public key only

    BACKUP CERTIFICATE CC_PRIVATE

   TO FILE = 'c:\CC_Public.DER'

  --=============Subscriber===========================

  --inport public key portion of the cert

  USE PKI_PUBLIC

    CREATE CERTIFICATE CC_PUBLIC AUTHORIZATION dbo

   FROM FILE = 'c:\CC_Public.DER'

   

  --=============Example of Encrypt/Decrypt function calls======================

  --Use cert with Public Key only "CC_PUBLIC" in "PKI_PUBLIC" dataabase to encrypt

  --Use cert with Private Key only to

  declare @encrypted varbinary(512)

  --Subscriber Encypt

  use PKI_PUBLIC

    select @encrypted = EncryptByCert ( Cert_ID('CC_PUBLIC')  , N'MY CC # HERE')

    select name, pvt_key_encryption_type_desc from sys.certificates

    select @encrypted

  --Publisher Decrypt

  use PKI_PRIVATE   

    select name, pvt_key_encryption_type_desc from sys.certificates

    select cast (DecryptByCert ( Cert_ID('CC_PRIVATE')  , @encrypted, N'somepwd') as nvarchar(max))

  --END SCRIPT

• July 11, 2005 at 8:00 am

  This was removed by the editor as SPAM

• semanticxq

  SSC Rookie

  Points: 43

  More actions

  May 29, 2006 at 1:50 pm

  #640578

  My w2003 server is not "on-line" but only broadband to

  Comcast and it's certificates are expired; but PGP may

  be useful with this encryption see

  PACE - semantic interpretation [new technology]

  CREATE commands are insufficient on SqlServer 2005

  Msg 911, Level 16, State 1, Line 12

  Could not locate entry in sysdatabases for database 'PKI_PRIVATE'. No entry found with that name. Make sure that the name is entered correctly.

  Manually created the Databases - parsed

  Command(s) completed successfully.

  Execution Successful - output recorded below;

  CC_PUBLIC NO_PRIVATE_KEY

  0xB2ECAF1D17DA3643DE9F37C874899670C08269466D4DEF4EA8C97A1BBFA4FE57EF9FCB30A72B11AC641706CEE2D01BF9D936230417BE36DFE922DCD6C5E070D5431F34BFD624ABFE15C57C8EF0A229E607D7194AB5FA8FE54DC6C32C8809AEF703BEED9534344B337ED7B3DB2960674FFB8420FBDFDACA92193E702EEB9FA33C

  CC_PRIVATE ENCRYPTED_BY_PASSWORD

  MY CC # HERE

  ---------------------------------

  truly keith scharding - echelonxq@comcast.net

  http://home.comcast.net/~echelonxq/SemXQ

   

   

  
  integral-solutions ltd.

• semanticxq

  SSC Rookie

  Points: 43

  More actions

  May 29, 2006 at 2:26 pm

  #640580

  Free - http://www.red-gate.com/Dynamic/Downloads/DownloadInstaller.aspx?DownloadID=257046&Password=MOXGATXQCR

  the script provides an execution plan but the tuning wizard warns

  that there is no "user" table for it to correspond with.

  you also need some type of "built-in function" like ENCRYPT for this

  to utilize dcom and remoting applications - sql cli clr assemblies could

  prove much more efficient - also if there are no user tables in the

  database then it must correspond by cross referencing other

  databases and their tables [which of course is optimal also].

  Here is a link to a VB script that "stores user passwords".

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=130701

  I am using csharp but this script can be converted - no doubt.

  Here was my starting point - I am working with a Kerberos web service - authx;

  which I found while updating my server to R2 - from microsoft - it has no SQLServer

  functionality - in other words it "instantiates" but has no functional application.

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=119&messageid=233204

  happy   memorial day  - a Net beta one soldier

   

  
  integral-solutions ltd.