This blog is an outcome of one of internal email thread where SQL Server Management Studio was allowing to connect only “Database Services” and “SQL Azure”. A picture is worth a thousand words, so here it is:

Analysis Services, Integration Services, Reporting Services are grayed out/disabled. I thought that this might be due to SSMS Express but looking at “Help” > “about” in Management Studio showed that it was full version.

“Copy Info” gives you this information.

Microsoft SQL Server Management Studio                        11.0.2100.60
Microsoft Analysis Services Client Tools                        11.0.2100.60
Microsoft Data Access Components (MDAC)                        6.1.7601.17514
Microsoft MSXML                        3.0 6.0
Microsoft Internet Explorer                        8.0.7601.17514
Microsoft .NET Framework                        4.0.30319.237
Operating System                        6.1.7601

While doing some more research, I remember setup screen had option for Management Tools – Basic and Complete. here is the screenshot of setup screen with both features highlighted.

I guess the feature description explains the behavior. If you want to find what was the option you selected, then SQL setup logs are your friend! [One more trick at the end of the blog]

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\DateTimeStamp\ConfigurationFile.ini

To repro the issue, I selected only Basic and this is what you would see in ConfigurationFile

FEATURES=SQLENGINE,REPLICATION,SSMS

Once setup completes, you would see same behavior which for which I am writing the blog. Now, to fix the issue we need to add “Management Tools – Complete”. Launch “SQL Server Installation Center” > Installation > “New SQL Server stand-alone installation or add features to an existing installation” . Go through the Wizard and choose as below:

and then

Now you should see FEATURES=ADV_SSMS in ConfigurationFile.ini and once you finish the setup, you should be able to select any option.

If you are not comfortable looking at Setup Logs, you can also use “Installed SQL Server features discovery Report” from “SQL Server Installation Center” > “Tools” Menu

Once you click on hyperlink and launch the report, it would show something like below.

So after Adding Management Tools Complete, I could see that in report.

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani