First off, if you want accurate color rendition on your screens, you need to properly calibrate them. Regardless of what configuration you may apply to any tool, without a properly calibrated screen, its all kind of moot. You can get hardware calibrators like the Spyder 3 from DataColor or the iOne or ColorMunki from X-Rite. All of them will do a great job properly calibrating your screen. Its very important that you never use any of the standard color gamuts (sRGB, AdobeRGB, ProPhotoRGB, etc.) as your monitors ICC profile. Those color profiles are not intended nor designed to be used directly with a computer screen, and will usually do more harm than good. You MUST calibrate your computer screen to tune it for proper, accurate color rendition. Calibration will generate a custom ICC profile tuned specifically to your computer screen, graphics card, and possibly the ambient lighting you normally work in.
Once calibrated, and once the correct ICC color profile is assigned to your screen in windows, the color rendition of untagged or sRGB tagged images should be much more consistent across applications. Photoshop has its own ICM engine, and if that is selected, it may look slightly different than other applications that use the Windows ICM engine (precision and algorithmic differences, generally nothing huge.) Applications that never use any ICM at all will usually only render untagged or sRGB tagged images properly (as if they were all sRGB images)...any images tagged with other ICC profiles will usually look incorrect in such applications (the notable examples are web browsers.)
When processing photos, its best to keep the photo in the largest gamut possible (i.e. ProPhoto RGB or Adobe RGB) until such time as you need to work in your target gamut. By default, Camera RAW/Lightroom assume ProPhotoRGB until you convert. For most things, sRGB is probably the final target gamut. If you intend to publish your photos online, convert a copy of your photos to sRGB and do all the final work there. If you are sending your prints off to a print lab, you may want to use AdobeRGB. Modern ink jet printers have greatly expanded gamuts, and all of the latest printers from Epson and Canon, as well as some newer commercial Giclée printers, support gamuts that cover most of AdobeRGB, and in some cases a little beyond (i.e. many Epson printers support more vibrant magenta hues than AdobeRGB, and Canon printers tend to support more vibrant greens.) If you know your printer uses a wide gamut printer, convert to AdobeRGB and do your final proofing in that gamut. Otherwise, its again best to use sRGB.
When it comes to RAW images...unprocessed RAW images generally do look a bit dull. Camera sensors have very high dynamic range and very high color precision...higher than most computer screens. As such, its impossible to render on screen the native color saturation and dynamic range of a RAW image like Nikon's NEFs. You will have to tune exposure, contrast, and maybe even saturation a bit in Camera RAW to draw out the color that is there, and attenuate the available DR to give your photos the pop you want them to have.
Its important to keep in mind, if you intend to print any of your photos, that printers generally have much lower dynamic range than either a computer screen or a DSLR. In a realistic scenario, you might get 5 stops of DR (at most around 7 with the right kind of paper and the best inks available today), compared to the 8 stops for a normal computer screen (10 stops for the best), and 12-14 stops from a DSLR. You will probably need to fine-tune the white and black points and possibly even color gamut of your images to get them to print properly and look similar to what you see on screen.
