Como cifrar las contraseñas de usuarios en SQL Server

En esta ocasión vamos a utilizar un método de cifrado de datos en SQL Server para nuestros proyectos, explicando cada paso de su creación:

• Creación y configuración de Tabla en Sql Server
• Creación de Procedimiento almacenado para registrar contraseña (Registrar dato a cifrar).
• Creación  de Procedimiento almacenado para validar contraseña (Obtener dato cifrado).

El  método de cifrado a utilizar será el "ENCRYPTBYPASSPHRASE"

El proceso consiste en ofuscar los datos a través de una palabra clave (PassPhraser), y de esta forma garantizar la protección frente al propietario de la base de datos y de posibles intromisiones a la base de datos.

No olvidar que mientras más campos cifrados tenemos, más tiempo le tomará a la base de datos obtener la información, así que solo realicen el cifrado a campos que realmente lo necesiten, como por ejemplo los obligados por la ley de protección de datos de su país (Un tema un interesante, tenía que decirlo).

Comencemos por la Creación de la tabla:

Crearemos una tabla con cuatro campos:

 1 USE [Pamf_BD]
 2 GO
 3 /****** Object:  Table [dbo].[Usuario]    Script Date: 11/05/2015 08:46:06 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 SET ANSI_PADDING ON
 9 GO
10 CREATE TABLE [dbo].[Usuario](
11 	[Id_Usuario] [int] IDENTITY(1,1) NOT NULL,
12 	[NombreUsuario] [varchar](15) NOT NULL,
13 	[ContraseñaUsuario] [varbinary](250) NOT NULL,
14 	[Estado] [nchar](1) NOT NULL
15 ) ON [PRIMARY]
16 GO
17 SET ANSI_PADDING OFF
18 GO

Creación del procedimiento almacenado para registrar contraseña a cifrar

Crearemos el procedimiento almacenado para el registro de los datos cifrados.

 1 USE [Pamf_BD]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[I_Usuario]    Script Date: 11/05/2015 09:55:03 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 -- =============================================
 9 -- Author:		<Oswaldo Ríos Mozombite>
10 -- Description:	<Creado para http://vbnet-codigofuente.blogspot.pe/>
11 -- =============================================
12 ALTER PROCEDURE [dbo].[I_Usuario]
13 	
14 	@NombreUsuario as varchar(20),
15 	@ContraseñaUsuario as varchar(20),
16 	@PassPhraser as varchar(20)
17 AS
18 BEGIN
19 
20 	SET NOCOUNT ON;
21 
22 	INSERT INTO USUARIO VALUES 
23 	(@NombreUsuario, --El nombre de Usuario
24 	ENCRYPTBYPASSPHRASE(@PassPhraser,@ContraseñaUsuario), --La palabra clave y la contraseña del usuario
25 	1) --Este el el estado del usuario (sólo para ejemplo)
26 END

Entonces lo que tendríamos que enviar a la BD desde nuestra aplicación será lo siguiente:

• El nombre de usuario: @NombreUsaurio
• La contraseña de  usuario: @ContraseñaUsuario
• La Palabra clave para utilizaremos para validar que tenemos el acceso a la información, es la variable que contiene una frase de contraseña a partir de la cual el SQL Server generará una clave simétrica, por ejemplo: Mi palabra clave será : "1ntr3p1d0" y lo que enviaré a la base de datos será (StrindeConexion, NombredeProcedimientoAlmacenado, NombredeUsuario, ContraseñadeUsuario, PalabraClave) 

Debería quedar algo así, así guardara el SQL Server la contraseña

Creación del procedimiento almacenado para validar contraseña cifrada

Crearemos el procedimiento almacenado para obtener la contraseña que hemos cifrado anteriormente

 1 USE [Prueba_BD]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[S_ValidarUsuario]    Script Date: 11/05/2015 12:32:26 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 -- =============================================
 9 -- Author:		<Oswaldo Ríos Mozombite>
10 -- Description:	<Creado para http://vbnet-codigofuente.blogspot.pe/>
11 -- =============================================
12 ALTER PROCEDURE [dbo].[S_ValidarUsuario]
13 	@NombreUsuario as varchar(20),
14 	@PassPhraser as varchar(20)
15 AS
16 BEGIN
17 	SET NOCOUNT ON;
18 	SELECT Id_Usuario, 
19 	NombreUsuario,
20 	--Con esto le decimos al SQL que te devuelva el campo "ContraseñaUsuario" convertido en varchar.
21 	--Pero la unica forma de obtener eso es con el @PassPhraser que será la palabra clave que nosotros enviemos desde la app
22 	CONVERT(VARCHAR(250),DECRYPTBYPASSPHRASE(@PassPhraser,ContraseñaUsuario))
23 	from Usuario where NombreUsuario=@NombreUsuario and Estado='1'
24 END

Con esto obtendremos los datos sigueintes:

• Id_Usuario,  
• NombreUsuario
• ContraseñaUsuario: Recuerndan que pusimos "1ntr3p1d0", pues eso será lo que obtengamos
• Estado

También tenemos otros tipos de cifrados, como el "ENCRYPTBYKEY", "ENCRYPTBYASYMKEY", "ENCRYPTBYCERT", "MD5", "SHA" y otros, que nos podrían ser útiles según nuestras necesidades.