Published
Mar 13 2019 06:49 PM
11.5K
Views
First published on MSDN on Sep 21, 2017
In these last days security is being a common topic on the news not for the best reasons and to help to avoid being the subject of one of those news I will share how to increase the security on our Azure SQL Databases.
One way to increase the security on our Azure SQL Database is by using certificates that can be used by some functions like SIGNBYCERT , VERIFYSIGNEDBYCERT , ENCRYPTBYCERT , DECRYPTBYCERT and others.
It's common to receive the certificate file from external entities, but Azure SQL Database don't have access to a file system where he can place the certificate file to import.
So how can we import certificates?
First be aware that depending on the compatibility level of your Azure SQL Database you should select the right algorithm to be used.
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/choose-an-encrypti...
Azure SQL Database can't import .pfx files so, if we have one we first need to convert them to .cer and .pvk files.
To convert we can use the " Microsoft PVKConverter for SQL Server " tool, using the following command.
PVKConverter.exe -i <PFX format file> -o <PVK/DER format file> -d <Decryption password> -e <Encryption password>
Remember the password used to encrypt as we will use it again.
The following image show the command and the result of the conversion.
More info on converting .pfx to .pvk: https://support.microsoft.com/en-us/help/2914662/how-to-use-pfx-formatted-certificates-in-sql-se...
We can't use the CREATE CERTIFICATE ... FROM FILE ... because like I said before we don't have access to a file system where we can place the files. So, we can use CREATE CERTIFICATE ... FROM BINARY ...
To use CREATE CERTIFICATE ... FROM BINARY ... we need to convert the .cer and .pvk files into binary that we will use. I use a local instance of SQL Server to execute the following queries to get the binary values of the .cer and .pvk files.
Next in the Azure SQL Database we create the certificate using the following query.
To complete the query we need to:
copy the value of BulkColumn of the .cer file and place on the "from binary ="
copy the value of BulkColumn of the .pvk file and place on the "private key ( binary ="
Now we have the certificate on our Azure SQL Database.
Now we can encrypt and decrypt using this certificate.
In these last days security is being a common topic on the news not for the best reasons and to help to avoid being the subject of one of those news I will share how to increase the security on our Azure SQL Databases.
One way to increase the security on our Azure SQL Database is by using certificates that can be used by some functions like SIGNBYCERT , VERIFYSIGNEDBYCERT , ENCRYPTBYCERT , DECRYPTBYCERT and others.
It's common to receive the certificate file from external entities, but Azure SQL Database don't have access to a file system where he can place the certificate file to import.
So how can we import certificates?
First be aware that depending on the compatibility level of your Azure SQL Database you should select the right algorithm to be used.
" Beginning with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated. To use older algorithms (not recommended) you must set the database to database compatibility level 120 or lower."
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/choose-an-encrypti...
Azure SQL Database can't import .pfx files so, if we have one we first need to convert them to .cer and .pvk files.
To convert we can use the " Microsoft PVKConverter for SQL Server " tool, using the following command.
PVKConverter.exe -i <PFX format file> -o <PVK/DER format file> -d <Decryption password> -e <Encryption password>
Remember the password used to encrypt as we will use it again.
The following image show the command and the result of the conversion.
More info on converting .pfx to .pvk: https://support.microsoft.com/en-us/help/2914662/how-to-use-pfx-formatted-certificates-in-sql-se...
We can't use the CREATE CERTIFICATE ... FROM FILE ... because like I said before we don't have access to a file system where we can place the files. So, we can use CREATE CERTIFICATE ... FROM BINARY ...
To use CREATE CERTIFICATE ... FROM BINARY ... we need to convert the .cer and .pvk files into binary that we will use. I use a local instance of SQL Server to execute the following queries to get the binary values of the .cer and .pvk files.
SELECT 1, BulkColumn FROM OPENROWSET(BULK N'C:\temp\Example_Certificate.cer', SINGLE_BLOB) rs
SELECT 1, BulkColumn FROM OPENROWSET(BULK N'C:\temp\ Example_Certificate.pvk', SINGLE_BLOB) rs
Next in the Azure SQL Database we create the certificate using the following query.
To complete the query we need to:
copy the value of BulkColumn of the .cer file and place on the "from binary ="
copy the value of BulkColumn of the .pvk file and place on the "private key ( binary ="
CREATE CERTIFICATE Example_Certificate
FROM BINARY = 0x308201D53082013EA...0E51E
WITH PRIVATE KEY ( BINARY = 0x1EF1B5B0000000000...5D9E4B5
, DECRYPTION BY PASSWORD = '<password used to encrypt the .pvk file>')
Now we have the certificate on our Azure SQL Database.
Now we can encrypt and decrypt using this certificate.