I wouldn't force him to play flag football if he doesn't want to. But I would be honest about why you're saying "no" to tackle football. If you have researched concussions and traumatic brain injuries, especially in the growing brain, then tell him. Youth soccer leagues aren't even letting kids head the ball until age 14 or so, and tackle football injuries are much worse. If you have talked to the pediatrician, tell your son that. (If you haven't, please do so.)
I think starting out with football after no experience whatsoever on any type of sports team is pretty tough. Specialized positions with specialized skills, tons of drills, and so on is a lot for a 9 year old to understand if he's never done anything before. At least on soccer (and basketball) teams, everyone learns to kick (or dribble) and pass, and there's more action on an ongoing basis.
Find out from him what the appeal is. Does he just like the idea of knocking people down? Does he think that's all the game is? (A lot of kids do - that's why I ask the question.)
But the bottom line is, you are the parents. You're going to make decisions he might not like. But it's best if you can explain your rationale.