Passing ClientCertificate data from excel to asp/aspx

[Philipe Meniu]

I have an Excel app in VBA which accesses asp pages
producing dynamic serialized datasets in xml from ADO.
This is sensitive data (mortgage rates and lenders
conditions) which needs to be protected. I decided to use
Client Certificates to identify uniquely the Excel client
to the asp page which produces the xml to be read by Excel.

Non secured, the application works OK. I try now to secure
it from the serving asp page (I also tried using asp.net
with no more results). What I want to do in the xml
generating asp page is read the ClientCertificate with
Request.ClientCertificate("SubjectEMAIL"), then I use this
email adress to verify the user exists and still still
active in our sql DB.

The standalone .asp page works fine, detects the
certificate and allows the program to manipulate the
certificate data and act accordingly, if the certificate
is valid and User stil active, I return the serialized
dataset in xml, other wise I return nothing to the calling
Excel VBA.

The issue I have is that once called within Excel, the asp
page which checks the client certificate data does not
work and returns a security error. If I run the page
outside of Excel in IE it pops-up a window to chose which
certificate to send to the requiring asp page and then
asks again for OK to send private Key, I think this is
where my problem stems from, Excel probably does not do
this automatically.

Does anybody have any idea as how to handle this?

Code for asp certif reading page:
<%@Language="VBScript"%>
<%
Response.ContentType = "text/xml"
'Response.Expires = 0
Response.Buffer = False
'Response.IsClientConnected
'Response.write "Jusqu'au : " & Request.ClientCertificate
("ValidUntil") & "<br>"

dim em, dtUntil, cert, usact, dtOK, usid
'em=Request.ClientCertificate("SubjectCN")
em=""
cert=0
em=Request.ClientCertificate("SubjectEMAIL")
if Request.ClientCertificate("Flags")="1" then
cert=1
else
cert=0
end if

if isDate(Left(Request.ClientCertificate("ValidUntil"),
10)) then
dtOK=CDate(Left(Request.ClientCertificate("ValidUntil"),
10))
elseif isDate(Left(Request.ClientCertificate
("ValidUntil"), 9)) then
dtOK=CDATE(Left(Request.ClientCertificate("ValidUntil"),
9))
elseif isDate(Left(Request.ClientCertificate
("ValidUntil"), 8)) then
dtOK=CDate(Left(Request.ClientCertificate("ValidUntil"),
8))
else
dtOK=0
end if

dtUntil = Request.ClientCertificate("ValidUntil")
%>
<!--#include file="../Connections/MPH.asp" -->
<%
Dim rsUser__M1
rsUser__M1 = ""
If (em <> "") Then
rsUser__M1 = em
End If
%>
<%
Dim rsUser
Dim rsUser_numRows

Set rsUser = Server.CreateObject("ADODB.Recordset")
rsUser.ActiveConnection = MM_MPH_STRING
rsUser.Source = "SELECT UserID, sexe, Lang1,
IntranetLevel, CallCenter, Active FROM dbo.Users WHERE
((EMail<>'' AND EMail='" + Replace(rsUser__M1, "'", "''")
+ "') OR (EMailOther='" + Replace(rsUser__M1, "'", "''")
+ "' AND EMailOther<>'')) AND Active=1"
rsUser.CursorType = 0
rsUser.CursorLocation = 2
rsUser.LockType = 1
rsUser.Open()

rsUser_numRows = 0
%>


<%
if rsUser.eof=false then
usact="OK"
usid = rsUser.Fields.Item("UserID").value
else
usact="Problem"
usid=" "
end if

Response.Write "<?xml version='1.0'?>" & vbNewLine
Response.write "<AccesGrille>" & vbNewLine
Response.write "<Certif Present='" & cStr(cert)
& "' Valid='" & Cstr(dtOK) & "' UserActif='" & usact & "'
UserID='" & usid & "' />" & vbNewLine
response.write "</AccesGrille>" & vbNewLine
%>

<%
rsUser.Close()
Set rsUser = Nothing
%>

 

[Edwin]

Hi Philipe Meniu,

You probably have to change something on the client side (the excel app
requesting the page) to include a client certificate in the request.

On the server the only thing you can do is require clientcertificates
for the website, which you already set hence the select certificate
prompt in the browser.

my 5 cents
regards,
Edwin