The topic of this post comes from a question on the MSDN Forums, and had it not been for some advice from Buck Woody (blog|twitter), I never would have known the answer. Lately I’ve been doing a lot of work with Policy Based Management, in particular, working with Lara Rubbelke’s (blog|twitter) Enterprise Policy Management Framework which is a free download from Codeplex and builds on SQL Server 2008 Policy Based Management, Powershell, and the Management Data Warehouse to provide an Enterprise level solution for managing SQL Server 2000, 2005, and 2008 from a Central Management Server. One of the great things about this is that it uses features that are already built into SQL Server 2008, it is easy to get up and running right out of the box, and it is highly extensible and configurable to meet the requirements of almost any environment.
Now that I have gotten the
butt kissing acknowledgements out of the way, lets talk for a minute about Policy Based Management and the question that was posed, ultimately leading to the purpose behind this blog post. The question on the forums post was:
While working on Policy based managements, I found an option unknown in the authentication mode.
I have heard windows/mixed and sql server authentication. Whats this unknown authentication type. Any thoughts from experts.
If you’ve never worked with Policy Based Management before, you probably don’t know that it is driven by the SQL Management Object (SMO) libraries that ship with SQL Server and are accessible through .NET. These are the same libraries on top of which SQL Server Management Studio are built, and they provide a common interface through which you can access SQL Server and perform a number of management tasks in a programmatic manner. To be honest I had never looked at the particular facet that was in question here so I jumped onto my SQL Server 2008 test VM and took a quick look at it in SSMS, but alas its not quite that simple. To find the specific object that was being asked about here you have to know at a basic level where you might set the Authentication Mode for SQL Server in SSMS, which happens to be on the Server Properties dialog under the Security tree option, so I scrolled down and found the Server Security facet and opened its Properties dialog:
So looking at this I could see that the actual property being referenced is the LoginMode property, so I jumped into IE to search my favorite search engine
Google Bing (ok its really Google, but I just typed it into the search box in IE which took me to Bing) and did a search for “smo server loginmode” which provided me with a link to the BOL topic Server.LoginMode Property. If you look at the Property Value for this entry it contains a link to the BOL Topic for the ServerLoginMode Enumeration, which has included in it, an Unknown Enumeration value for the LoginMode property. Where, you might ask, do you find this in Policy Based Management? I am glad you asked.
To see the available options for the LoginMode in Policy Based Management, right click on the Server Security facet, and select New Condition:
Add a Name to the Condition, which I have called “Authentication Mode Not Unknown” and then in the Expression region click the drop down for Field (or click the ellipsis) and select @LoginMode. Then click the dropdown for Value, and the last option listed is Unknown. For the sake of brevity, change the operator to !=, and select the Unknown value type and then click on OK to create this condition on the server, which we will use later on.
Once you create a Condition, if you refresh the Conditions folder under Management>Policy Management in SSMS you will see the Condition we just created.
My first response to the question was that the facet just offers up the options that are available in the SMO Enumeration as possible values, but that didn’t really answer the problem that the poster had for the question, and the response back was:
I am not clear with you answer.
correct me If I am wrong. Unknown option will not work in this scenario. Guessing it's a bug.
My response to this was the well coined phrase by Paul Randal (blog|twitter), “It Depends”. Does Unknown actually apply here? Its a possible value for the SMO Enumeration so its certainly not a bug, Microsoft didn’t decide to just put an arbitrary “Unknown” value in the SMO Enumeration for nothing. However, to understand why “Unknown” is a possible valid value for this facet, you have to know a bit more about how the LoginMode property in SQL Server is actually configured. Its not implemented like a lot of the other configuration properties for the SQL Server Instance, that being a sp_configure option that is stored in the engine, it is instead implemented as a registry key that is editable through means outside of SQL Server and therefore can be set to values that are not applicable and could be in fact “Unknown”.
To see this, connect to any SQL Server, 2000, 2005, or 2008 instance in SSMS and right click on the Server Node in Object Explorer. Open the Properties Dialog and then click on Security in the treeview on the left hand side. Change the Login Mode configuration option from whatever it is currently set to to the other option and then click on the Script Button at the top of the dialog box. When you do this, a script similar to the following will be generated, relative of course to the instance to which you connected:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
You can also see this value by running the Windows Registry Editor (regedit) and navigating to the corresponding key entry for the SQL Server Instance you are looking at.
Fair Warning: DO NOT just arbitrarily play with the above TSQL command, the Server Properties Dialog, or the Windows Registry Editor (regedit) on a production or otherwise important server in your workplace, you can screw it up!!! If you break something based on the information in this blog post it is your problem, not mine.
Since there are a few different ways to set values for this registry key, and none of them implement any sort of validity checks, it is very understandable that an invalid value can be set for it. To validate this, because I
love to break things wanted to ensure that I was providing a valid response, I decided to play around on my 2008 VM for a second and used a PowerShell script to validate the SMO Enumeration output for ServerLoginMode under various values for this registry key. What I found was the following mappings between the registry key value and the ServerLoginMode Enumeration output:
|0 ||Normal |
|1 ||Integrated |
|2 ||Mixed |
|3+ ||Unknown |
The PowerShell Script I used to test this was really simple:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "."
So how does this play into Policy Based Management? I am glad you asked. Consider for a moment that you have a large SQL Server environment consisting of a few hundred SQL Servers with varying versions from 2000 to 2008 R2, and you wanted to check to see if any of them had an incorrect value setup for the LoginMode Server Security Property. Using the previously created Condition from this blog post, we can easily define a Policy that can be used inside of the Enterprise Policy Management Framework to look for servers that have an invalid registry key value set for the LoginMode Server Property. To do this we simply have to define a Policy on the Condition so that we can evaluate our servers for Unknown Authentication Modes and plan to correct them according to our established configuration.
Once we create the Policy, we can validate that it exists by refreshing the Policies folder under Management > Policy Management in SSMS.
To test out our new Policy and Condition, we can do a simple evaluation on our test server by right clicking the Policy and selecting Evaluate from the context menu.
If the server is properly configured, the Result Dialog will show up with all green in it showing that the Policy passed Evaluation on a properly configured server:
Now that we have validated that the Policy indeed returns correctly for a valid configuration, we need to set an invalid value for the registry key so that we can validate that the Policy fails for an invalid or “Unknown” value for the LoginMode on the server. To set an invalid value, we can simply change the TSQL query from above (READ THE FAIR WARNING) and execute the query:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 4
Now with an invalid value set, we can go back and re-evaluate the Policy and make sure that it fails, which it should if you set things up correctly.
One of the best parts about using Policy Based Management is that you don’t have to just accept that “Ok, it failed” you can actually click on View Details for the specific instance of a failure, you can see the actual results that were evaluated against the condition to determine the specific cause of the failure. In this case, the output of the SMO Enumeration is “Unknown” in violation of our specific Condition, resulting in a Policy failure for this instance of SQL.
Hopefully you found this post to be
the best post you’ve read all week really insightful into not only how Policy Based Management actually works, but also into how you can leverage some of the existing features in the SQL Server 2008 product set to learn more about your environment, and provide a better level of service, as well as gain in depth information about your servers configurations.