Search results matching tags 'Database Administration', 'SQL Server 2008 R2', and 'SQL Server 2008'

Parsing the sqlserver.sql_text Action in Extended Events by Offsets

jmkehayias — Mon, 31 Jan 2011 02:05:43 GMT

A couple of weeks back I received an email from a member of the community who was reading the XEvent a Day blog series and had a couple of interesting questions about Extended Events. This person had created an Event Session that captured the sqlserver.sql_statement_completed and sqlserver.sql_statement_starting Events and wanted to know how to do a correlation between the related Events so that the offset information from the starting Event could be used to find the statement of the completed Event to know the specific statement and its reads, cpu and write information from the sqlserver.sql_statement_completed Event. Confused yet? Since it doesn’t really read well what the intent was, lets look at the columns that are output by both of these Events, and it will make more sense. I am going to break this post into two sections, SQL Server 2008 and SQL Server Denali, because there are significant differences between the two with regard to Extended Events and specifically these two events.

SQL Server 2008

In SQL Server 2008, the sqlserver.sql_statement_starting Event has the following output columns:

select name, column_id, type_name
from sys.dm_xe_object_columns
where object_name = 'sql_statement_starting'
  and column_type <> 'readonly'

The sqlserver.sql_statement_completed Event has the following output columns:

select name, column_id, type_name
from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'
  and column_type <> 'readonly'

The intent as I understood it was to use the offset and offset_end from the sqlserver.sql_statement_starting Event with the information in the sqlserver.sql_statement_completed Event and I could only assume the sqlserver.sql_text Action. When I got the email I replied back to use the sqlserver.tsql_stack Action to get the offset information with the sqlserver.sql_statement_completed Event and use that to parse the statement from the sqlserver.sql_text Action. I didn’t have time to play with it at the moment, but I promised to write a blog post showing how to do it later that day.

When I got a free minute where I could write a demo to blog about for this I ran into some problems with the results that I got from parsing the sql_text data that was returned by Extended Events. To show this, lets create an Event Session and run a test multi-statement batch so that we have to some sample Event Data to do processing on.

IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = 'SQLStmtEvents')
    DROP EVENT SESSION SQLStmtEvents ON SERVER
GO
-- Create our Event Session for current Session_ID
DECLARE @SqlCmd NVARCHAR(MAX) = N'
CREATE EVENT SESSION SQLStmtEvents
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR(4))+')    ),
ADD EVENT sqlserver.sql_statement_starting
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR(4))+')    )
ADD target package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)';
EXEC(@SqlCmd);
GO
-- Start the Event Session
ALTER EVENT SESSION SQLStmtEvents
ON SERVER
STATE=START;
GO
-- Run a multi-statement Batch
SELECT * 
FROM sys.objects

SELECT * 
FROM master..spt_values
WHERE type = N'P'

SELECT *
FROM INFORMATION_SCHEMA.TABLES
GO

With the Event Session created I did quick check to validate that the offset information held in the tsql_stack matched the offset information returned by the sql_statement_starting Event. The tsql_stack returns an XML document that is similar to the following:

<frame level="1" handle="0x020000004765D81E643DBDA1E90A8F634B78C042FF805374" line="2" offsetStart="64" offsetEnd="124" />

After validating the offsets matched, I wrote out the parser for the Event Data and sql_text as follows:

-- Extract the Event information from the Event Session 
SELECT 
    event_name, 
    timestamp, 
    cpu, 
    duration, 
    reads, 
    writes, 
    state, 
    ISNULL(offset, tsql_stack.value('(frame/@offsetStart)[1]', 'int')) as offset,
    ISNULL(offset_end, tsql_stack.value('(frame/@offsetEnd)[1]', 'int')) as offset_end,
    ISNULL(nest_level, tsql_stack.value('(frame/@level)[1]', 'int')) as nest_level,
    SUBSTRING(sql_text,    
                (ISNULL(offset, tsql_stack.value('(frame/@offsetStart)[1]', 'int'))/2)+1, 
                ((CASE ISNULL(offset_end, tsql_stack.value('(frame/@offsetEnd)[1]', 'int')) 
                    WHEN -1 THEN DATALENGTH(sql_text) 
                    ELSE ISNULL(offset_end, tsql_stack.value('(frame/@offsetEnd)[1]', 'int')) 
                  END - ISNULL(offset, tsql_stack.value('(frame/@offsetStart)[1]', 'int')))/2) + 1) AS statement_text_Xevents,
    sql_text,
    event_sequence,
    activity_id
FROM
(
    SELECT 
        event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
        COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
            event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
        event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        event_data.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
        event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') AS [reads],
        event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS [writes],
        event_data.value('(event/data[@name="state"]/text)[1]', 'nvarchar(4000)') AS [state],
        event_data.value('(event/data[@name="offset"]/value)[1]', 'int') AS [offset],
        event_data.value('(event/data[@name="offset_end"]/value)[1]', 'int') AS [offset_end],
        event_data.value('(event/data[@name="nest_level"]/value)[1]', 'int') AS [nest_level],
        CAST(event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS XML) AS [tsql_stack],
        event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
        event_data.value('(event/data[@name="source_database_id"]/value)[1]', 'int') AS [source_database_id],
        event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
        event_data.value('(event/data[@name="object_type"]/text)[1]', 'int') AS [object_type],
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id,
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) as event_sequence
    FROM 
    (   SELECT XEvent.query('.') AS event_data 
        FROM 
        (    -- Cast the target_data to XML 
            SELECT CAST(target_data AS XML) AS TargetData 
            FROM sys.dm_xe_session_targets st 
            JOIN sys.dm_xe_sessions s 
                ON s.address = st.event_session_address 
            WHERE name = 'SQLStmtEvents' 
              AND target_name = 'ring_buffer'
        ) AS Data 
        -- Split out the Event Nodes 
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
    ) AS tab (event_data)
) AS results

If you run these two examples, you will notice immediately that something is not right with the statement_text_XEvents data, it didn’t parse out correctly.

I also validated that this same behavior exists in SQL Server Denali CTP1. To look at this further and determine where exactly the problem might be, I used the plan_handle from the tsql_stack to get the sql text from sys.dm_exec_sql_text() and then offset parse that in the same manner to validate that the offset information is not the problem.

SELECT 
    event_name, 
    tsql_stack.value('(frame/@offsetStart)[1]', 'int') AS offset,
    tsql_stack.value('(frame/@offsetEnd)[1]', 'int') AS offset_end,
    tsql_stack.value('(frame/@level)[1]', 'int') AS nest_level,
    SUBSTRING(sql_text,    
                (tsql_stack.value('(frame/@offsetStart)[1]', 'int')/2)+1, 
                ((CASE tsql_stack.value('(frame/@offsetEnd)[1]', 'int')
                    WHEN -1 THEN DATALENGTH(sql_text) 
                    ELSE tsql_stack.value('(frame/@offsetEnd)[1]', 'int')
                  END - tsql_stack.value('(frame/@offsetStart)[1]', 'int'))/2) + 1) AS statement_text_Xevents,
    SUBSTRING(st.text, (tsql_stack.value('(/frame/@offsetStart)[1]', 'int')/2)+1, 
        ((CASE tsql_stack.value('(/frame/@offsetEnd)[1]', 'int')
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE tsql_stack.value('(/frame/@offsetEnd)[1]', 'int')
         END - tsql_stack.value('(/frame/@offsetStart)[1]', 'int'))/2) + 1) AS statement_text_from_cache, 
         sql_text
FROM
(
    SELECT 
        event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        CAST(event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS XML) AS [tsql_stack],
        event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
    FROM 
    (   SELECT XEvent.query('.') AS event_data 
        FROM 
        (    -- Cast the target_data to XML 
            SELECT CAST(target_data AS XML) AS TargetData 
            FROM sys.dm_xe_session_targets st 
            JOIN sys.dm_xe_sessions s 
                ON s.address = st.event_session_address 
            WHERE name = 'SQLStmtEvents' 
              AND target_name = 'ring_buffer'
        ) AS Data 
        -- Split out the Event Nodes 
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
    ) AS tab (event_data)
) AS results
CROSS APPLY sys.dm_exec_sql_text(tsql_stack.value('xs:hexBinary(substring((/frame/@handle)[1], 3))', 'varbinary(max)')) AS st

The information from the cache is correct, so there has to be something different about the sql_text Action output. To figure out what that was, I changed my script slightly so that it would parse out each character in the text from cache and the sql_text Action to see what was different and where:

-- Extract the Event information from the Event Session 
SELECT 
    sv.number AS Position, 
    ASCII(SUBSTRING(text, sv.number+1, 1)) AS cache_text,
    ASCII(SUBSTRING(sql_text, sv.number+1, 1)) AS sql_text
FROM
(
    SELECT 
        CAST(event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS XML) AS [tsql_stack],
        event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
    FROM 
    (   -- Get only one of the events this time
        SELECT TOP 1 XEvent.query('.') AS event_data 
        FROM 
        (    -- Cast the target_data to XML 
            SELECT CAST(target_data AS XML) AS TargetData 
            FROM sys.dm_xe_session_targets st 
            JOIN sys.dm_xe_sessions s 
                ON s.address = st.event_session_address 
            WHERE name = 'SQLStmtEvents' 
              AND target_name = 'ring_buffer'
        ) AS Data 
        -- Split out the Event Nodes 
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
    ) AS tab (event_data)
) AS results
CROSS APPLY sys.dm_exec_sql_text(tsql_stack.value('xs:hexBinary(substring((/frame/@handle)[1], 3))', 'varbinary(max)')) AS st
CROSS JOIN master.dbo.spt_values AS sv
WHERE sv.type = 'P'
  AND sv.number <= DATALENGTH(text)

What I found was that the carriage returns are missing from the sql_text Action in Extended Events. We can work around that by doing a REPLACE(sql_text, CHAR(10), CHAR(13)+CHAR(10)) operation to return the carriage returns to the output.

-- Extract the Event information from the Event Session 
SELECT 
    event_name, 
    timestamp, 
    cpu, 
    duration, 
    reads, 
    writes, 
    state, 
    SUBSTRING(sql_text,    
                (tsql_stack.value('(frame/@offsetStart)[1]', 'int')/2)+1, 
                ((CASE tsql_stack.value('(frame/@offsetEnd)[1]', 'int')
                    WHEN -1 THEN DATALENGTH(sql_text) 
                    ELSE tsql_stack.value('(frame/@offsetEnd)[1]', 'int') 
                  END - tsql_stack.value('(frame/@offsetStart)[1]', 'int'))/2) + 1) AS statement_text_Xevents,
    SUBSTRING(st.text, (tsql_stack.value('(/frame/@offsetStart)[1]', 'int')/2)+1, 
        ((CASE tsql_stack.value('(/frame/@offsetEnd)[1]', 'int')
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE tsql_stack.value('(/frame/@offsetEnd)[1]', 'int')
         END - tsql_stack.value('(/frame/@offsetStart)[1]', 'int'))/2) + 1) AS statement_text_from_cache,
    sql_text,    
    event_sequence,
    activity_id
FROM
(
    SELECT 
        event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
        COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
            event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
        event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        event_data.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
        event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') AS [reads],
        event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS [writes],
        event_data.value('(event/data[@name="state"]/text)[1]', 'nvarchar(4000)') AS [state],
        event_data.value('(event/data[@name="offset"]/value)[1]', 'int') AS [offset],
        event_data.value('(event/data[@name="offset_end"]/value)[1]', 'int') AS [offset_end],
        event_data.value('(event/data[@name="nest_level"]/value)[1]', 'int') AS [nest_level],
        CAST(event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS XML) AS [tsql_stack],
        REPLACE(event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'), CHAR(10), CHAR(13)+CHAR(10)) AS [sql_text],
        event_data.value('(event/data[@name="source_database_id"]/value)[1]', 'int') AS [source_database_id],
        event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
        event_data.value('(event/data[@name="object_type"]/text)[1]', 'int') AS [object_type],
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id,
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) as event_sequence
    FROM 
    (   SELECT XEvent.query('.') AS event_data 
        FROM 
        (    -- Cast the target_data to XML 
            SELECT CAST(target_data AS XML) AS TargetData 
            FROM sys.dm_xe_session_targets st 
            JOIN sys.dm_xe_sessions s 
                ON s.address = st.event_session_address 
            WHERE name = 'SQLStmtEvents' 
              AND target_name = 'ring_buffer'
        ) AS Data 
        -- Split out the Event Nodes 
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
    ) AS tab (event_data)
) AS results
CROSS APPLY sys.dm_exec_sql_text(tsql_stack.value('xs:hexBinary(substring((/frame/@handle)[1], 3))', 'varbinary(max)')) AS st

I filed this as a bug on Connect titled Extended Events sqlserver.sql_text Action is not Offset Parseable after trading a couple of emails with Mike Wachal about this, and he has already replied to the connect item stating that they are looking at the difference between the two operations in the engine.

SQL Server Denali

I was hesitant to even bother filing this as a bug because in SQL Server Denali CTP1, none of this is necessary to collect the statement, since the statement exists as a customizable column on the Events.

In Denali CTP1, the sql_statement_starting Event has the following columns:

select name, column_id, type_name, column_type
from sys.dm_xe_object_columns
where object_name = 'sql_statement_starting'
  and column_type <> 'readonly'

The sql_statement_completed Event has the following columns:

select name, column_id, type_name, column_type
from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'
  and column_type <> 'readonly'

If you compare the columns output by the sql_statement_completed Event in Denali CTP1, you will notice that it has significantly more columns in its base column payload, as well as having the statement as a customizable column. In Denali CTP1, the only thing necessary to get the specific TSQL statement that is executing is to turn collection of the customizable column statement on using the collect_statement column.

IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = 'SQLStmtEvents')
    DROP EVENT SESSION SQLStmtEvents ON SERVER
GO
DECLARE @SqlCmd NVARCHAR(MAX) = N'
CREATE EVENT SESSION SQLStmtEvents
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(    SET collect_statement=1
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR(4))+')    ),
ADD EVENT sqlserver.sql_statement_starting
(    SET collect_statement=1
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR(4))+')    )
ADD target package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)';
EXEC(@SqlCmd);
GO
ALTER EVENT SESSION SQLStmtEvents
ON SERVER
STATE=START;
GO

SELECT * 
FROM sys.objects

SELECT * 
FROM master..spt_values
WHERE type = N'P'

SELECT *
FROM INFORMATION_SCHEMA.TABLES
GO

With this turned on, all we have to do is query the Event Data and parse out the statement column to know the specific statement that was executing.

-- Extract the Event information from the Event Session 
SELECT 
    event_name, 
    timestamp, 
    cpu, 
    duration, 
    reads, 
    writes, 
    state, 
    statement,
    activity_id,
    event_sequence
FROM
(
    SELECT 
        event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
        event_data.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
        event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') AS [reads],
        event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS [writes],
        event_data.value('(event/data[@name="state"]/text)[1]', 'nvarchar(4000)') AS [state],
        event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS [statement],
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id,
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) as event_sequence
    FROM 
    (   SELECT XEvent.query('.') AS event_data 
        FROM 
        (    -- Cast the target_data to XML 
            SELECT CAST(target_data AS XML) AS TargetData 
            FROM sys.dm_xe_session_targets st 
            JOIN sys.dm_xe_sessions s 
                ON s.address = st.event_session_address 
            WHERE name = 'SQLStmtEvents' 
              AND target_name = 'ring_buffer'
        ) AS Data 
        -- Split out the Event Nodes 
        CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
    ) AS tab (event_data)
) AS results

The new statement column in Denali CTP 1 eliminates the need to do offset parsing of the sql_text Action entirely. Knowing that, it really became clear to me that the addition of the offset information in the Event’s base column payload was really useless, and for that I submitted a different Connect feedback item Extended Events - Remove data from Event output that is available by Actions. You might ask why does this matter? It matters because for everytime the Event fires, this information is included in the Event Data for the Event. Since Event Data is materialized as XML, this becomes needless bloat of the Event Data output for the Event and it really is useless information. If you really wanted to collect the offset information, you could add the tsql_stack Action to the Events to collect it, so there is no reason for the Event to carry this information by default.

So there you have it. How to parse the sqlserver.sql_text Action in SQL Server 2008 using offset information to isolate the actual executing statement from a multi-statement batch using a work around, and why its not necessary to do this in SQL Server Denali going forward.

Policy Based Management and the Unknown LoginMode

jmkehayias — Wed, 15 Sep 2010 05:47:10 GMT

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:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

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') "."
$smosvr.LoginMode

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:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 4
GO

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.