In the first veto override of his presidency, the Senate voted 97-1 and the House of Representatives 348-77, meaning the bill becomes law.
Mr Obama told CNN the lawmakers had made "a mistake".
The president argued the bill could expose US companies, troops and officials to potential lawsuits abroad.
CIA Director John Brennan said the vote carried "grave implications" for national security, adding: "The downside is potentially huge."
The House and Senate unanimously passed the legislation, known as the Justice Against Sponsors of Terrorism (JASTA), this year despite the Obama administration's lobbying efforts.
The bill amends a 1976 law that shields other countries from American lawsuits, allowing victims' families the right to sue any member of the Saudi government suspected of playing a role in the 9/11 attacks.
Mr Obama argued in his veto that the bill would undermine US-Saudi relations and warned of tit-for-tat lawsuits against US service members in places like Afghanistan and Iraq.
He told CNN on Wednesday: "It's a dangerous precedent and it's an example of why sometimes you have to do what's hard.
"And, frankly, I wish Congress here had done what's hard.
"If you're perceived as voting against 9/11 families right before an election, not surprisingly, that's a hard vote for people to take.
"But it would have been the right thing to do."
White House spokesman Josh Earnest told reporters the vote was "the single most embarrassing thing the United States Senate has done" in decades.
But the measure's supporters contended the legislation only applies to acts of terrorism that have occurred on US soil.
"The White House and the executive branch (are) far more interested in diplomatic considerations," said Democratic New York Senator Chuck Schumer.
"We're more interested in the families and in justice."
- George W Bush vetoed same number of bills as Mr Obama, 12, but four were overriden
- Bill Clinton wielded the veto 37 times and was defeated twice
- Gerald Ford's veto was defeated 12 times
- The last US president to avoid a veto override was Lyndon B Johnson
- But the record is Andrew Johnson, who was repelled by Congress 15 times when president in the late 1860s
Mr Obama has issued 12 vetoes during the two terms of his presidency, but until now none have been overridden.
Democratic Minority Leader Harry Reid was the lone 'no' vote against the bill.
Democratic Senator Tim Kaine, Hillary Clinton's running mate, and Senator Bernie Sanders did not vote.
Fifteen of the 19 hijackers were Saudi nationals, but the oil-rich kingdom - a key US ally - has denied any role in the attacks, which left nearly 3,000 people dead.
While US intelligence raised suspicions about some of the hijackers' connections, no link has been proven to support claims that Saudi officials provided financial support to the suspects.