Much of the popular sense of the relationship between Kennedy and the CIA has been tinged by the persistent (and, in my view, ludicrous) conspiracy theories about the Agency's alleged involvement in his assassination. In fact, I'd characterise the relationship more in terms of tension between an inexperienced president with a broad and aggressive foreign policy agenda, and the entrenched leadership of an agency with an unfortunate predilection for short-sighted, amateurish adventurism in its covert actions.
The Director of Central Intelligence when Kennedy took office was Allen Dulles, a staunch Republican whose brother, John Foster Dulles, served as Dwight D. Eisenhower's Secretary of State from 1953-59. A veteran of the wartime Office of Strategic Services, Dulles was one of the pioneers of US covert action and paramilitary operations abroad, through the Office of Policy Coordination. In 1951, he was appointed Deputy Director for Plans, responsible for the CIA's covert action programme; months later, he was elevated to Deputy Director of Central Intelligence; in 1952, when Eisenhower took office, he was appointed Director of Central Intelligence, becoming the first civilian DCI. He viewed himself as one of the founding fathers of the CIA, and became a polarising figure. Like J. Edgar Hoover at the FBI, Dulles was a defining force in the early years of the Agency, overseeing many of the CIA's most controversial operations.
Operation Zapata, the invasion of Cuba at the Bay of Pigs in April 1961, was the most striking example of Dulles' CIA in action: a complex, overly ambitious plan that was driven by ideology and predicated on a laundry list of flawed assumptions, both strategic and tactical. The catastrophic bungling of the invasion caused the most significant breach between the CIA and the Kennedy administration: it was a very public failure that was hugely embarrassing for Kennedy personally, and that was to have profound long-term ramifications for US foreign policy. Kennedy's fury at the operation's failure was heightened by the fact that its planning had been initiated by his predecessor, Eisenhower, and his (accurate) belief that he had been poorly advised by his intelligence leaders.
In the aftermath of the Bay of Pigs, two official post-mortems both concluded that the operation had been a remarkable bit of overreach (both in terms of capability and jurisdiction) by the CIA, and that the Agency had badly and knowingly misled the administration about the operation's likelihood of success:
Less than a week after the invasion, Kennedy asked a former US Army chief of staff, General Maxwell D. Taylor, to lead an independent investigation into the fiasco. In June 1961, Taylor's Cuba Study Group concluded in its final report that "the impossibility of running Zapata as a covert operation under CIA should have been recognized and the situation reviewed", and that "the leaders of the operation did not always present their case with sufficient force and clarity to the senior officials of the Government to allow the latter to appreciate the consequences of some of their decisions."
In February 1962, the CIA's own inspector-general, Lyman B. Kirkpatrick, Jr., submitted a damning report in which he concluded that the CIA had "failed to recognize that when the project advanced beyond the stage of plausible denial it was going beyond the area of Agency responsibility as well as Agency capability"; that it "failed to appraise the chances of success realistically" and "failed to keep the national policy-makers adequately and realistically informed of the conditions considered essential for success."
Kennedy was livid with the CIA for having put the US government — and himself — in such a humiliatingly exposed position, and in late 1961 (before the Kirkpatrick report was even complete), he demanded the resignations of the Agency's three most senior officials: DCI Dulles, Deputy Director Charles Cabell and Deputy Director for Plans Richard M. Bissell. Kennedy briefly considered appointing his brother and Attorney-General, Robert, as the new DCI; eventually, he settled for giving Robert unofficial oversight of CIA covert action.
Dulles' successor as DCI, John McCone, worked hard to rebuild the relationship with the administration after the Bay of Pigs, and became personally close with Robert Kennedy. The CIA continued to be a major tool of Kennedy's foreign policy, and rebuilt some credibility with the White House in 1962-63 — not least by providing timely intelligence to Kennedy during the early stages of the Cuban Missile Crisis. Although aerial reconnaissance photographs from US Air Force U-2s and photographic analysis by Defense Intelligence Agency personnel provided the first warning of suspicious Soviet activity in Cuba, CIA analysts and human-source intelligence from Oleg Penkovsky, a CIA-MI6 mole in Soviet military intelligence, helped conclusively confirm that the USSR intended to emplace nuclear weapons in Cuba — thus preventing the Soviets from achieving a fait accompli, and setting the stage for Kennedy's successful handling of the crisis.
Vaguely conspiratorial postscript
While the Bay of Pigs was certainly the nadir of the relationship between the Agency and the Kennedy administration, it wasn't necessarily the first point of tension. Tim Weiner, a journalist who wrote Legacy of Ashes (a highly critical history of the CIA), contends that Kennedy had a rather suspicious relationship with both the CIA and FBI from the earliest days of his presidency:
President-elect Kennedy immediately announced the re-appointments of [FBI Director] J. Edgar Hoover and Allan Dulles. That decision came from his father, and it was made for political and personal protection. Hoover knew some of the deeper secrets in the Kennedy family—including the president-elect's sexual dalliances during World War II with a suspected Nazi spy—and he had shared that knowledge with Dulles. Kennedy knew all of this because his father, a former member of Eisenhower's board of foreign intelligence consultants, had told him on good authority.
The "suspected Nazi spy" Weiner is talking about was Danish journalist Inga Arvad, with whom Kennedy had a relationship in 1941-42 — you can read some of their correspondence at the Kennedy presidential library. Arvad has never been conclusively — or even particularly convincingly — shown to have been a German agent, but she was certainly seen in the 1930s and '40s as having been rather friendly with Nazi Germany.