BUG: SQL 2008 cluster setup on Windows 2012 does not show the option to use “Service SID”   4 comments


Before I dive into the subject of this blog, a few words on Service SID. As you might know, SQL Server uses a service SID to provide service isolation. This feature was introduced in SQL 2008 and higher versions, however, whether you can install a SQL failover clustered instance (FCI) using Service SIDs or not, depends on the version of windows you are using. For SQL Server 2008 FCI, ideally you should be able to use the Service SID option if installing it over Windows 2008 or higher.

The Service SID option is shown in the Cluster Security Policy screen of SQL failover cluster install. Here is a screenshot from SQL Server 2008 FCI on Windows Server 2008/R2:

 

SQL 2008 on Win 2008

 

In this blog post, I’d like to talk about a *bug* which I discovered while installing SQL Server 2008 cluster on Windows Server 2012. When trying to install SQL 2008 FCI on Win 2012, the radio button to use Service SID doesn’t appear on the Cluster Security Policy screen. I see the only radio button to use is of Domain Groups.

 

SQL 2008 on Win 2012

 

I opened a case with Microsoft PSS where they confirmed it to be a bug. Here are some more details after I researched further:

 

Cause

 

SQL 2008/R2 setup code checks only for the minor version of OS to determine whether OS version qualifies for using Service SID or not. E.g. if you are installing SQL 2008 FCI on Win 2003/R2, you can’t use Service SID. Apparently, due to the aforementioned bug in the code, that only minor version of the OS is checked, Windows 2012 minor version returns 2 which is same as that of Windows 2003/R2.

Refer to http://msdn.microsoft.com/en-us/library/windows/desktop/ms724833(v=vs.85).aspx for more details.

 

Therefore, when we run SQL 2008/R2 FCI setup on Win 2012, it determines the minor version of OS to be 2, thinks that Service SID can’t be used on this OS hence the issue.

 

OSVERSIONINFOEX structure (Windows)

 

Workaround

 

There are two workarounds for this issue I know of:

 

1.       Use command line for SQL FCI. Refer to Failover Cluster Parameters section of http://msdn.microsoft.com/en-us/library/ms144259(v=sql.100).aspx#ClusterInstall for more details.

 

2.       The best and the simplest workaround is to just click Next on the Cluster Security Policy screen. J Yes, it’s that simple to overcome this bug however it might wonder you for hours if you didn’t know this. Let me explain why and how this works.

 

P.S: Don’t select Domain groups radio button. Just click on Next.

 

For SQL 2008/R2 cluster setup, Microsoft recommends use of Service SID over Domain groups provided the OS version supports it. If you check the Failover Cluster Parameters section of http://msdn.microsoft.com/en-us/library/ms144259(v=sql.100).aspx#ClusterInstall you would not find any parameters through which you can specify Service SIDs to be used from command line install. How do we specify it then? Well, the trick is, you leave the SQLDOMAINGROUP to be empty. SQLDOMAINGROUP is to specify the accounts for Domain groups in case you don’t choose Service SID option. If you leave this parameter empty, setup picks up Service SID as the option and proceeds.

 

As such, when you click Next on the Cluster security policy screen, Domain groups are left empty and by default setup picks up Service SID as the input parameter.

 

I did some further tests with other versions of SQL Server and I was surprised to see that the Cluster Security Policy screen itself is missing for SQL 2012 FCI on Windows 2012.

 

SQL 2012 on Win 2012

 

The same behaviour was observed for SQL 2014 CTP1 (I know it is still a CTP but couldn’t resist to test it out)

 

Upon researching further, it appears that it is by design. If the OS version qualifies for Service SIDs to be used for SQL 2012 or higher, setup will by default choose Service SID in that case. Therefore, the Cluster Security Policy screen won’t appear at all. Here is an extract from msdn article:

 

For SQL 2008/R2 http://technet.microsoft.com/en-us/library/ms179530(v=sql.105).aspx

 

Use this page to specify Cluster Security Policy.

·         Windows Server 2008 and later versions – Service SIDs (server security IDs) are the recommended and default setting. The option to specify domain groups is availablebut not recommended. For information about service SIDs functionality on Windows Server 2008, see Setting Up Windows Service Accounts.

·         On Windows Server 2003, specify domain groups for SQL Server services. All resource permissions are controlled by domain-level groups that include SQL Server service accounts as group members.

 

For SQL 2012 or higher http://technet.microsoft.com/en-us/library/ms179530.aspx

 

Use this page to specify Cluster Security Policy.

·         Windows Server 2008 and later versions – Service SIDs (server security IDs) are the recommended and default setting. There is no optionfor changing this to security groups.For information about service SIDs functionality on Windows Server 2008, see Configure Windows Service Accounts and Permissions. This has been tested in standalone and cluster setup on Windows Server 2008 R2.

·         On Windows Server 2003, specify domain groups for SQL Server services. All resource permissions are controlled by domain-level groups that include SQL Server service accounts as group members.

 

Here is a comparison chart across different versions of SQL/OS listing out the current and expected behaviour whether Service SID radio button should appear or not:

 

Expected Behaviour Windows Server 2003/R2 Windows Server 2008/R2 Windows Server 2012
Service SID Domain Groups Service SID Domain Groups Service SID Domain Groups
SQL Server 2008 No Yes Yes Yes Yes Yes
SQL Server 2008 R2 No Yes Yes Yes Yes Yes
SQL Server 2012 Unsupported setup. Unsupported setup. Yes No Yes No
SQL Server 2014 CTP1* Unsupported setup. Unsupported setup. Unknown. Unknown. Yes No
Current Behaviour Windows Server 2003/R2 Windows Server 2008/R2 Windows Server 2012
Service SID Domain Groups Service SID Domain Groups Service SID Domain Groups
SQL Server 2008 No Yes Yes Yes No Yes
SQL Server 2008 R2 No Yes Yes Yes No Yes
SQL Server 2012 Unsupported setup. Unsupported setup. Yes No Yes No
SQL Server 2014 CTP1* Unsupported setup. Unsupported setup. Unknown. Unknown. Yes No

 

I have submitted a bug on this issue. Pls visit the connect item and vote for it if you think this issue confused you enough to be fixed.

 

Stay tuned for some more interesting blogs!

 

4 responses to “BUG: SQL 2008 cluster setup on Windows 2012 does not show the option to use “Service SID”

Subscribe to comments with RSS.

  1. Thank you for this post! I am like what on earth is going on here! 🙂

  2. thanks for detail information and it helped and saved time

  3. Thanks a lot for this post. Saved my day!!!

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: