Why was the Bay of Pigs an embarrassment for the United States?
It was unsuccessful.
Bad planning, limited resources and an alert and resourceful enemy doomed the invasion. There was supposed to a general uprising in response to the invasion that never developed. The event solidified Castro's control on the country. It also led to the Cuban missile crisis. Internationally and domestically it was a black eye for Kennedy.
The main appeal of the Bay of Pigs as an invasion point is that it is remote. It is, other than that, overly shallow. The USN destroyers which were the main U.S. support of the invasion could not get in close to bring their guns into action or to rescue the invasion force if it was discovered. It was discovered almost immediately and was swarmed by Cuban volunteers and Army. The US supplied no air cover and the supporting supply ships were sunk by the small Cuban Air Force.
Eisenhower originally ordered the CIA to organize the invasion. President Kennedy had to deal with the actual landings and the aftermath. Kennedy was lukewarm about the project in the planning stages. He took responsibility for the invasion when it went bad.
There are tours available to the Bay of Pigs. It is shallow and sandy and a bit over grown. Other than historical interest there is limited appeal. Other places in Cuba worth the time.