SQL Server Management Studio Startup Time
Note: This posting only applies to Management Studio 2005. We have corrected the behavior for Management Studio 2008 thus there is no special configuration necessary.
One of my co-workers recently responded to a problem a user was having with SQL Server Management Studio (SSMS). The user was experiencing extremely slow startup of SSMS – a couple of minutes. The potential cause for the problem is not apparently obvious, but could impact tons of installations.
One issue that can cause this problem is that if the server does not have access to the internet, then the .NET framework can't access the crl.microsoft.com website to verify that the digital signatures used to sign the binaries for managed applications are valid. Each certificate check has a 15 second timeout in the .NET runtime implementation. Depending on what features are installed, this can add up to a minute of startup time for Management Studio.
There are a couple workarounds:
1) Configure a proxy server to allow access to crl.microsoft.com from your server
2) Configure your firewall to return a failure status quickly when it blocks access to the crl.microsoft.com website
3) Disable checks for certificate revocation. You can do this using Internet Explorer by opening the Internet Options dialog, going to the Advanced Page, and then un-checking the "Check for publisher's certificate revocation" checkbox. There are fraudulently signed binaries in the wild that can make virus-infected applications look like they were published by Microsoft. Disabling this check should probably not be done on machines with internet access.
Comments
Anonymous
August 23, 2006
Why cannot this be done locally?Anonymous
August 23, 2006
Is it reported to Microsoft and will it be fixed ?Anonymous
September 06, 2006
Wow! If I understood what you said, the current version of .NET needs access to the Internet (for real or by proxy). Not having it can slow down Management Studio startup time. Doesn't seem too good for a developer using a Laptop in an Airport (maybe in disconnected mode).
BillAnonymous
September 20, 2006
Add a host entry in your hosts file.
This will cause certificate revocation not to be be checked at crl.microsoft.com
Also this is system wide and not user specifict like the IE settings.
I don't realy understand the use, when it can't reach the server it's all ok anyway.
So why not let it be disabled in SQL 2005 alone maintaining your safety in browsers.
Add this text in your hosts file under system32driversetc
127.0.0.1 crl.microsoft.com
Works for us.Anonymous
February 14, 2007
I think its shameful that so many applications, especially those written in .NET, run many times slower than apps released 5 years ago. Microsoft just has to keep soaking up that cpu to ensure the world keeps buys more pcs = more windows licenses. Sql Server 2000 finally became useable in 2005 when performance of servers and pcs got to circa P2500/1GB ram, and now they've made it unuseable once again. Without applying this tweak sql 2005 takes 60 seconds to start on my laptop, and about 15 seconds with the tweak applied. It should start in < 1second.Anonymous
February 16, 2007
Thanks, I had the same problem! I spend so much time looking for the answer!Anonymous
February 21, 2007
http://support.microsoft.com/kb/555686. It sets HKEY_USERS[SID]SoftwareMicrosoftMicrosoft SQL Server90ToolsShellHelpUseOnlineContent and HKEY_USERS[SID]SoftwareMicrosoftMicrosoft SQL Server90ToolsShellHelpUseOnlineContentUseMSDNOnlineF1 to 0 from 1. What do you think, can it be set in HKEY_USERS.DEFAULT, too? Erik: http://www.google.hu/search?hl=hu&q=sql+management+studio+starts+slow&meta= ;) Google Is Your Friend :)) BTW MS KB article had been posted on August 8, 2006. :-)Anonymous
April 10, 2007
PingBack from http://gubus2.wordpress.com/2007/04/10/sql-server-management-studio-lassu-indulas/Anonymous
May 10, 2007
I've been for week with this annoyance. None of your tips worked, BUT when I was looking in the registry I found something... This keys were pointing to a folder in machine that no longer is in my network !!! HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server90ToolsShell HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server90ToolsShell I changed both to a local directory, and now it's opening in less than five seconds !!! Thanks for the hint !!!Anonymous
May 16, 2007
THANK YOU! We spent days working with a frustratingly sluggish system only to discover that this was the source of our issues. In our case, it not only caused SQL MS to open slowly but it caused Identity Lifecycle Manager 2007 rules extensions to crash and timeout during sync runs (compiled in .NET 2.0 CLR). Option #3 solved the problem! Brad Turner MIIS MVPAnonymous
May 17, 2007
Link back: http://idchaos.blogspot.com/2007/05/miis-sp2ilm-2007-timeouts-out-of-memory.htmlAnonymous
July 19, 2007
The comment has been removedAnonymous
July 28, 2007
This issue is covered pretty well by blogs in the context of SSMS ( Dan Jones and Euan Garden ) and inAnonymous
July 28, 2007
This issue is covered pretty well by blogs in the context of SSMS ( Dan Jones and Euan Garden ) and inAnonymous
August 16, 2007
I am astounded that Microsoft insists on doing this. SQL is an enterprise product, and I don't know a single enterprise technician or security analyst that would allow a database server of any sort access to the internet, direct or otherwise. In fact, no server should be allowed access to the internet unless it is one of it's specific tasks. I would imagine that the hacking community are having a field day with the knowledge that Microsoft servers are operating like this.Anonymous
January 22, 2008
Проверка аннулирования сертификата может порождать некоторые задержки в работе SQL Server 2005 ManagementAnonymous
January 25, 2008
Thanks for issue. Resolved in my case.Anonymous
March 12, 2008
thanks for this! it works like lightning now. am really gratefulAnonymous
May 14, 2008
PingBack from http://alonzo.mediaplusnews.info/sqlservermanagementstudio.htmlAnonymous
June 03, 2008
it seems that the new patch 3054 from Microsoft for SQL 2005 also fixes the issue for the slow start of the SSMS.Anonymous
July 24, 2008
The comment has been removedAnonymous
September 30, 2008
Hello Eric, thank you for your issue, it resolved my case! Before the etc.host was modified: 1 minute to run Management Studio now: 1-3 seconds WOW:)Anonymous
October 02, 2008
We have a client that experiences this certificate verification problem using a 3rd party application (not SQL Server Management Studio) and will try the hosts change. But, we have the same software installed on our test servers and on the developer's servers and don't experience this attempt to get out to the internet. We have sniffers and have checked. So, why does one system do this verification and others not? There must be a setting that is causing this verification to occur. We have checked the internet explorer settings and they are the same. We have checked other local policy settings relating to certificates and they are the same. I'd prefer to find out why it is verifiying rather than just diverting the check or implementing a workaround. Any ideas? Thanks.Anonymous
October 04, 2008
Hi Mark, The best thing to do is contact the vendor for the application and ask them about this problem. There may be some configuration tucked away somewhere the app is using to determine how to do certificate verification. Cheers, DanAnonymous
October 20, 2008
Thanks Dan. We are in contact with the vendor however they also do not know. We have tested a few workarounds (including the hosts change) and the only one that works is to change Internet Explorer security settings to NOT "check for publisher's certificate revocation". Still don't know why it is checking for publisher's certificate revocation when other installations of the same software do not. The problem occurs when the app is run from a client PC/laptop or from the server. Could their server have some bad certificates loaded? Any other ideas? Thanks, Mark.Anonymous
October 26, 2008
Hey Mark, I'm sorry but I'm not an expert in this space. Honestly your best bet is to keep working it with the vendor. DanAnonymous
July 10, 2009
AHA! I was dealing with FIM2010 RC0 with the Password Reset workflow (just getting setup) and was down to everything working EXCEPT after password reset wouldn't go through. Checked logs on ILM and found an MMS fail when I tried. Took a shot and did option #3. Now of course I'm running about a billion updates to the Server 2008 Enterprise ILM server. Either we're blocked or my certs are out of date. Hopefully the updates fix this :) THANKS OH MIGHTY ILM MVP'S. YOU MADE THE DAY!Anonymous
September 10, 2009
Dan, Can this also my issue where my vb app works fine until customer's internet is down? Then I am getting a timeout error on my login query (first sql connection)? But, if is a sql startup issue, not just management studio, then my app should have same error running it on the server. But, customer THINKS it's okay on server, but will not run from workstations. Thanks,Anonymous
September 19, 2009
In general this shouldn't impact any app that connects directly to SQL Server. In other words, this doesn't have anything to do with the DB Engine or establishing a connection to the DB Engine. It has everything to do with Management Studio. If your app cannot connect from a remote machine there are a number of things that you need to consider: firewall settings, port settings, default vs. named SQL Server instance name, protocols enabled (e.g. TCP/IP), etc.Anonymous
December 16, 2010
Cool!!