I need to download an x509 Certificate from Azure Key Vault and import it in SQL Server to use for TDE. *Note: I'm not referring to EKM providers/ASYMMETRIC KEYs, where SQL accesses key vault to pull the key when needed.* My use case is simple: grab a cert from key vault and load it into SQL server. Due to security requirements, it's not possible to save the certificate to a file where SQL Server can access it. I can download the certificate successfully and convert it to a X509Certificate2 object, but when I run the CREATE CERTIFICATE statement in SQL Server, it completes successfully but doesn't include the private key. I believe I need to specify a PRIVATE KEY (BINARY = private_key_bits ) clause, but I can't figure out how to extract the private key from the X509Certificate2 object in a format that SQL Server will accept. This powershell script runs w/o errors, but doesn't include the private key:

$keyVault = "my-key-vault"
 $CertName = "tde-certificate"

 $SQLInstance = ".\SQLEXPRESS"
 $SQLCertName = "KeyVault_TDECert"


# download the KeyVaultSecret for the cert as base64 string
$certBase64 = Get-AzKeyVaultSecret -VaultName $keyVault -Name $CertName -AsPlainText

# Convert to a [System.Security.Cryptography.X509Certificates.X509Certificate2] object
$certBytes = [convert]::FromBase64String($certBase64)
$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null)  # byte[], $null for password

# CREATE CERTIFICATE in SQL Server.
# $cert.GetRawCertDataString() returns hex encoded data, which SQL Server gladly accepts.

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$($cert.GetRawCertDataString()) "

Invoke-sqlcmd -ServerInstance $SQLInstance -Database master -Query $sql

$cert.HasPrivateKey returns $true. $cert.PrivateKey.key.ExportPolicy equals None which should mean unrestricted.

$cert.PrivateKey.key | Select-Object *

Algorithm          : RSA
AlgorithmGroup     : RSA
ExportPolicy       : None
Handle             : Microsoft.Win32.SafeHandles.SafeNCryptKeyHandle
IsEphemeral        : False
IsMachineKey       : False
KeyName            : 
KeySize            : 3072
KeyUsage           : AllUsages
ParentWindowHandle : 0
Provider           : Microsoft Enhanced RSA and AES Cryptographic Provider
ProviderHandle     : Microsoft.Win32.SafeHandles.SafeNCryptProviderHandle
UIPolicy           : System.Security.Cryptography.CngUIPolicy
UniqueName         :

How can I download a certificate from Key Vault with the private key and load it into SQL Server? ---- Update 1: ============ Changed to .Export("pfx"); need to add "Exportable" to constructor to make private key exportable.

$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null)  # byte[], $null for password

# CREATE CERTIFICATE in SQL Server.
# $cert.GetRawCertDataString() returns hex encoded data, which SQL Server gladly accepts.

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$($cert.GetRawCertDataString()) "

to

$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null, "Exportable")  # byte[], $null for password,

# CREATE CERTIFICATE in SQL Server.
# $cert.GetRawCertDataString() returns hex encoded data, which SQL Server gladly accepts.

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$([Convert]::ToHexString($cert.Export(Pfx))) "

Now I receive a SQL error:

Msg 15468, Level 16, State 6, Line 5
An error occurred during the generation of the certificate.

Google turns up an old MS blog post where this occurred due to pfx not being a supported certificate format (affect SQL 2014). I'm testing on SQL 2022 (but would like support for 2016+)

In PostgreSQL, using the pgcrypto module, I can generate a **slow** hash with blowfish (or any supported algorithm) like this:

crypt('SomeTextHere', gen_salt('bf'));

However, it auto-generates a salt (with gen_salt) and requires it. I don't want to use any salt. I am also not interested in using a normal hashing function (like sha256). How can I generate a **slow** hash without a salt?

Hope you're having a good day. This is in continuation to [my previously posted question in Salesforce StackExchange](https://salesforce.stackexchange.com/questions/388321/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via) . I was suggested to seek help at **DBA SE** in order to find a way to extract Symmetric key from MS SQL in order to use it for decrypting the data in Salesforce Apex using its Crypto class. Or otherwise, an alternative. --- Recently, I have been working on a project and haven't been able to find a solution to this problem. What I want to achieve is: To decrypt the data in Apex using [the crypto class](https://developer.salesforce.com/docs/atlas.en-us.apexref.meta/apexref/apex_classes_restful_crypto.htm#apex_System_Crypto_encrypt) . The data is encrypted using the AES_256 algorithm in MS SQL Server. Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-transact-sql?view=sql-server-ver16 MS SQL server has its own functionality to encrypt/decrypt data using symmetric/asymmetric keys. The problem is, unlike Apex or any other language, we cannot view the generated AES key. The server stores the key such that it is self-encrypted and can only be accessed within the database by its name. This is how the data is encrypted in MS SQL: Can be tested at [MS SQL Online IDE](https://sqliteonline.com/)

--Creating a table in db to store data. It has a column to store encrypted password as well.
--
CREATE TABLE encryption_test ( name VARCHAR(20), email VARCHAR(40), password VARCHAR(45), password_encrypted VARBINARY(MAX))


-- Create a DB Master key as pre requisit for symmetric key creation.
--
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncPass@123#';


-- Create a self-signed cert
--
CREATE CERTIFICATE EncryptCert1 WITH SUBJECT = 'EncryptCert1';


-- Create a symmteric key with AES_256 with self-signed cert
--
CREATE SYMMETRIC KEY EncryptKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE EncryptCert1;


-- Create a UDF to easily encrypt/decrypt any data

--Encryption function
--
CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max)  
    SET @Result = EncryptByKey(Key_GUID('EncryptKey1'), @ValueToEncrypt)  
    -- Return the result of the function  
    RETURN @Result  
END


-- Decryption function
--
CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max)  
    SET @Result = DecryptByKey(@ValueToDecrypt)  
    -- Return the result of the function  
    RETURN @Result  
END


-- Create a server stored procedure to easily access encryption/decryption key.
--
CREATE PROCEDURE sp_OpenEncryptionKeys  
AS  
BEGIN  
    SET NOCOUNT ON;  

    BEGIN TRY  
        OPEN SYMMETRIC KEY EncryptKey1  
        DECRYPTION BY CERTIFICATE EncryptCert1 
    END TRY  
    BEGIN CATCH  
        --catch
    END CATCH  
END

-- Insert some data in table
--
EXEC sp_OpenEncryptionKeys  --run procedure to access key.
INSERT INTO encryption_test VALUES ( 'MyName', 'MyName@gmail.com', 'MyPass123', dbo.Encrypt('MyPass123') );  --dbo.Encrypt encryps the data in BLOB

-- Finally, access the encrypted data.
--
EXEC sp_OpenEncryptionKeys
SELECT name, email, password, password_encrypted, pass_base64
FROM encryption_test
cross apply (select password_encrypted '*' for xml path('')) T (pass_base64);

--OUTPUT DATA
----------------------------------------------------------------
--| name: MyName 
--| email: MyName@gmail.com
--| password: MyPass123
--| password_encrypted: -0,114,0,42,94,23,84,68,157,243,45,3,148,238,239,41,2,0,0,0,254,67,113,151,205,120,22,129,189,211,250,94,72,151,11,120,215,250,241,70,193,107,75,191,219,153,101,6,228,84,203,130,84,151,13,71,146,95,234,10,233,6,77,132,176,46,52,240
--| pass_base64: AHIAKl4XVESd8y0DlO7vKQIAAAD+Q3GXzXgWgb3T+l5Ilwt41/rxRsFrS7/bmWUG5FTLglSXDUeSX+oK6QZNhLAuNPA=
----------------------------------------------------------------

Now the data is encrypted in MS SQL server and exported in bas64 format in a CSV. Following suggestion from [identigral](https://salesforce.stackexchange.com/users/4142/identigral) in my [previous question](https://salesforce.stackexchange.com/questions/388321/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via) , I manipulate the output data by cutting out all the junk to arrive at IV + encrypted data.

GUID: 0072002a5e1754449df32d0394eeef29

VER: 02000000

IV: fe437197cd781681bdd3fa5e48970b78
IV_b64: /kNxl814FoG90/peSJcLeA==

Header: d7faf146c16b4bbf

Data: db996506e454cb8254970d47925fea0ae9064d84b02e34f0
Data_b64: 25llBuRUy4JUlw1Hkl/qCukGTYSwLjTw

However, still missing out the Symmetric key to be used for decryption inside Salesforce Apex.

Blob key = Blob.valueOf('???????????????????');  //no such way to export key from T-SQL
        Blob encrypted = EncodingUtil.base64Decode('25llBuRUy4JUlw1Hkl/qCukGTYSwLjTw');

        Blob decrypted = Crypto.decryptWithManagedIV('AES256', key, encrypted);
        
        String decryptedString = decrypted.toString(); 
        System.debug('decrypted String: '+decryptedString);

I gave it a try by creating a symmetric key encrypted by password to use the password as the **key**. But, it then returns **error: *last block incomplete in decryption*.**

-- Create a symmetric key with AES_256 with a password.
--
CREATE SYMMETRIC KEY EncryptKey1   
WITH ALGORITHM = AES_256  
ENCRYPTION BY PASSWORD = '12345qwertyu@!#$%asdEWQAS#$r4cfr';  -- 32 bytes, to be used as the key.

After encoding and manipulating the same password, this is the result:

PASSWORDED

GUID: 005d15df67da1d4199bb777f779acb26

VER: 02000000

IV: b6175270ad8b7a259531206018591c4f
IV_b64: thdScK2LeiWVMSBgGFkcTw==

Header: a0a1bb88411dea02


Data: 19b397e8335e6f5934b31814ce62645395c2e76408667160
Data_b64: GbOX6DNeb1k0sxgUzmJkU5XC52QIZnFg

Using the password as **KEY** in Apex Class. Returns above-mentioned error.

Blob key = Blob.valueOf('12345qwertyu@!#$%asdEWQAS#$r4cfr');  //using password as the key.
        Blob iv = EncodingUtil.base64Decode('thdScK2LeiWVMSBgGFkcTw==');
        Blob encrypted = EncodingUtil.base64Decode('GbOX6DNeb1k0sxgUzmJkU5XC52QIZnFg');

        Blob decrypted = Crypto.decrypt('AES256', key, iv, encrypted);
        
        String decryptedString = decrypted.toString(); 
        System.debug('decrypted String: '+decryptedString);

Therefore, need a solution in some way to either export the key string from MS SQL. Or otherwise an alternate to decrypt the T-SQL encrypted data in Apex Class.

I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense. I need to generate multiple such identifiers as part of an INSERT...SELECT statement. For architectural reasons I want to generate the identifiers server-side in this case. How can I generate a securely random UNIQUEIDENTIFIER? Note, that NEWID would not be random enough as it does not promise any security properties at all. I'm looking for the SQL Server equivalent of System.Security.Cryptography.RandomNumberGenerator because I need unguessable IDs. Anything based on CHECKSUM, RAND or GETUTCDATE would also not qualify.

I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried.

-- Create a self-signed certificate
CREATE CERTIFICATE MyCert WITH SUBJECT = 'Integrations';

-- Output the public certificate
SELECT [der_hex] = CERTENCODED(CERT_ID('MyCert'));

Using CyberChef, I can convert this binary output into a PEM format and also verify that it parses as a X.509 certificate with an RSA public key: CyberChef example . After writing this PEM-format certificate to a file, then using openssl I can extract this RSA Public Key in PEM format:

% openssl x509 -inform pem -in cert.pem -pubkey -noout
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvHYJf/jNWetCyySQBWVO
S9M8maFmMQXOZDG2eXHsw92rCjhNbtFsWBi077rQrN/zAXuiiWC1oFAwHMOrMleI
jZCaghqdBr9HI5IbCe1eZvucg3K+AfLx3iZ9zr//HSh+0bKVScWW9POW19F2F8z/
uIWnHDOXEDO0BrZ7W9thl0WRaY+dG8jPtQSxL8fVhcho1g6fF3vmD1zpiIHmAiZI
h0np0rdYQ/pj8aK7jRLgPlsz4JqQ5JZBhtLmxK5Vz6OZ6+ocqfltmuL0f5wGbd8M
e2qmXcBB6DFJm4xs+Ey7FqSjAMFiMBNYWt2NbEeAKHxBjXOjM0vbtGaVyT8uaSJW
JQIDAQAB
-----END PUBLIC KEY-----

Now, I can have SQL Server sign some data:

SELECT [signature] = SIGNBYCERT(CERT_ID('MyCert'), 'hello');

And now, again using CyberChef, I want to verify the signature against the same plaintext using the public key: CyberChef example . Unfortunately, this signature does not verify and I'm unable to figure out where I'm going wrong. [The documentation for SIGNBYCERT()](https://learn.microsoft.com/en-us/sql/t-sql/functions/signbycert-transact-sql?view=sql-server-2016) is not exactly tremendous, in that it is not specific about any particular standards or formats that are used to generate the signature. I am assuming that it is an RSA signature by virtue of the metadata in the certificate used to create the signature. But I recognize that this could be an issue of text encoding; I'm sure the text I'm verifying in CyberChef is UTF-8, and nowhere in the SQL code have I declared what text encoding to use when generating the signature -- and indeed, if I pass in N'hello' instead of 'hello', the signature output changes. However, this seems to be a moot point since when attempting to verify a signature in CyberChef, the error I receive is Error: Encryption block is invalid. This would indicate an issue with the signature format, not with the data simply not validating -- valid or invalid data instead would receive a Verification failure or Verified OK message. [This GitHub issue](https://github.com/digitalbazaar/forge/issues/316) indicates it could be a difference in padding algorithm. I think I am also concerned about the internal structure of SQL Server's signature output not being "formatted" the right way, but I am not really knowledgeable enough to know how far-fetched that is. Assuming that nothing super proprietary is going on here, what do I have to do to verify a SQL Server signature with external systems?

is it possible to create a function on PostgreSQL server, which routines can't be seen by any Postgres-user on the server? The function gets some string as a parameter and outputs a boolean value. But nobody should know, how the calculation of the boolean value works. Why do I need it: in the function's text is a private key. Maybe there is some other possibility to "hide" the private key or salt securely.

I am hoping someone has some crossover experience here. I am attempting to utilize the Hashbytes function within SQL to match a value that is being generated utilizing the MD5 function from Informatica. Has anyone ever been dove into this issue? My first assumption is that since the systems are different, the actual calculation of the checksum might have minute differences. It also could be how I am attempting to concatenate the values prior to using the hashbytes MD5 function. I am at a loss, and am beginning to think it won't ever be possible to match these values (as many have said online) Any help would be greatly appreciated.

Am trying to insert some value through JAVA Application with AES Algorithm and need to decrypt the data from the SQL Procedure and need to process a data. Which is the best practice to do so?

I was wondering if there would be any way to do the following in PostgreSQL: UPDATE cryptotable SET work = work + 'some big hexadecimal number' where work is an unsigned 256 bit number. Right now my column is a character varying(64) column (hexadecimal representation) but I would be happy to switch to another data type if it lets me do the operation above. If it's not possible with vanilla PostgreSQL, are there extensions that could help me? (I also [posted this to pgsql-hackers](http://www.postgresql.org/message-id/CALwxDuETY1_jzBSQC+nR-uBY0eZHhH5poTMo0KCCi14Z4RtCtQ@mail.gmail.com) so check out the thread there for more ideas.)

I have considered TDE and cell level encryption mechanism for securing my database, however these two cannot fully satisfy my requirements. I've found that PGP may help me, but it is mainly used for mail services. What is the technical feasibility for implementing PGP for my SQL server database, is there any possibilities for that? TDE does not satisfy my requirements because it only protects data-at-rest. I need to protect the data from an attacker who has access to the database while it is running on the server containing the encryption certificate, as well as the data contained within backups. I'd also like to have some users able to see the decrypted values, while some other users cannot ever see the decrypted values. 1. I have implemented master-slave Replication, and need to enable security without affecting the replication. 2. Approximately 80% of coding has been completed, so its really hard to change the queries and stored procedures. 3. I need to enable security for selected tables only (payments, customer details, password, etc.) 4. I need to secure data from injection.

The source code for pg_crypto module mentions an option to build without cryptographically-strong random generation. >/* > > * Generate random bits. pg_backend_random() will do here, we don't promis > > * UUIDs to be cryptographically random, when built with > > * --disable-strong-random. > > */ Discussed further in this thread . In the particular build I may be using, how can I **detect if the strong random feature is enable or disabled**?

I am using encryption algo DES/CBC/PKCS5Padding. How do I encrypt data using this in DB2? In Pl/SQL I used DBMS_CRYPTO package with DES_CBC_PKCS5 from DBMS_CRYPTO Block Cipher Suites - https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm Version is DB2 v10.5.0.5 and it will run on a Linux box. Distro may be different. Query - SELECT service_level, fixpack_num FROM TABLE (sysproc.env_get_inst_info());

When I connect to some e-commerce website with my browser I use HTTPS that uses a certificate that (more or less) guarantees that example.com is indeed example.com. Now I connect to a SQL Server somewhere on the Internet - it might be SQL Azure for example and then I just connect to a URL like abcdef.database.windows.net. There's a chance that there's a fake server there and then I expose my sensitive data. I haven't found any evidence that certificates or anything equivalent is used when connecting to SQL Server. How is SQL Server host identity ensured?