Skip to main content

SQL Server Encryption Using T-SQL functions - Encrypt and Decrypt a Password using EncryptByPassPhrase and DecryptByPassPhrase


Introduction


You can use encryption in SQL Server for connections, data, and stored procedures.
This Article explains you how to Encrypt and Decrypt a text using T-SQL functions.you can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE function.


Encryption

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.
Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. When you are deciding whether to implement encryption, consider how users will access data. If users access data over a public network, data encryption might be required to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.
 

Explanation


EncryptByPassPhrase:

EncryptByPassPhrase uses the Triple DES algorithm to encrypt the text passed in.

Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE',‘text’)
In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key,and 'text' data type shoulb be VarBinary.
Creating a 'login_details' table:- 

create table login_details(uid integer,username varchar(10),password varbinary(100))
insert into login_details(uid,username,password) values(1,'smith',EncryptByPassPhrase('12',’XXX’))
insert into login_details(uid,username,password) values(2,'kennal',EncryptByPassPhrase('12','YYY'))
insert into login_details(uid,username,password) values(3,'staurt',EncryptByPassPhrase('12','ZZZ'))
 select * from login_details




 

DECRYPTBYPASSPHRASE:-

Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE.

DECRYPTBYPASSPHRASE function takes two arguments one is 'PASSPHRASE'and text or column_name.
select uid,username, DECRYPTBYPASSPHRASE ('12',password) as Password from login_details



In the above result the password still in VarBinary.So we have to Convert the VarBianry in Varchar by using Convert function as follows.
 select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) from login_details




Pros and Cons
Sometimes we need to protect the data from DBA and SysAdmin too. The problem with using above approach is that it will not be possible. For that matter it will not be possible in almost all SQL server encryption method.

This is because Encryption and Decryption takes place at the server and Admins do have access to the server. They can use profiler to find out what query has been fired.

We need to save the encryption key so where and Admins do have all permissions there.

The good part is that you can use retrieve the data even if the system crashes which would not have been possible with DPAPI.

Thus you can use it when you have total control over the environment and have your own (reliable) Admins. So for sure we are not going to use it in case we are hosting the application on a hosting providers server.


Solution
SQL Server Encryption is generally used while transferring the data over network.

For Encrypting the sensitive data it is recommended to either use Extensible Key Management (EKM) or encrypt and decrypt data in business layer.





Labels:

Comments

Post a Comment

Popular posts from this blog

Create a background / taskbar application in c# .NET

Recently, I was working on integration of two windows applications. First application will launch the second application on login and then they both will communicate using pre-defined set of instructions. There were some complications (I am not going into them) and thus we decided to have a third application which actually will act as mediator. First application will launch the mediator (third application) and it will launch the second application. For this purpose we needed to create a task bar application (which will run in background). How To ·          Create a new windows project and delete the default form (Form1). ·          In Program.cs create a new class and inherit it from Form. ·          Please refer the code below. ·          Now change the Main method. In Application.Run change the startup objec...
2 comments
Read more

Check SQL Server Job status (State) using sp_help_job and xp_sqlagent_enum_jobs

This article is about checking the status of a SQL job. In our work place we have lot of SQL jobs. These jobs will run whole day and are business critical. They will load the data and generate extracts which will be used by business people. Thus, it becomes quite essential to support the system efficiently so that the job finishes in time and as desired. Also, while designing a new system sometimes we need to check the dependency of one job over another. In such scenario we need to check whether a particular job has finished or not. All this can be achieved in SQL Server by using the procedures:- sp_help_job xp_sqlagent_enum_jobs Note: xp_sqlagent_enum_jobs is an undocumented proc inside of sp_help_job and is used extensively to get SQL agent job information. sp_help_job: This procedure gives some insight into the status, and information, about a job. This stored procedure provides information such as last start time, job status etc. Syntax sp_help_job { [ @job_id= ] jo...
2 comments
Read more

Java 8 JMX Default Metrics

This is more of a note. Here you can find default types and attributes for JMX on top of Java 8. Code: I will clean and explain it later :( private static void WriteAttributes(final MBeanServer mBeanServer, final ObjectName http) throws InstanceNotFoundException, IntrospectionException, ReflectionException { MBeanInfo info = mBeanServer.getMBeanInfo(http); MBeanAttributeInfo[] attrInfo = info.getAttributes(); System.out.println("Attributes for object: " + http +":\n"); for (MBeanAttributeInfo attr : attrInfo) { System.out.println(" " + attr.getName() + "\n"); } } Attributes for object: java.lang:type=MemoryPool,name=Metaspace:   Name   Type   Valid   Usage   PeakUsage   MemoryManagerNames   UsageThreshold   UsageThresholdExceeded   UsageThresholdCount   UsageThresholdSupported   CollectionUsageThreshold   Collectio...
Post a Comment
Read more