Loading...

Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

View: 385    Dowload: 0   Comment: 0   Post by: admin   Category: SQL Server   Fields: Other

Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the demand. E.

Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the demand. E.g: a bunch of products, transactions such as invoices, receipts etc. or customer details.

But today data has become an important entity, which drives business towards success. In today’s fast-moving world, companies who owned data and does analytics has become the most successful companies.

However one of the major concerns we have today is how to protect these data. Especially the sensitive ones. Since more data is being exposed to the cloud, it’s essential to protect it from going to the wrong hands and it has become a major problem since hackers nowadays are well equipped and are always on the look for stealing this valuable information whenever possible, since it’ll be a valuable asset in the open market.

But protecting the data from unauthorized access is a must. Failing to do so can have unexpected consequences. Entire business could get wiped out of the business due to this. Hence enterprises should seriously consider protecting their data and we will discuss how we can achieve this in SQL Server through data encryption.

Ways of Data Encryption in SQL Server

There are few ways of encrypting data in SQL Server. We will discuss the advantages and disadvantages of each method.

SQL Server provides following methods to encrypt data:

  • T-SQL Functions
  • Using Symmetric Keys**
  • Using Asymmetric Keys**
  • Using Certificates**
  • Transparent Data Encryption**

**Note : In this article I only plan to explain encryption/decryption functionality using T-SQL. I will talk about other methods which is mentioned about in future articles.

Using T-SQL Functions

Encrypting data using ENCRYPTBYPASSPHRASE 

Encryption is done using T-SQL function ENCRYPTBYPASSPHRASE.

ENCRYPTBYPASSPHRASE(passphrase,text_to_encrypt)

The first parameter is the passphrase which can be any text or a variable of type NVARCHAR, CHAR, VARCHAR, BINARY, VARBINARY, or NCHAR. The function uses this passphrase to generate a symmetric key.

For the illustration purpose, we will create a table which to hold employee details

CREATE TABLE dbo.Employee(
    Id               INT
    ,EmpName     VARCHAR(100)
    ,EmpDOB          SMALLDATETIME
    ,SSN         VARBINARY(128)
)

This example is to demonstrate the data encryption during INSERT DML Statement

INSERT INTO dbo.Employee(
    Id
    ,EmpName
    ,EmpDOB
    ,SSN
)
VALUES(
    1
    ,'Luke'
    ,'01-June-1980'
    ,ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','111-22-3333')
) 

image

Further details can be found in the Microsoft Documentation:
https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql

Decrypting data using T-SQL function DECRYPTBYPASSPHRASE 

Will take the same details which we inserted during the first case. The encrypted data can be decrypted using SQL function DECRYPTBYPASSPHRASE. If any attempt has been made to decrypt the data without using DECRYPTBYPASSPHRASE nor providing the proper details, it will fail the operation.

Without Decryption

SELECT
    Id,EmpName,EmpDOB,CONVERT(VARCHAR(128),SSN) AS SSN
FROM
    dbo.Employee
WHERE
    Id = 1

image

With Decryption (Incorrect Pass-phrase)

SELECT
    Id
    ,EmpName
    ,EmpDOB
    ,DECRYPTBYPASSPHRASE('IncorrectPassword',SSN ) AS SSN 
FROM
    dbo.Employee
WHERE
    Id = 1

image

But providing the correct pass-phrase will return the correct details

SELECT
    Id
    ,EmpName
    ,EmpDOB
    ,CONVERT(VARCHAR(128),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',SSN )) AS SSN
FROM
    dbo.Employee
WHERE
    Id = 1

image

However, there could be a requirement which you need to protect your data, not from stealing, but from getting updated with someone else’s.

One classic example is a login table. Suppose we have a table which stores login credentials, which is having the following structure.

*Note: In real-world cases, usually it’s more secure if you hash passwords rather than encrypting them. But I am using encryption for illustration purpose.

So if a person has access to update the details in the password column, he/she can easily replace the contents with their own and log on using that. This can be stopped by providing two additional values when details are inserted into the table using ENCRYPTPASSPHRASE.

CREATE TABLE dbo.LoginCredentails(
    UserId      INT
    ,UserName   VARCHAR(20)
    ,Pwd        VARBINARY(128)
)

We will insert two records into the above-created table.

INSERT INTO dbo.LoginCredentails(
    UserId
    ,UserName
    ,Pwd
)
VALUES
    (1001,'luke.skywalker',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','force be with you',1,CAST(1001 AS sysname)))
    ,(1002,'darth.vader',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','i am your father',1,CAST(1002 AS sysname)))

Please note that unlike the previous example, we are now providing two additional values to the ENCRYPTBYPASSPHRASE function. The first values are 1, which indicates whether an authenticator will be encrypted together with the password. If the value is 1 and authenticator will be added. The second value is the data which from which to derive an authenticator. In this example, we will use a value similar to a user id, so that when the value is decrypted, we could use the same value.

Following is a function to fetch the decrypted password based on the UserId. Assume we will be using this when validating the credential prior login.

CREATE FUNCTION  Fn_GetUserPwdById(@UserId AS INT)
RETURNS VARCHAR(50)
AS
BEGIN  
 
    DECLARE @Pwd AS VARCHAR(50)
    SELECT
        @Pwd = CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',LC.Pwd,1,CAST(LC.UserId AS sysname))) 
    FROM
        dbo.LoginCredentails AS LC
    WHERE
        LC.UserId = @UserId
 
    RETURN @Pwd
END

Using the aforementioned function we will retrieve the details.

SELECT
    UserId
    ,UserName
    ,dbo.Fn_GetUserPwdById(UserId) AS Pwd 
FROM
    dbo.LoginCredentails

image

But querying the data simply will get you the binary string of the encrypted value.

SELECT
    UserId
    ,UserName
    ,Pwd 
FROM
    dbo.LoginCredentails

Suppose if a person has enough privileges to do an update the password with a known one (from an existing user) it’ll allow him/her to log in to the system impersonating any user.

UPDATE LC SET LC.Pwd = (
    SELECT LC2.Pwd FROM dbo.LoginCredentails AS LC2 
    WHERE LC2.UserName = 'luke.skywalker'
) 
FROM dbo.LoginCredentails AS LC
WHERE
LC.UserName = 'darth.vader'

image

But if when the same function is used for decryption, it will return NULL for the updated record, preventing the login to be invalid if it’s replaced using an existing one.

Hashing data using HASBYTES

Apart from the above-mentioned function, there’s another function which can be used to hash data. Unlike encrypting, there’s no way you can reverse the hashed data and see the raw details.

Syntax:

HASHBYTES ( 'algorithm', { @input | 'input' } )  
/*
algorithm::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512   
*/
</algorithm></algorithm>

There are two parameters which you require to provide. The first parameter is the algorithm which should be used for hashing. The hashing algorithm can be any of the following:

  • MD2
  • MD4
  • MD5
  • SHA
  • SHA1
  • SHA2_256
  • SHA2_512

The second parameter is the input, which needs to be hashed. This can be either a character or binary string.

The return value is VARBINARY(n). n = maximum 8000 bytes.

Example:

DECLARE
    @TextToHash AS NVARCHAR(1000) = N'My Secret Message'
 
SELECT HASHBYTES('SHA1',@TextToHash) AS HashedData

image

Further details can be found in the Microsoft Documentation:
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql

Hope this might be useful to you and please feel free to comment your ideas.

Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the demand. E.

Posted on 21-03-2018 

Comment:

To comment you must be logged in members.

Files with category

  • Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

    Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

    View: 386    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the...

  • Building a Multi-Cloud Strategy

    Building a Multi-Cloud Strategy

    View: 215    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    Today, few companies are in a position to leverage a single cloud for all their needs. Even as service providers add multiple services like email and collaboration to try to keep people in the ecosystem, companies need a multi-cloud strategy.

  • Insufficient access rights error when doing backup in SQL Server

    Insufficient access rights error when doing backup in SQL Server

    View: 623    Download: 0   Comment: 0

    Category: SQL Server     Fields: none

    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    Migrate MySQL to Microsoft SQL Server

    View: 2738    Download: 0   Comment: 0

    Category: SQL Server     Fields: none

    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    COUNT of DISTINCT Rows in SQL Server

    View: 1541    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    Finding SQL Servers on the Network

    View: 888    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and...

  • SQL Server and Database Enumerator

    SQL Server and Database Enumerator

    View: 893    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 871    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

 
File suggestion for you
Loading...
File top downloads
Loading...
Loading...
Codetitle - library source code to share, download the file to the community
Copyright © 2018. All rights reserved. codetitle Develope by Vinagon .Ltd