Need a way to connect to a webservice I have a lot of data that sits outside of Craft (the data is in MS SQL Server) and I plan on writing a simple .NET Service which will return JSON. I need to display this data but not sure on how to connect to a service using Craft CMS.

Are there any clients (plugins) which will connect to a service get the JSON data and display it?
Craft 2.x includes Guzzle 3 natively, so you could write a Craft plugin that uses it to consume your web service.

There are plenty of examples in the Guzzle 3 docs on how to do that (as well as in Craft itself).

You could also use something like this: https://straightupcraft.com/craft-plugins/guzzle

Which is a wrapper for Craft's Guzzle 3 you can use from a template.