The unfortunate thing about that is that it's wrong. I don't mean the usage, I'm British so I'm firmly 'lots of Lego', but it's obviously a noun. Using it to describe a brick etc. doesn't preclude that. (It's 'attributive', or 'adjunct'.)
In “SQL Server”, “SQL” functions as an adjective modifying “Server”; its not unusual for what are normally nouns to do that, distinguished only by position(e.g
, “hat” in “hat rack”.)
Wikipedia says [0] 'adjectival noun' was 'formerly synonymous, but now usually means an adjective used as a noun'.
Regardless, it's not an adjective, much less 'always' - it's always a noun; sometimes used attributively ('adjectivally' if you like) / as a noun adjunct, such as in 'Lego bricks'.