EKM module for KMIP servers
How to make PyKMIP (and other KMIP servers) to work for SQL Server encryption such as EncryptByKey, EncryptByAsymKey and TDE operations?
Activecrypt offers free solution: EKM module for a KMIP server.
Free KMIP server PyKMIP 0.10 can be used for SQL Server key operations with slight modifications. Click here to see modifications.
EKM feature is not available on every edition of SQL Server. Alternatively, Activecrypt offers DbDefence - database encryption solution that works on all editions of SQL Server. It includes data-at-rest encryption, backup encryption, masking, protection from Profiler.
Installation
1. Download and extract the DLL to the server.2. Use CREATE CRYPTOGRAPHIC PROVIDER and specify path to the DLL:
sp_configure
'show advanced options',
1 ;
GO
RECONFIGURE ;
GO
sp_configure
'EKM provider enabled',
1 ;
GO
RECONFIGURE ;
GO
CREATE
CRYPTOGRAPHIC PROVIDER
ACKMIPEKM FROM
FILE
=
'c:\path\to_the_dll\ac_kmip_ekm.dll'
GO
3. The path must be accessible for the SQL Server service account.
Configuration
1. Add configuration function (once):
use
exec
sp_addextendedproc
'ac_kmip_ekm_cfg','c:\path\to_the_dll\ac_kmip_ekm.dll'
2.
Call the function with the host, port and root certificate. Example:
exec
ac_kmip_ekm_cfg
@host='192.168.1.13',@port=5696,
@rootcert='-----BEGIN
CERTIFICATE-----
MIIC7zCCAdegAwIBAgIUN1orsCcCzKXhCelid/VrW/yAf30wDQYJKoZIhvcNAQEL
BQAwJzETMBEGA1UECgwKVGVzdCwgSW5jLjEQMA4GA1UEAwwHUm9vdCBDQTAeFw0y
MjA4MDQwNjA5MjRaFw0yMzA4MDQwNjA5MjRaMCcxEzARBgNVBAoMClRlc3QsIElu
Yy4xEDAOBgNVBAMMB1Jvb3QgQ0EwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEK
AoIBAQDjr/ZJcEyVdcdm9Of1OPA+5XVM8RoNG9KzOdlDixKaGyWt7BvdMeUiVG1u
+XdQ7InXEsHMmF9lazvDhIBR9XbFdXo75F/C6KW6x/UjFD/OgYSQS7VswtQXtsjB
FXxy6MoulGVQcnMi+weWinDsysOD+Eb9TnLG1qEkZ7JrniXbHqqTVbN2UUL9tDxf
Wi2BLqK15LKfxDK59IRI0oiLtntBNagNk0gXeqz0yJ/s+mPFxpdUQWG/m3KH0dOM
-----END CERTIFICATE-----'
3. Credential configuration:
PyKMIP and other KMIP servers are setup to accept user certificates for authorization.
Here is how to setup user's private key and certificate. Replace the key and certificate on your own. Replace the name of credential from sysadmin123 to your own.
declare @nv
nvarchar(4000)
declare @v varchar(8000)
select @v=
'-----BEGIN
PRIVATE KEY-----
MIIEwAIBADANBgkqhkiG9w0BAQEFAASCBKowggSmAgEAAoIBAQC4hjjadDwgqJC7
extMvSqIaBiZ7umMw/FHS4qqgyD5bzpS8ZViBbXDe12XzwafUHW2cxJoaIg9JamL
k6Vd+UX4Boq7cJEFfugOFiBmxIqxzFBSjujgPADmM69IiO3nzrT+HMLLmraIkGki
YogzHwDWiVd7oe2nxrEPu5QjIGTvCpi2m1RCAerSp2U2bnY5E+yG/e4NKE2368JX
L68q3CJh54RQvmOR0X2XQFywQBc99fWXxH47iHcfwLesBm1mTABx+JzYouPcAx88
i3lNM3NCQcUBHFHWkkSmS3PVfju5+j95V1413VjySNkgUhJobOibpukMRTGYs630
XSII2Su3AgMBAAECggEBAJUCNskaUi/hT7dGS55lqAItZahxCQLWfHIha6IUcD1d
d9DhK8vTZpusdMERunxOojAqrrPHrjUKw+Zl3YWMtdb7mW6SEVpJyCUP8yxqDMJV
-----END PRIVATE KEY-----
-----BEGIN CERTIFICATE-----
MIIC9zCCAd+gAwIBAgIUC++oeYc9A2JS/V8Zn5CsqJl9d2swDQYJKoZIhvcNAQEL
BQAwJzETMBEGA1UECgwKVGVzdCwgSW5jLjEQMA4GA1UEAwwHUm9vdCBDQTAeFw0y
MjA4MDQwNjA5MjRaFw0yMzA4MDQwNjA5MjRaMCgxEzARBgNVBAoMClRlc3QsIElu
Yy4xETAPBgNVBAMMCEpvaG4gRG9lMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIB
CgKCAQEAuIY42nQ8IKiQu3sbTL0qiGgYme7pjMPxR0uKqoMg+W86UvGVYgW1w3td
l88Gn1B1tnMSaGiIPSWpi5OlXflF+AaKu3CRBX7oDhYgZsSKscxQUo7o4DwA5jOv
SIjt5860/hzCy5q2iJBpImKIMx8A1olXe6Htp8axD7uUIyBk7wqYtptUQgHq0qdl
Nm52ORPshv3uDShNt+vCVy+vKtwiYeeEUL5jkdF9l0BcsEAXPfX1l8R+O4h3H8C3
-----END CERTIFICATE-----'
set @nv
=
CAST
(@v
as
varbinary(4000))
declare @q
nvarchar(max)
set @q
=
N'CREATE CREDENTIAL sysadmin123
WITH IDENTITY = ''*'',
SECRET = '''+@nv+N'''
'
EXECUTE
sp_executesql
4. That's all. You can start encryption or TDE setup.
Questions? Contact us: support@activecrypt.com