A man named Noel came into possession of the horn after unearthing it from a desert in Saudi Arabia. At some point in his life, Noel made a deal with the demon Crowley for all the knowledge in the world. After ten years, he realized the gift was not worth it. He became sick. He summoned Crowley so they could trade the horn for the cancellation of Noel's contract.
After Crowley sees the horn, he snaps the contract, but also snaps Noel's neck and takes the horn for himself. He contacts the Winchester brothers in hopes of using the weapon against Lucifer.
Sam and Dean decide to use the horn against Amara instead, but during their attempt to expel Lucifer from Castiel's vessel, the archangel breaks free from his bindings and takes the horn for himself. He then tries to use the horn's power against Amara, but the weapon proves ineffective.
The lore about the Horn of Joshua contained in the story of Jericho, which is told in Joshua 6:1-27.
The first five books of the Hebrew Bible tell how Noah cursed Canaan to become a slave, and how God gave the land of the Canaanites to Abraham and his descendants. The children of Israel (descendants of Abraham) themselves became slaves in Egypt, but through Moses God brought them out of Egypt and to the borders of the promised land of Canaan. There Moses instructed them to seize the land by conquest, and placed them under the command of Joshua.
Joshua sent spies to Jericho, the first city of Canaan to be taken, and discovered that the land was in fear of Israel and their God. The Israelites marched around the walls once every day for seven days with the priests and the Ark of the Covenant. On the seventh day they marched seven times around the walls, then the priests blew their ram's horns, the Israelites raised a great shout, and the walls of the city fell. Following God's law of herem the Israelites took no slaves or plunder but slaughtered every man, woman and child in Jericho, sparing only Rahab, a Canaanite prostitute who had sheltered the spies, and her family.