Database Encryption

Infinity is able to provide a mechanism that will allow data to be stored in the database in a manner that assists with PCI Compliance. This mechanism will enable data to be loaded and retrieved to the database and automatically encrypted and decrypted.

The database encryption makes use of the inbuilt encryption routines within SQL Server and enlists triggers to perform the data encryption when data is inserted in, or updated on, a table. A stored procedure is used to retrieve the decrypted data. Infinity does not provide a view, or select a trigger that performs a bulk decryption of the data, as per PCI guidelines.

Database encryption relies on the database being configured, and it should be performed by the DBA or a user with full SQL Super Administrator rights on the database server.

Setup

To setup the database for encryption you will need to follow these steps:

Create a master key for the database by running the following SQL. You will need to replace the <password> tag with your own password.

IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'

Warning: It is very important that this password is recorded as if you need to move the database to a different server you will not be able to access any of the encrypted data without it.

Create a certificate that will be used to generate a symmetric key that will be used to perform the data encryption. You can either generate the certificate on the SQL server or import one from your own certificate store. To create the certificate on the server run the following replacing the <yyyymmdd> tag with today’s date:

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'InfinityEncCert')

CREATE CERTIFICATE InfinityEncCert WITH SUBJECT = 'Infinity Encryption', Start_Date = '<yyyymmdd>'

Create the symmetric key by running the following. If using your own certificate replace the name “InfinityEncCert” with the name of your certificate:

IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = 'InfinityEncSymKey') CREATE SYMMETRIC KEY InfinityEncSymKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE InfinityEncCert

Update the Infinity database, so that it is aware encryption is enabled, by running the following SQL.

Note: If using your own certificate replace the name “InfinityEncCert” with the name of your certificate.

 

IF NOT EXISTS(SELECT * FROM pConfig WHERE conf_Item='USESQLENCRYPTION') BEGIN

INSERT INTO pConfig(conf_Team, conf_Group,

conf_Item, conf_Value1)

VALUES (0, 'ENCRYPTION', 'USESQLENCRYPTION', 'Y')

END ELSE BEGIN

UPDATE pConfig SET conf_Value1='Y' WHERE conf_Item='USESQLENCRYPTION'

END

IF NOT EXISTS(SELECT * FROM pConfig WHERE conf_Item='USESQLCERTIFICATE') BEGIN

INSERT INTO pConfig(conf_Team, conf_Group,

conf_Item, conf_Value1)

VALUES (0, 'ENCRYPTION', 'USESQLCERTIFICATE',

dbo.UDF_Encrypt('InfinityEncCert'))

END ELSE BEGIN

UPDATE pConfig SET conf_Value1 = dbo.UDF_Encrypt('InfinityEncCert')

WHERE conf_Item='USESQLCERTIFICATE'

END

Log into Infinity Console and locate the Project, then select Database -> Verify. Once the verify tab has loaded select the “Encryption.db.xml” file from the list on the left hand side and click “Check”. This will highlight a number of amendments along with new tables and procedures that need to be applied to the database. To apply these click the “Repair” option.

Encrypting Data

Once the database has had encryption enabled, end users will be able to encrypt database columns via the Tables screen. When the user adds or edits a database column the Encrypt Column option will now be enabled in the column's Parameters.