HTTP status 403: Forbidden exception using certificate to authenticate ASP.NET web service

c# asp.net web-services authentication certificate

397

Solution 1

Sounds like the SSL certificate is failing to authenticate for the web service client. A good check is if you go to the service from the client’s machine and get an alert in the browser about an SSL certificate your service will not authenticate with the certificate (certificate is not trusted). It’s not that the certificate doesn’t work, it’s just not trusted.

If the service is across machines you might have to setup a certificate authority (this might help http://www.petri.co.il/install_windows_server_2003_ca.htm) and add it as a trusted publisher on the client machine. This might also help http://support.microsoft.com/kb/901183.

Another option is to simple not validate the SSL, see: http://geekswithblogs.net/jwhitehorn/archive/2006/09/20/91657.aspx

Solution 2

When I had this problem it turns out the client certificate/key pair I was using was signed by an intermediate CA which was in the current user store instead of the local machine store. It all looked good if you examined the cert while logged in but the IIS worker process could not see the intermediate CA. Thus, the web service call was not supplying the certificate with the request. You can verify this by checking the server web log for a 403 7 5 response.

397

Author by

Brecht27

Updated on July 05, 2022

Comments

Brecht27 almost 2 years

There is something wrong with the SUM i made in the query as below:

EDIT I post my query in English so everyone understand what i try to say:

    $sql = "SELECT 
       c.stock,
       c.id as cid,
       cb.course as ccourse,
       cb.price_member as cbprice_member,
       cb.price_not_member as cbprice_not_member,
       cb.study as cbstudy,
       cb.studentid,


          (SELECT 
              SUM(CASE WHEN c.stock > 0 THEN price_member ELSE 0 END) AS subtotal_member,
              c.stock 
           FROM 
              courses_orders cb
           JOIN 
              courses c 
           ON 
              cb.course_id = c.id 
           WHERE 
              cb.date_removed IS NULL AND 
              cb.date_order_mail IS NOT NULL AND
              cb.date_pickup IS NULL AND 
              cb.date_pickup_mail IS NULL AND 
              cb.studentid = '$studentid' AND 
              cb.course_id = '$cid' AND 
              c.stock > 0
          ) as subtotal_member,


       (SELECT SUM(price_not_member) FROM courses_orders WHERE date_removed IS NULL AND date_order_mail IS NOT NULL AND date_pickup IS NULL AND date_pickup_mail IS NULL AND studentid = '$studentid' AND course_id = '$cid') as subtotal_not_member
     FROM 
       courses c
     JOIN
       courses_orders cb
     ON
       cb.course_id = c.id
     WHERE 
       c.id = '$cid' AND cb.date_removed IS NULL AND cb.date_pickup IS NOT NULL AND cb.date_pickup_mail IS NULL AND cb.studentid = '$studentid'
     ";

So, The problem is the SUM(CASE WHEN c.stock > 0 THEN price_member ELSE 0 END) AS subtotal_member and i have subtotal_member twice. I think that is also not correct. If i set cb.price_member i have the error: operand should contain 1 column and if i set price_member (without cb. before) i have the error: price_member is to ambiguous. What i want to do here is to have the total price of all items without the ones where the stock is below 1. So i take here c.voorraad > 0 but the result is always the totalprice with all items and not only the ones who has a stock above 0.

This is the original query (with Dutch items), it is the same as above query with different fields. So ignore this if you have read the EN version.

         $sql = "SELECT 
               c.voorraad,
               c.id as cid,
               cb.artikel as cbartikel,
               cb.prijs_lid as cbprijs_lid,
               cb.prijs_niet_lid as cbprijs_niet_lid,
               cb.studierichting as cbstudierichting,
               cb.studentid,


                  (SELECT 
                      SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid,
                      c.voorraad 
                   FROM 
                      cursusdienst_bestellingen cb
                   JOIN 
                      cursusdienst c 
                   ON 
                      cb.cursus_id = c.id 
                   WHERE 
                      datum_verwijderd IS NULL AND 
                      datum_reservatie_mail IS NOT NULL AND
                      datum_afhaling IS NULL AND 
                      datum_afhaling_mail IS NULL AND 
                      studentid = '$studentid' AND 
                      cursus_id = '$cid' AND 
                      c.voorraad > 0
                  ) as subtotaal_lid,


               (SELECT SUM(prijs_niet_lid) FROM cursusdienst_bestellingen WHERE datum_verwijderd IS NULL AND datum_reservatie_mail IS NOT NULL AND datum_afhaling IS NULL AND datum_afhaling_mail IS NULL AND studentid = '$studentid' AND cursus_id = '$cid') as subtotaal_niet_lid
             FROM 
               cursusdienst c
             JOIN
               cursusdienst_bestellingen cb
             ON
               cb.cursus_id = c.id
             WHERE 
               c.id = '$cid' AND cb.datum_verwijderd IS NULL AND cb.datum_afhaling IS NOT NULL AND cb.datum_afhaling_mail IS NULL AND cb.studentid = '$studentid'
             ";

The problem is the SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid and i have subtotaal_lid twice. I think that is also not correct. If i set cb.prijs_lid i have the error: operand should contain 1 column and if i set prijs_lid (without cb. before) i have the error: prijs_lid is to ambiguous.

What i want to do here is to have the total price of all items without the ones where the stock is below 1. So i take here c.voorraad > 0 but the result is always the totalprice with all items and not only the ones who has a stock above 0.

EDIT The table cursusdienst contains the following fields (e.g.):

id   prijs_lid   prijs_niet_lid   artikel  voorraad
1    24.00       25.00            Course1  12
2    30.00       35.00            Course2  -10

The table cursusdienst_bestellingen contains the following fields (e.g.):

id   cursus_id   prijs_lid   prijs_niet_lid   artikel  studentid
1    1           24.00       25.00            Course1  123456789
2    2           30.00       35.00            Course2  123456789

The output in a table (invoice) - main query who gives me the correct output:

Artikel   Aantal   Prijs
Course1   1        24.00

And below the output table the totalprice table - subquery were it goes wrong:

Subtotal: 24.00
Tax: 1.20
Total: 25.20

So the SUM totalprice_member (=totaalprijs_lid in dutch) here would be 24.00 for the members (lid in dutch) and not 54.00 for the members because the stock of course2 is below 1. Now i have the 54.00 for the totaalprijs_lid what is wrong...

The total code:

        <table cellpadding="0" cellspacing="0" width="600" class="w320">
            <tr>
              <td class="item-table">
                <table cellspacing="0" cellpadding="0" width="100%">
                  <tr>
                    <td class="title-dark" width="300">
                      Cursus
                    </td>
                    <td class="title-dark" width="163">
                      Aantal
                    </td>
                    <td class="title-dark" width="97">
                      Totaal
                    </td>
                  </tr>

<?php
    if (!empty($_POST['bachelor1'])) {
    foreach ($cursus as $cid) {

         $sql = "
select 
  c.voorraad,
  c.id as cid,
  cb.artikel as cbartikel,
  cb.prijs_lid as cbprijs_lid,
  cb.prijs_niet_lid as cbprijs_niet_lid,
  cb.studierichting as cbstudierichting,
  cb.studentid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_lid,
  case when c.voorraad > 0 then
    (
      select 
        sum(prijs_niet_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_niet_lid
from cursusdienst c
join cursusdienst_bestellingen cb on cb.cursus_id = c.id
where cb.datum_afhaling is not null 
  and cb.datum_afhaling_mail is null 
  and cb.datum_verwijderd is null 
  and cb.studentid = '$studentid'
  and c.id = '$cid'
                 ";
         $res = mysql_query($sql) or die (mysql_error());

         //$subtotaal1 = '';
         //$totaal1 = '';
         //$btw1 = '';

         while($row = mysql_fetch_assoc($res))
            { 
               $cursus_id1 = $row['cid'];
               $studierichting1 = $row['cbstudierichting'];
               $voorraad1 = $row['voorraad'];

               if ($num_rows_lid > 0) {
                   $prijs1 = round(number_format(($row['cbprijs_lid'] / 1.21), 2, '.', ''), 2);
               } else {
                   $prijs1 = round(number_format(($row['cbprijs_niet_lid'] / 1.21), 2, '.', ''), 2);
               }

               $artikel1 = $row['cbartikel'];
               $aantal1 = '1';

               $subtotaal_lid += number_format(round(($row['subtotaal_lid'] / 1.21), 2), 2, '.', '');
               $totaal_lid += number_format($row['subtotaal_lid'], 2, '.', '');
               $btw_lid = round(number_format(($totaal_lid - $subtotaal_lid), 2, '.', ''), 2);

               $subtotaal_niet_lid += number_format(round(($row['subtotaal_niet_lid'] / 1.21), 2), 2, '.', '');
               $totaal_niet_lid += number_format($row['subtotaal_niet_lid'], 2, '.', '');
               $btw_niet_lid = round(number_format(($totaal_niet_lid - $subtotaal_niet_lid), 2, '.', ''), 2);

?>
                  <tr>
                    <td class="item-col item">
                      <table cellspacing="0" cellpadding="0" width="100%">
                        <tr>
                          <td class="product">
                            <span style="color: #4d4d4d; font-weight:bold;"><?php echo wordwrap($artikel1, 20, "<br />\n"); ?></span>
                          </td>
                        </tr>
                      </table>
                    </td>
                    <td class="item-col quantity aantal">
                      <?php echo $aantal1; ?>
                    </td>
                    <td class="item-col">
                      <?php echo '€ '.($prijs1 * $aantal1); ?>
                    </td>
                  </tr>



<?php
         //$sql = "UPDATE cursusdienst_bestellingen SET datum_afhaling_mail = NOW() WHERE cursus_id = '$cursus_id1' AND datum_verwijderd IS NULL AND studentid = '$studentid'";
         //$res = mysql_query($sql) or die (mysql_error()); 

 } } } ?>


<!--
                  <tr>
                    <td class="item-col item">
                      <table cellspacing="0" cellpadding="0" width="100%">
                        <tr>
                          <td class="product">
                            <span style="color: #4d4d4d; font-weight: bold;">Pink Shoes</span> <br />
                            Newest styles
                          </td>
                        </tr>
                      </table>
                    </td>
                    <td class="item-col quantity aantal">
                      1
                    </td>
                    <td class="item-col price">
                      $10.50
                    </td>
                  </tr>
-->

                  <tr>
                    <td class="item-col item mobile-row-padding"></td>
                    <td class="item-col quantity"></td>
                    <td class="item-col price"></td>
                  </tr>

<?php
         if($num_rows_lid > 0) {
             $subtotaal = $subtotaal_lid;
             $btw = $btw_lid;
             $totaal = $totaal_lid;
         } else {
             $subtotaal = $subtotaal_niet_lid;
             $btw = $btw_niet_lid;
             $totaal = $totaal_niet_lid;
         }
?>
                  <tr>
                    <td class="item-col item">
                    </td>
                    <td class="item-col quantity" style="text-align:right; padding-right: 10px; border-top: 1px solid #cccccc;">
                      <span class="total-space">Subtotaal</span> <br />
                      <span class="total-space">BTW</span>  <br />
                      <span class="total-space" style="font-weight: bold; color: #4d4d4d">Totaal</span>
                    </td>
                    <td class="item-col price" style="text-align: left; border-top: 1px solid #cccccc;">
                      <span class="total-space"><?php echo '€ '.$subtotaal; ?></span> <br />
                      <span class="total-space"><?php echo '€ '.$btw; ?></span>  <br />
                      <span class="total-space" style="font-weight:bold; color: #4d4d4d"><?php echo '€ '.$totaal; ?></span>
                    </td>
                </table>
              </td>
            </tr>
        </table>

Thorsten Kettner over 7 years

It seems you don't know how to apply the subqueries. You are selecting from the same tables again, even giving them the same alias names and you are not relating them to your main query. For one row in your results (which is mainly a cursusdienst_bestellingen record), what sums do you actually want to show? What is a subtotal_member and a subtotaal_niet_lid? Are these two values supposed to be the same in every result row or do they depend on the cursusdienst_bestellingen shown?
Thorsten Kettner over 7 years

Maybe you could show some sample data and expected results, so we see what the sums cover.
Brecht27 over 7 years

The only thing the SUM must do is when a user is a member, subtotaal_lid is showing me the correct totalprice without the items where the stock (= voorraad) is empty.
Brecht27 over 7 years

subtotal_member is the same as subtotaal_lid (in dutch). This must be the totalprice of the column prijs_lid where the stock is above 0. All other items in the list where the stock is below the 0 must be removed from the totalprice_lid.
Thorsten Kettner over 7 years

So these two values will be the same in every result row, yes? It doesn't matter what cursusdienst_bestellingen you are showing, you want to show with it the totals over all cursusdienst_bestellingen, yes?
Thorsten Kettner over 7 years

Or the total for all cursusdienst_bestellingen with the same cursus_id as the record shown?
Brecht27 over 7 years

i want to show the totals over all cursusdienst_bestellingen where the stock is above 0 per cursus_id. Every cursus_id is an article (or a course) in the table cursusdienst so if i made the sum for every cursus_id they are ordered and where the stock is above 1 per article it would be ok.
Thorsten Kettner over 7 years

I still don't get it. Again: please show some sample records, let's say three cursusdienst, ten cursusdienst_bestellingen and the results that you expect.
Thorsten Kettner over 7 years

Well, you select only one c.id = '$cid' anyway. So if I set $cid = 1 then of course the second records have nothing to do with my results anyway. Correct? So the sample doesn't really help much. BTW: Is it on purpose that you consider different records in your main query and the subqueries (datum_reservatie_mail is not null only in subqueries, datum_afhaling is nullin subqueries and datum_afhaling is not null in main query)?
Brecht27 over 7 years

$cid is coming from a foreach loop. On the frontend website the student can select with checkbox a couple of courses. So the foreach loop is foreach ($cursus as $cid) where the $cursus is the post from the form. The datefields are correct in the query. Some datefields were updated after a mail has send to the student.
Thorsten Kettner over 7 years

Anyway, you have writen a query to get you rows for exactly one c.id. So in the loop where $cid is 1, the query looks at entries for c.id 1. Entries for c.id 2 are not relevant in that instance of the query, right? So even if voorraad for c.id 2 were +10, this wouldn't change the results for $cid 1. Correct?
Brecht27 over 7 years

The mail query is working correct so the output is a table were i see the records where the stock is above 0. All other records are not shown. But the subquery is the only thing who has calculated the totalprice for the same records where the stock is above 0. And in my posted query it is not. I have the totalprice of every records also the ones where the stock is below 0.
Brecht27 over 7 years

Yes it is correct. The query is inside the foreach loop so for every c.id (in $cid) the result are filled in the output table. I will edit my post with the output results now.
Thorsten Kettner over 7 years

And please, please, please show sample data that makes sense. One record in each table for a $cid doesn't elaborate anything. Show more data plus the result rows you want to see. And again: is it on purpose that you look at different records in your main and your subqueries? They consider different datums.
Brecht27 over 7 years

I have updated my original post with the result rows i want to see. The article output (the first tabel invoice) is correct in my output. But the second (totalprice) table shows me the wrong results. I had 54.00 there instead of 24.00 for the subtotal. The datefields are different in the main query and the subquery. That is correct because some datefields are changed after sending a invoice mail to the student (like datum_afhaling_mail and datum_afhaling - these are the dates where the fields are updated after sending the mail to the student where the stock is above 1.)

Brecht27 over 7 years

I have the following error: Column prijs_lid in field list is ambiguous if i use (SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid . . . ) And what about the subtotaal_lid i have twice?
Brecht27 over 7 years

I have edited my query in English so it's better to understand for everyone
Brecht27 over 7 years

Can you see the problem in my query here? I can't see it.
Xenos over 7 years

Yes, I see THEN prijs_lid with no table alias, so there must be two columns of that name in the tables involved in your query. Prefix prijs_lid with the table you want to use value from. But like MySQL, I cannot tell you which table you have in mind.
Brecht27 over 7 years

If i use cb.prijs_lid (what is must be) then i have ambiguous error. What is see is that i have subtotaal_lid twice so if i take the first item to subtotaal_lid1 and the second (at the end of the select) as subtotaal_lid the error is gone but the result is not good. What i want to do here is to have the total price of all items without the ones where the stock is below 1. So i take here c.voorraad > 0 but the result is always the totalprice with all items and not only the ones who has a stock above 0.
Xenos over 7 years

There may be multiple ambiguous columns since you have a lot of colnames without their table prefix.
Brecht27 over 7 years

I have solved that issue but the result is still the same. See my original post for update.
Brecht27 over 7 years

Can anyone help me out here?
Xenos over 7 years

Remove the nested SELECT and try your query again to check if the issue comes from joins (if so, remove those nested SELECT from your question). And again, alias every table to make things non ambiguous (are more robust in time)
Xenos over 7 years

Ah, sorry, I though it was someone's else edit (and the goal was to leave the ? in the answer since neither me or MySQL can tell the correct table name). And with all table aliases everywhere, you still have "ambiguous column"? Can you give the exact error message then?
Brecht27 over 7 years

Are you asking me to change the ? in your post? Or something else? I can change the code myself of course but you don't know the correct alias then. What are you want me to do here?
Brecht27 over 7 years

I have edited my original post above with an example
Brecht27 over 7 years

What i have now with your first posted query is that the subtotaal_lid = the totalprice of all records and not only the ones who has voorraad > 0. So it is the same result i had before.
Brecht27 over 7 years

I have posted the total code (original post) with the tables inside (html) and the query so you can look of there is something wrong with the subtotal.
Thorsten Kettner over 7 years

The result contains rows. Each row is one cb with its one associated c. voorraad is a column in c. So either the row shown has voorraad <= 0, then 0 is shown. Or it has voorraad > 0 then all cbs for this c are summed up. In that sum there can never be records with voorraad <= 0, because they don't have a column voorraad; they belong to the c that has voorraad > 0. You are getting confused somehow and I don't know where.
Brecht27 over 7 years

I my output i see that the totalprice is the total of all selected rows (also the ones with voorraad <= 0)... I don't know why... The first table with the course items (artikel - aantal - prijs) are correct. I have posted the whole output code with queries inside. Is there something wrong?
Thorsten Kettner over 7 years

It should not be the total of the selected rows, because you select rows where datum_afhaling is not null whatever datum_reservatie_mail, but you sum records where datum_afhaling is null and datum_reservatie_mail is not null. If you would look at the same dates, then yes, the sum would equal the total of the selected rows, because both contain records for the same cursus_id and studentid.
Brecht27 over 7 years

I would look to my datefields of there is something wrong in the subquery. If it is not, i will come back here.
Brecht27 over 7 years

I think my datefields are different between the subquery and main query. The result is still wrong. I don't know why.
Brecht27 over 7 years

Have you seen my HTML output for that? Maybe there is something wrong in it? Is it possible?