A couple of years ago I created a post with the major SQL version numbers. While working with a client this morning, I realized that I had not updated it to reflect several updates that have been released since the original post. Here is an updated table of major version numbers. To see all major and minor version numbers (i.e. versions for cumulative update versions), see this post. I’m also using this post to clean up some inconsistency in how the version numbers were listed in my previous post.
|SQL Version||Version Number|
|SQL Server 2012 RTM||11.0.2100.6|
|SQL Server 2012 SP1||11.0.3000.0|
|SQL Server 2008 R2 RTM||10.50.1600.1|
|SQL Server 2008 R2 SP1||10.50.2500.0|
|SQL Server 2008 R2 SP2||10.50.4000|
|SQL Server 2008 RTM||10.0.1600.0|
|SQL Server 2008 SP1||10.0.2531.0|
|SQL Server 2008 SP2||10.0.4000.0|
|SQL Server 2008 SP3||10.0.5500.0|
|SQL Server 2005 RTM||9.00.1399|
|SQL Server 2005 SP1||9.00.2047|
|SQL Server 2005 SP2||9.00.3042.01|
|SQL Server 2005 SP3||9.00.4035|
|SQL Server 2000 RTM||8.00.194.0|
|SQL Server 2000 SP1||8.00.384.0|
|SQL Server 2000 SP2||8.00.534.0|
|SQL Server 2000 SP3||8.00.760|
|SQL Server 2000 SP3a||8.00.760|
|SQL Server 2000 SP4||8.00.2039|
|SQL Server 7.0 RTM||7.00.623|
|SQL Server 7.0 SP1||7.00.699|
|SQL Server 7.0 SP2||7.00.842|
|SQL Server 7.0 SP3||7.00.961|
|SQL Server 7.0 SP4||7.00.1063|
|SQL Server 6.5 RTM||6.50.201|
|SQL Server 6.5 SP1||6.50.213|
|SQL Server 6.5 SP2||6.50.240|
|SQL Server 6.5 SP3||6.50.258|
|SQL Server 6.5 SP4||6.50.281|
|SQL Server 6.5 SP5||6.50.415|
|SQL Server 6.5 SP5a||6.50.416|
|SQL Server 6.5 SP5a Update||6.50.479|
Over time I have talked with numerous people about where the SQL database should be for the Configuration Manager database. Where this conversation typically comes up is when a company has a DBA team that is demanding that all SQL databases be hosted on dedicated (and super powerful) database servers. These servers predominantly will host numerous SQL databases for a variety of applications. The reasoning typically falls into the following arguments:
- Licensing – We don’t want to have to pay for another SQL license, so all DBs will be on our dedicated SQL servers.
- Performance – Our crazy powerful DB servers will give better performance than what you would install locally.
- Security – We need to maintain control over the content of the DB, and the DB integrity in general. Having them on a dedicated SQL server allows us to do that in the best way.
Sounds like some good arguments right? Well…not so much. Let’s take a look at each of the three.
- Licensing – Not an issue at all. Configuration Manager 2012 licensing includes the ability to install SQL Standard…at no additional charge.
- Performance – There have been arguments for years about whether Configuration Manager performed better with remote or on-box SQL. I’ve seen people give great arguments both ways…but haven’t really seen anything definitive either direction. With Configuration Manager 2012, the recommendation from Microsoft is that SQL be local unless you hit certain size limitations. Unless you are over 50,000 clients, then on-box SQL Standard will work just fine for you. If more than 50,000 clients, then a remote SQL Standard will take you to 100,000 clients. SQL Enterprise is only necessary on a Central Administration Site supporting more than 50,000 clients. (For more info.)
- Security – THIS IS THE BIG ONE! It generally takes about a three minute conversation with a DBA before they run away from this argument. Consider the following facts and implications in a remote SQL scenario:
- The Configuration Manager site server must be a member of the local administrators group on the remote SQL server. (See the Configuration Manager documentation.)
- Several people who are not SQL admins will be administrators on the Configuration Manager site server.
- It is trivial for an admin on the Configuration manager site server to run any application (such as a CMD prompt or SQL Server Management Studio) as Local System. (See this post.)
- Since the Configuration Manager server (Local System) has admin rights on the remote SQL server…the non SQL Admin can VERY easily obtain admin rights on the SQL server.
- The DBA has now started sweating, twitching and begging you to keep your weird database away from his/her server. :-)
So, really the only reason to consider doing remote SQL at all is a performance issue…but you have to be a pretty big organization for that one to come into play. And even if you do need to do remote SQL…it should be a SQL server that is dedicated to Configuration Manager.
Note (12/4/2012): I was talking with a friend late in the day yesterday about this blog post. He reminded me that I had already posted about this issue last April. Thanks Phil…I’m a little scatterbrained sometimes! I’m leaving this post up anyway because it is better than the original in my opinion.
I have had a few discussions over the years about whether a Configuration Manager installation should include SQL “on box” or “remote”. The answer is generally “it depends”. This blog post is not going to dig into all of the reasons why you would choose either local or remote SQL…it is designed to highlight one particular security concern with the remote SQL option. Let’s think through several of the underlying components that are necessary for remote SQL to take place along with a few very common scenarios when this is the case.
- Generally a company will choose remote SQL because they want to have a beefy SQL box that is managed by their DBA team. This SQL box will commonly house several SQL databases…not just the Configuration Manager DB. Which means that any disruption on that SQL server has an impact on much more than just Configuration Manager.
- A requirement for remote SQL with Configuration Manager is that the Configuration Manager server’s computer account must be in the local admin group on the SQL server.
- Commonly there will be a number of Configuration Manager administrators that have admin rights on the Configuration Manager server.
- Commonly there will be a number of those same Configuration Manager administrators that do NOT have admin rights on the remote SQL server.
THAT is where the problem rears it’s head. Let’s connect all of the dots…
- Joe Admin is an admin on the Configuration Manager server…but is not an admin on the SQL server.
- The Configuration Manager server’s computer account is an admin on the SQL server.
- Joe Admin has read my article on how to run a command prompt as local system. Uh oh.
- Joe Admin uses psexec to run a command prompt (or SQL Management Studio…or regedit…or services.msc…or disk management…or whatever else) as local system on the Configuration Manager server.
- Joe Admin then connects that app (running in the “user” context of the Configuration Manager server’s computer account) to the SQL server.
- Joe Admin is now able to do anything that the Configuration Manager server’s computer account has rights to do…which is full Administrator rights…ON THE SQL SERVER!!!
- That security you thought you had…well it didn’t work so well.
Is there anything to keep Joe Admin from (either accidentally or maliciously):
- Stopping services?
- Deleting files?
- Rebooting the server?
- Jacking with the registry?
- Installing (either good or bad) software?
- Copying data off of the SQL server?
- etc (I think you get the picture.)
Now…for some people that doesn’t matter. In many smaller installations the same team is managing Configuration Manager and SQL. However…if you are that small…why take on the extra complexity of the remote SQL scenario?
For others it matters big time! I’ve had conversations with customers who cringe at the very idea that some random Configuration Manager admin could possibly gain full rights to the SQL server that other business critical databases are stored on.
This weekend I was setting up a new demo environment on my laptop. This is a VM that I use to demonstrate ConfigMgr and OpsMgr to clients. I also use it for troubleshooting issues. The VM is running Active Directory, DNS, DHCP, WDS, SQL 2008, ConfigMgr SP2 R2, MDT 2010, SCOM R2, along with various other components.
So I’m building a new demo environment because I wanted to be using Windows Server 2008 R2 and SCOM R2. When I went to install SQL 2008, I got an error message:
Invoke or BeginInvoke cannot be called on a control until the window handle
has been created.
I did a quick Bing search on the error and found that I’m not the only one who has hit that error. At first, the most promising link appeared to be a KB article addressing that very issue. I also took a look at another link to blogs.msdn.com. That one describes the issue and states that if you restart the install it will probably work. In my case it didn’t work. In the comments section of that post, the very last comment was from someone who states that they simply created a c:\temp folder and it solved the issue.
I was skeptical, but it worked. SQL 2008 went through completely fine after creating that folder.