Step 1: prepare answers to "obvious" questions.
Note that these aren't questions that we need answered in order to answer your question, but that probably need to be answered within your game world.
Note also that the answers to these questions may be "I don't know yet" - or, potentially more interestingly, "the characters don't know yet", and may lead to great arcs as the party tries to grapple with the issue or resolve complications it may bring.
Question 1: is this thematically appropriate?
Why is this ability appropriate for this character? Are they generally hot-tempered or are they usually calm?
Is this a racial feature? If so, why is it thematically appropriate for tieflings, generally?
Question 2: why fireball?
Has the character chosen fire-themed spells or otherwise leaned into a fiery motif? Do tieflings in your world generally have a fire motif?
Question 3: can the player influence the roll?
If this keys off of emotional state, is there anything that the player can do to prevent - or encourage - a fireball "mishap"?
Can they use advantage (eg., from Inspiration) to roll twice? If so, can they choose to take the lower if they really don't want fireball going off?
Can they use other floating bonuses (eg., Guidance) to affect the roll?
Can they take an action of some sort to center themselves to avoid (or intentionally trigger) a fireball? Previous editions have had full-round actions, which take both your action and your movement for the round; could the bard trade their movement (and/or bonus action, I suppose) to nudge the roll in their favor?
Question 4: why does this happen?
You suggested that this is a loss of control of "magical abilities or emotions". Which is it? How can the character learn to keep control? Why can't this character (or tieflings in general) keep control enough to cast like every other caster in the world?
Question 5: does this scale with the spell slot used?
At first level, getting 8d6 surprise damage is great against goblins. At 15th level, or if triggered by an up-cast spell, that could be quite disappointing.
Step 2: get buy-in from the player.
This feels like a major change to the character's abilities; such changes should generally get buy-in from the player. Without that buy-in, the player may feel frustration and/or a loss of agency, possibly enough to want to play a different character or even leave the group.
Story time: I was in a short-lived 3.5e campaign in which this happened to a player who chose a minotaur (which was otherwise appropriate for the campaign); they accepted that minotaurs were "second class citizens" in most of the world, and that theirs had been recently freed from slavery (the details were murky about exactly how that happened). The GM then decided that the minotaur's horns would have been removed by their last owner (thus also removing their gore attack). The player was ... unimpressed, to say the least. In that particular case, the GM was full of other failings, so it's not surprising that the campaign fell apart after just a few sessions, but the horn issue didn't help anything.
And, to be frank, this player would almost certainly bail from the character - and maybe the campaign - if this change were forced.
Step 3: get buy-in from the rest of the players.
This feels like a big change to the character's abilities, and one that the other characters should become aware of fairly quickly (especially if it's a racial thing, not just this one tiefling). Why would a band of adventurers accept a team member that not only makes some NPCs more difficult to work with but also has a chance of accidentally frying them in combat?
IME, most players will bend over backwards to come up with an in-universe justification for their characters doing pretty much anything that forwards the campaign, from working with "undesirables" to parlaying with a pit fiend. But, each player has a limit. Talk to the players to make sure that this doesn't cross any of their limits.
FWIW, this player would put "fixing the bard" at the top of the priority list.
Step 4: don't worry about balance too much.
Skimming through the bard spell list again, there aren't a huge number of bard spells that require a spell attack roll. My suspicion is that, unless the player is trying to trigger this ability, it'll only come up once or twice in the whole campaign.
Most of my experience is in 3.5/PF, but the 5e campaigns I've been in feel close enough that I think this is relevant:
In my experience, one session will average 2-3 "major" combat encounters (that is, big enough to bring out the map and the minis - and I love me a good map and some minis). A combat encounter averages something like 4 rounds, so that's 8-12 rounds of combat per session. The longest single campaign I've been a part of lasted about 5 years, meeting twice a month on average (we had a player with an obnoxious work schedule, which didn't help that). So, 26 sessions a year - call it 30 for easy math. 30 sessions a year * 5 years = 150 sessions or 1,500 combat rounds. Ballpark, casters cast in, say, 2/3 of those rounds, so about 1,000 spells cast per caster; maybe half of which are AoEs. So, down to about 500 targeted spells cast.
If a short rest resets the chance of casting fireball, that will probably more-or-less counter the increased chances that come with casting more spells - at least, for this kind of back-of-the-envelope calculation. Given that, roughly 1% of targeted spells will spontaneously become fireball - that's about 5 castings over a 5-year-long campaign, very roughly.
If a long rest resets the chance, but a short rest doesn't, I'd about triple the chances and ballpark maybe 15-20 castings over that same 5-year campaign.
The 5e bard spells I've scanned through would reduce those chances a fair bit: lots of AoEs or "choose a creature" without a spell attack roll.
This GM would expect the bard to accidentally pop a fireball maybe once or twice over the course of a campaign. Honestly, getting a free wish once or twice over the course of a campaign probably won't affect game balance meaningfully.
A suggested adjustment to wording.
This GM would suggest wording the ability as:
Whenever they cast a spell [using a spell slot?] that requires a spell attack roll, Tieflings have a cumulative 1% chance of accidentally casting Fireball instead of they spell they chose. The fireball is centered on the creature or point that the original spell targeted. The chance of mis-casting resets when a spell is mis-cast or after a short or long rest.
Using terms like "DC" and "success" or "failure" suggests that this is something that the character/player has some control over or may want to have happen. As a player, I would rarely want this to happen; for me, succeeding would almost always be rolling a "failure".