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
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:
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!