How to rank SQL Server table entries by field criteria using primary key

I have an Access database that uses a SQL Server back end, and I have written a query that ranks entries in the Panels table by AgreementID column, using the PanelID primary key to determine ranking.

The query in MS Access is this:

SELECT 
    X.AgreementID, X.PanelID, X.Panel_TypeID, 
    ((SELECT COUNT(*) 
      FROM Panels 
      WHERE (PanelID < X.PanelID) 
        AND (AgreementID = X.AgreementID);) + 1) AS Rank
FROM 
    Panels AS X
WHERE 
    (((X.AgreementID) IS NOT NULL))
ORDER BY 
    X.AgreementID, X.PanelID;

The output from this looks like this:

As you can see, each entry related to the AgreementID is ranked according to its PanelID.

The problem I have is there are 20k rows and it takes too long to run in Access, so I was hoping to recreate this in the back end in the hopes that it would run faster than using linked tables in the front end.

The method above doesn't seem to be supported in SQL Server, so I need some pointers to help me solve this.

Running a simple subquery like that should work fine in SQL Server. Assuming your back-end table has the same schema as your Access table, try removing a few of the parentheses and running the same query:

SELECT 
    X.AgreementID, 
    X.PanelID, 
    X.Panel_TypeID, 
    (
    SELECT 
        COUNT(*) 
    FROM 
        Panels 
    WHERE 
        PanelID < X.PanelID
        AND AgreementID = X.AgreementID
    ) + 1 AS Rank
FROM 
    Panels X
WHERE 
    X.AgreementID IS NOT NULL
ORDER BY 
    X.AgreementID, 
    X.PanelID
;