SQL Server offers many cryptographic methods such as one-way encryption (hashing) and cell-level encryption. These methods all are designed to protect data while it is stored in the database. To utilize encrypted data it must be decrypted. In doing so, the formerly protected sensitive data is captured into the process memory in plain text.

Below is an example of a value, "ThisIsPlaintext", that is stored in the database using cell-level encryption and appears in plain text in the process memory after querying:

's e l e c t  t h e  w h o l e  r o w . . 0 ' ' ' ' . . . 0
' ' ' . . . . ' ' ' ' ' 0 . ' . . ' ' 3 . * . T h i s I s
P l a i n t e x t . . . . . . . . . . . . . . . . . . . . .

With the HashBytes method, decryption does not occur. Instead, a value is hashed and the resulting hash is compared to the stored hashed value. If there is a match, then it is confirmed that the correct value was compared. When the HashBytes method is executed, it requires the sensitive data to be passed as a parameter in plain text. It is at this point that the plain text value is captured into the process memory. The text below illustrates this occurrence in the process memory:

s e l e c t . . . C A S E  W H E N C i p h e r _ T e x t
= H a s h B y t e s ( ' S H A 1 ', ' M y  S e n s i t i v e
V a l u e ' ) T H E N  ' T r u e ' E L S E ' F a l s e '
E N D . . . f r o m . . . d b o . H a s h b y t e s _ S a m

A similar occurrence can be seen when a user gains access to SQL Server through a SQL Server Login. The authentication process is very similar to the HashBytes method in that it does not perform decryption of the stored password, instead it creates a hash value of the password that was submitted and compares it to the hash value that is stored in the database. When a positive match occurs authentication is granted and the submitted password is stored in plain text as a part of a connection string in the process memory, as illustrated below:

S Q L _ _ _ _ _ _ P r o c e s s _ _ _ _ _ _ A v a i l a b l e .
. . ' ' ' . - . s e r v e r = ' M y S e r v e r '; u i d = '
T e s t L o g i n ' ; p a s s w o r d = ' T h i s i s m y p a
s s w o r d ' ; A p p l i c a t i o n

TIP: Utilize Windows Authentication instead of SQL Server Logins to grant access to a SQL Server instance. Windows Authentication does not store passwords in plain text in the process memory.

At first glance this vulnerability may seem quite alarming; but it is not one that is unmitigated. To view the process memory in a Microsoft Windows operating system requires administrative privileges to the server. If a hacker, or malicious individual, were to gain this level of privileges to the server there is very little they cannot do. One of which is to disable or uninstall any software that was installed on the server to protect the process memory. Additionally, the ability to read the process memory is the least of your concerns if a hacker had gained this level of control over your server.

The exposure of sensitive data in the process memory is not unique to the Microsoft world of products. There are methods in Oracle that require plain text arguments, such as ALTER USER, which result in the sensitive values to be cached. The decryption process in Oracle also results in plain text values being stored in memory. Other operating systems, such as Unix, also reveal sensitive data in its process memory.

The protection methods that are available in SQL Server are designed to protect data while it is stored in the database. Once it is queried and cached there is a dependency upon the operating system of the server as well as the application performing the query to provide additional layers of protection. In this example, the restricted access to the process memory is managed by the operating system.

There is no magic bullet that one application can provide that mitigates all possible vulnerabilities. It is the security methods of all components, including the people factor, working in concert and in layers that achieves a secure environment.