Offhand, I'm thinking the problem is the use of the @Counter variable in the recursive part of the CTE query. Maybe something like this instead:

with cte as (

SELECT @Counter as c,ASSET_NUM,[SHAPE].STPointN(@Counter).STAsText() as Points

FROM [dbo].[GEOMETRY_TABLE]

where ASSET_NUM = 9782654

union all

SELECT cte.C+1,t.ASSET_NUM,t.[SHAPE].STPointN(cte.C+1).STAsText()

FROM cte inner join [dbo].[GEOMETRY_TABLE] t

on cte.ASSET_NUM = t.ASSET_NUM and t.ASSET_NUM= 9782654

where cte.C < 5

)

Or avoid recursion altogether and use a tally table to pass into STPointN. If you don't have a tally table, you could improvise a small one on the fly here like:

WITH Tally AS

(SELECT N FROM (VALUES (1),(2),(3),(4),(5)) AS list (N))

SELECT t.N AS c, g.ASSET_NUM, g.[SHAPE].STPointN(t.N).STAsText() AS Points

FROM [dbo].[GEOMETRY_TABLE] g

CROSS JOIN Tally t

WHERE g.ASSET_NUM = 9782654