Encriptación SQL Server y ASP

[ivanargulo]

Buenas!

Este tema no sé si debe ir en ASP o en SQL Server, pero me parece que es mas propio aquí.

Tengo un procedimiento almacenado de la siguiente forma

Código PHP:

  CREATE PROCEDURE ep.sp_datos_cliente
    (
        @IdCliente int,
        @Clave nvarchar(50)    
    )
AS
BEGIN
 
    SET NOCOUNT ON;
 
    DECLARE @KEYID INT
 
    EXEC ep.sp_id_clave_asimetrica @KEYID = @KEYID OUTPUT
 
    SELECT  CAST(DecryptByAsymKey(@KEYID, Pass, @Clave) AS varchar(MAX)) AS Pass 
    FROM Clientes
    WHERE IdCliente = @IdCliente
END 
   

El campo "Pass" guarda la contraseña encriptada en el servidor. Ejecutando este procedimiento almacenado desde SQL Server veo la contraseña desencriptada y correctamente.

Sin embargo, al recogerlo desde ASP, el campo aparece vacío. Como información adicional, si recojo otros campos de la base de datos todos se muestran correctamente.

El código ASP no tiene nada, simplemente crear un procedimiento almacenado y recoger los valores (lo tengo todo creado con funciones, así que no tiene mucho sentido).

Código PHP:

  CrearSP sp, "ep.sp_datos_cliente"
            
ParamIN sp, "IdCliente", adVarChar, 100, 16
(etc)
                
EjecutarSP rs, sp
 
Response.Write rs("Pass") 
   

Deciros que este código está probado y funcionando en mil procedimientos, sólo falla al leer campos codificados.

¿Alguna idea? He probado cambiando los tipos de parámetros como adWVarChar, etc. Y nada...

Gracias de antemano

[Myakire]

Solo para confirmar .....

- Si ese SP lo corres desde el analizar de consultas, te regreso todos los campos (encripatados y no encriptados) correctamente, ¿es verdad?

- Ese SP lo haz probado en otras palataforma y funciona SOLAMENTE falla en ASP , ¿es verdad?

-¿Cuántos registros te va a regresar ese SP? (Si te regresa solo uno, podrías probar con parámetros de salida)

Sería más útil saber que hacen los procedimientos ParamIN y EjecutarSP.

Saludos

[ivanargulo]

Gracias por tu interés, Myakire.

- Desde el analizador de consultas, los resultados son correctos (por eso sospecho que el fallo está en el código ASP).

- Sólo lo he probado en un servidor Windows con ASP y SQL Server (Express, por si nos puede ser útil) instalado.

- En principio sólo devolverá un registro, aunque no descarto la posibilidad de que en el futuro devuelva más. He probado con parámetros de salida y el resultado es el mismo: campos sin encriptar correctos, campos encriptados vacíos.

Aquí está el código de las funciones:

Código:

public sub CrearSP(byRef adocmd, nombreSP)

	set adocmd = Server.CreateObject("ADODB.Command")

	'Procedimiento Almacenado
	adocmd.CommandText = nombreSP
	
	adocmd.ActiveConnection = SGM_Conexion_x
	adocmd.CommandType = adCmdStoredProc
	
	'HayErrores()
	
end sub

public sub ParamIN(byRef sp, nombre, tipo, tam, valor)

	On Error Resume Next
	
	if tipo = adVarchar then valor = Left(valor, tam)

	sp.Parameters.Append sp.CreateParameter(nombre, tipo, adParamInput, tam, valor)
	
	if Err.Number <> 0 then
		Response.Write "Error en par&aacute;metro: " & nombre
	end if
	
	On Error GoTo 0

end sub

public sub ParamOUT(byRef sp, nombre, tipo, tam)

	sp.Parameters.Append sp.CreateParameter(nombre, tipo, adParamOutput, tam)

end sub

public sub EjecutarSP(byref RecSet, byRef sp)

	On Error Resume Next
	
	set RecSet = sp.Execute
	
	if Err.Number <> 0 then 
		Response.Redirect "error.asp"
	end if
	
end sub

No da ningún error, simplemente aparece el campo vacío.

He probado pasando como parámetros adWVarChar en lugar de adVarChar, porque el procedimiento almacenado para desencriptar utiliza el tipo nvarchar.

* Nota: También he probado ejecutando la SQL que llama al procedimiento almacenado directamente, la misma que genera el analizador de consultas, y el resultado es el mismo: vacío.

Muchas gracias por tu colaboración.

[ivanargulo]

He probado también lo siguiente:

Código:

SELECT Nombre, 
CAST(DecryptByAsymKey(256, A, N'$$miClave$$') AS varchar(MAX)) AS Pass 
FROM Clientes WHERE IdCliente = 1

El mismo código, ejecutado desde SQL Server da el valor correcto, pero desde ASP no...

[Myakire]

mmmmmmmm, es extraño

No me he topado con algo así, ....., ¿el resultado de esa encriptación son caracteres imprimibles? ¿qué es lo que generas? ¿dentro de qué rango del ascii están?

[ivanargulo]

Consiste en crear una clave de encriptación, en mi caso la he creado asimétrica. Se almacena en un campo tipo varbinary.

Código:

CREATE ASYMMETRIC KEY ClaveAsimetrica
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = '$$Clave$$'

Para insertar un valor encriptado (es una cadena de texto normal y corriente, con todos los caracteres ASCII.

Código:

DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')

INSERT INTO TARJETAS (NUMERO)
VALUES (EncryptByAsymKey(@KEYID, 'Cadena a encriptar'))

Así el valor aparece ilegible.

Hasta aquí todo correcto. El problema viene al hacer el proceso inverso:

Código:

DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')

SELECT CAST(DecryptByAsymKey(@KEYID,numero, N'$$Clave$$') AS VARCHAR(100))
FROM TARJETAS

Éste es el paso que me devuelve vacío. En teoría, el resultado es una cadena tipo nvarchar (unicode), pero como se ve en todos los ejemplos, hago el paso a varchar para que sea correctamente leído.

El resultado debería ser la cadena que codificamos al comienzo: "Cadena a encriptar". Es perfectamente imprimible. Es más, te digo la cadena exacta que debería devolver en mi caso: sgm. Como ves, no hay caracteres raros.

Pues bien, todo ello va a la perfección en SQL Server Management Studio, pero no desde ASP. El resultado es una cadena NULA. No lo entiendo...

Gracias por el tiempo!!

[Shiryu_Libra]

, solo una preguntita, esta "N" va dentro del codigo???

SELECT CAST(DecryptByAsymKey(@KEYID,numero, N'$$Clave$$') AS VARCHAR(100))

[ivanargulo]

Sí y no. No va en el procedimiento almacenado porque se declara el parámetro como nvarchar, y así se le pasa desde ASP.

En mis múltiples pruebas he llegado a ejecutar directamente la SQL desde ASP, con la cadena embebida en el código. En ese caso sí hay que poner la N, para que convierta una cadena normal en una unicode.

Código:

N'cadena' = CAST('cadena' AS nvarchar(6)

Pero ni por esas funciona :S

Gracias a ti también :)

[Myakire]

Bueno, ya con ese código veo que no es cosa del ASP, te falta crear un master key desde el TSQL.

Pueba con esto:

Código:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'bmsA$dk7i82bv55foajsd9764';

CREATE ASYMMETRIC KEY ClaveAsimetrica
WITH ALGORITHM = RSA_2048;

INSERT INTO TARJETAS (NUMERO)
VALUES (EncryptByAsymKey(AsymKey_ID('ClaveAsimetrica'), '$$Clave$$'))

SELECT NUMERO, 
       CAST(DecryptByAsymKey(AsymKey_ID('ClaveAsimetrica'), NUMERO) as VARCHAR)
AS DecryptedData 
FROM TARJETAS

[ivanargulo]

Probaré y te cuento, pero un master key sólo es necesario en claves simétricas (de hecho, ahí no la estás usando). Yo uso clave asimétrica.

Lo que me extraña es que desde SQL Server todo se vea perfecto, y desde ASP no...

Gracias.

[Myakire]

Prueba y me dices ............. deja igual hago una prueba más a fondo

[ivanargulo]

¿Lo has probado desde ASP? ¿O desde el Query Analizer?

[Myakire]

Debo pedirte disculpas ..
al probar ya en ASP, veo que efectivamente regresa un nulo

Código:

<%
	set con = Server.CreateObject("ADODB.Connection")
  ConnString = "Provider=SQLNCLI;Server=192.168.0.1;Database="&vBD&";Uid=cccc;Pwd=ccccc;Initial Catalog=pruebas"
  Con.CommandTimeout  = 0
  con.Open  ConnString

  Set cmd=server.CreateObject("ADODB.command")
  Set cmd.ActiveConnection = Con
	
  cmd.CommandText = "DROP ASYMMETRIC KEY ClaveAsimetrica; " &_
	                  "CREATE ASYMMETRIC KEY ClaveAsimetrica WITH ALGORITHM = RSA_2048; " &_
                    "INSERT INTO TEST (ClaveTxt, ClaveEncrip) VALUES ('$$Clave$$', EncryptByAsymKey(AsymKey_ID('ClaveAsimetrica'), '$$Clave$$'))"

  cmd.Execute	

  set rs = Server.CreateObject("ADODB.RecordSet")
  rs.Open "SELECT ClaveTxt, ClaveEncrip, IsNull(CAST(DecryptByAsymKey(AsymKey_ID('@Password'), ClaveEncrip) as CHAR(10)),'nulo') AS DecryptedData FROM TEST", con
	
  response.write "Clave Texto: " & rs(0) & "<br>"
  response.BinaryWrite "Clave Original: " & rs("DecryptedData") & "<br>"
%>

Dame unos minutos para seguir investigando

[Myakire]

Bueno, parece que el problema es del proveedor del SQL que no regresa el dato al IIS, pero haciéndolo directamente en la BD ya funciona dado que con parámetros de salida lo hace bien:

Código:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spValida]
@Clave char(10) output
AS
BEGIN
	SET NOCOUNT ON;


CREATE ASYMMETRIC KEY ClaveAsimetrica
WITH ALGORITHM = RSA_2048;

INSERT INTO TEST (ClaveTxt, ClaveEncrip)
VALUES ('$$Clave$$', EncryptByAsymKey(AsymKey_ID('ClaveAsimetrica'), '$$Clave$$'))

SELECT @Clave = 
       CAST(DecryptByAsymKey(AsymKey_ID('ClaveAsimetrica'), ClaveEncrip) as VARCHAR)
FROM TEST

END

Y el código ASP:

Código:

   cmd.CommandText = "spValida"
 	 cmd.CommandType=adCmdStoredProc
   cmd.Parameters.Append (cmd.CreateParameter("@Clave",      adchar, adParamOutput,10))
	 cmd.Prepared = True
	 cmd.Execute
	 
	 response.write "Valor: " & cmd.Parameters("@Clave")

Esto por fin, da el resultado esperado

Saludos!

[ivanargulo]

Veo que lo único distinto es el tipo de parámetro de salida: adchar..

Voy a probar. La pena es que solo funciona si hay un solo registro y no con todo un recordset, pero en fin.

Con lo que sea contesto. Saludos y gracias!

[Myakire]

Pues según he probado, si se puede regresar un RecordSet con múltiples filas desencriptadas, pero siempre y cuando sea por medio de un SP, lo cual constata que el problema esta en el proveedor de la BD con el IIS.