///<summary>
///Definitions(andvalues)forSQLServerSignaturequeries
///</summary>
classSigSigningProvider_SQLServer_Server
{
#define.SystemDataSqlDbType('System.Data.SqlDbType')
#define.NVarChar('NVarChar')
#define.DateTime('DateTime')
#define.VarBinary('VarBinary')
#define.Int('Int')
#define.SystemConvert('System.Convert')
#define.ToBase64String('ToBase64String')
#define.FromBase64String('FromBase64String')

#define.ElementNameSignatureList('signaturelist')
#define.ElementNameSignature('signature')
#define.AttrNameStart('start')
#define.AttrNameLength('length')

#define.PosStart(1)
#define.PosLength(2)
#define.PosSignature(3)

System.Data.SqlClient.SqlConnectionconn;
}
///Verifyifthereisavalidcertificateforthe<paramrefname="_signeeId"/>
///</summary>
///<paramname="_signeeId">
///Certificatesignee
///</param>
///<returnstype="boolean">
///true,ifavaildcertificateisfoundforsignee;otherwisefalse
///</returns>
///<exceptioncref="Exception::Error">
///SQLServerwasunabletovalidatecertificate
///</exception>
publicbooleanhasValidCertificateSQL(SIGSQLCertSubject_signeeId)
{
#LocalMacro.SelectStmt
'SELECTCOUNT(*)FROMsys.certificates'
+'WHEREsubject=@s'
+'ANDstart_date<=@f'
+'ANDexpiry_date>@t'
+'ANDpvt_key_encryption_type=N\'PW\''
#EndMacro
#define.ParmNameSubject('@s')
#define.ParmNameStartDate('@f')
#define.ParmNameExpiryDate('@t')

System.Data.SqlClient.SqlCommandcmd;
System.Data.SqlClient.SqlParameterparam;
System.Data.SqlDbTypetype;
System.Data.SqlClient.SqlParameterCollectionparams;
strstmtText;
intintRes;
booleanret=false;
utcdatetimeutcToday;
strerrMsg;
;

utcToday=DateTimeUtil::newDateTime(systemdateget(),0);
stmtText=#SelectStmt;

try
{
newInteropPermission(InteropKind::ClrInterop).assert();
conn.Open();
cmd=conn.CreateCommand();
cmd.set_CommandText(stmtText);

params=cmd.get_Parameters();
type=CLRInterop::parseClrEnum(#SystemDataSqlDbType,#NVarChar);
param=params.Add(#ParmNameSubject,type);
param.set_Value(_signeeId);

type=CLRInterop::parseClrEnum(#SystemDataSqlDbType,#DateTime);
param=params.Add(#ParmNameStartDate,type);
param.set_Value(utcToday);

param=params.Add(#ParmNameExpiryDate,type);
param.set_Value(utcToday);

intRes=cmd.ExecuteScalar();
ret=(intRes>0);
conn.Close();
}
catch
{
errMsg=AifUtil::getClrErrorMessage();
try
{
conn.Close();
}
catch
{
ret=false;
}
throwerror(errMsg?errMsg:"@SYP1558");
}
returnret;
}
//---------------------------------------
///<summary>
///Revokepermissiontocertificate
///</summary>
///<paramname="_id">
///CertificateIdforcertificatetohavepermissionsrevoked
///</param>
publicvoidrevokeCertificateSQL(SIGCertificateId_id)
{
#LocalMacro.RevokeStmt
'ALTERCERTIFICATE\%1REMOVEPRIVATEKEY'
#EndMacro
System.Data.SqlClient.SqlCommandcmd;
strstmtText;
strname;
strsubject;
;

name=this.getNameForId(_id);
if(!name)
{
return;
}
subject=this.getSubjectForId(_id);
if(subject!=curuserid()
&&(!hasSecuritykeyAccess(securitykeynum(AdminSetup),AccessType::Delete)))
{
return;
}
stmtText=strfmt(#RevokeStmt,name);

try
{
newInteropPermission(InteropKind::ClrInterop).assert();
conn.Open();
cmd=conn.CreateCommand();
cmd.set_CommandText(stmtText);

cmd.ExecuteNonQuery();
conn.Close();
}
catch
{
try
{
conn.Close();
}
catch
{
stmtText='';
}
}
}
//----------------------------------------------
///<summary>
///RetrievecertificatenamebaseoncertificateId(<paramrefname="_id"/>
///</summary>
///<paramname="_id">
///CertificateId
///</param>
///<returnstype="str">
///Certificatename
///</returns>
publicstrgetSubjectForId(SIGCertificateId_id)
{
#LocalMacro.SelectStmt
'SELECTsubject'
+'FROMsys.certificates'
+'WHEREcertificate_id=@id'
#EndMacro
#define.ParmNameId('@id')

System.Data.SqlClient.SqlCommandcmd;
System.Data.SqlClient.SqlParameterparam;
System.Data.SqlDbTypetype;
System.Data.SqlClient.SqlParameterCollectionparams;
strstmtText;
strsubject;
;

stmtText=#SelectStmt;

try
{
newInteropPermission(InteropKind::ClrInterop).assert();
conn.Open();
cmd=conn.CreateCommand();
cmd.set_CommandText(stmtText);

params=cmd.get_Parameters();
type=CLRInterop::parseClrEnum(#SystemDataSqlDbType,#Int);
param=params.Add(#ParmNameId,type);
param.set_Value(_id);

subject=cmd.ExecuteScalar();
conn.Close();
}
catch
{
try
{
conn.Close();
}
catch
{
subject='';
}
}
returnsubject;
}
//------------------------------------------------
///<summary>
///Revokepermissiontocertificate
///</summary>
///<paramname="_id">
///CertificateIdforcertificatetohavepermissionsrevoked
///</param>
publicvoidrevokeCertificateSQL(SIGCertificateId_id)
{
#LocalMacro.RevokeStmt
'ALTERCERTIFICATE\%1REMOVEPRIVATEKEY'
#EndMacro
System.Data.SqlClient.SqlCommandcmd;
strstmtText;
strname;
strsubject;
;

name=this.getNameForId(_id);
if(!name)
{
return;
}
subject=this.getSubjectForId(_id);
if(subject!=curuserid()
&&(!hasSecuritykeyAccess(securitykeynum(AdminSetup),AccessType::Delete)))
{
return;
}
stmtText=strfmt(#RevokeStmt,name);

try
{
newInteropPermission(InteropKind::ClrInterop).assert();
conn.Open();
cmd=conn.CreateCommand();
cmd.set_CommandText(stmtText);

cmd.ExecuteNonQuery();
conn.Close();
}
catch
{
try
{
conn.Close();
}
catch
{
stmtText='';
}
}
}