How to find out Security Errors on SQL Server 2005 instance and above including the API call it failed on


More often than not, while troubleshooting SQL Server Security Issues, you end up performing post-mortem analysis. Under such circumstances, the Ring Buffer output comes in handy because it stores the Security Errors that were reported in the past for a certain period of time. 

For an example, let’s say if you tried to create a SQL Login with the Password Policy enabled and provided a password that didn’t match a password policy, then you will receive an error stating that the password is not complex enough. This error will be stored in the ring buffer. When you execute the query below, you will find out the SPID that raised the error along with the API Name that is failing. In the sample output for this scenario described above, you will find that the NetValidatePwdPolicy failed. 

If you convert the ErrorCode value 0x8C5 (= 2245) to decimal and do a NET HELPMSG <number> from command prompt, you will find more information on why that error occurred. 

Output of NET HELPMSG 2245: 

The password does not meet the password policy requirements. Check the minimum password length, password complexity and password history requirements. 

The Calling API Name and the API Name are very helpful when troubleshooting Domain Account related security errors as it would call functions related to Directory Services whose documentation can be found on Tech Net which would make your troubleshooting easier. 

Sample Output: 

Notification_time: 2009-11-06 08:37:08.023
ErrorCode: 0x8C5             
CallingAPIName: NetValidatePwdPolicy                                       
APIName: NetValidatePwdPolicy                         
SPID: 53       
Record Id: 0           
Type: RING_BUFFER_SECURITY_ERROR                          
 

For SQL Server 2005: 

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - a.[Record Time]), GETDATE()) AS Notification_time, 
a.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
  x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] ASC

 For SQL Server 2008

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
dateadd (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time],
a.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
  x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] ASC

  

Additional Reference 

http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-sql-server-2005-sp2-security-ring-buffer-ring-buffer-security-error.aspx

Advertisements

Misconceptions around –T1118


There has been a lot on debate on whether having the same number of tempdb data files as the number of physical cores help a SQL instance in case you are facing Latch Contention issues on tempdb. This is typically seen with the wait-resource in the sysprocesses or sys.dm_exec_requests (depending on what you use to monitor blocking) output with values like 2:1:X or 2:2:X with having non-zero waittimes.

An article was published for this particular issue which asked you to enable –T1118 and split the tempdb to have multiple data files:

328551    Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551

This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.

Furthermore, with the advent of new monster systems having 64 processors (16 quad cores) and 128GB of RAM, would it make sense to split the tempdb into multiple files for SQL Server 2005 and 2008. One of the things that is not commonly known as mentioned by Paul in his post below is that on SQL Server 2005 and 2008, there is a temp table cache that is maintained for temp tables created on the instance. In case, your temp table creation workload is very high, enabling –TF1118 wouldn’t benefit you.

A quote from that post:

As far as data files go though, the number has changed. Instead of a 1-1 mapping between processor cores and tempdb data files (*IF* there’s latch contention), now you don’t need so many – so the recommendation from the SQL team is the number of data files should be 1/4 to 1/2 the number of processor cores (again, only *IF* you have latch contention). The SQL CAT team has also found that in 2005 and 2008, there’s usually no gain from having more than 8 tempdb data files, even for systems with larger numbers of processor cores. Warning: generalization – your mileage may vary – don’t post a comment saying this is wrong because your system benefits from 12 data files. It’s a generalization, to which there are always exceptions.

Yet again, Paul Randall has published an informative blog post around the usage of -TF1118:

http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx 

Incremental Servicing Model for SQL Server 2005


The Release Services team for SQL Server introduced a new servicing policy for SQL Server 2005 where in Cumulative Updates for the product are released every 2 months or 8 weeks cycle. Even though there is a KB Article and multiple blog posts surrounding the same, I have found the common question:

This fix is available in the list of available fixes in CU3 but I don’t see the bug/KB# in the Service Pack 3 KB Article.

Read on to find out why!! 🙂

So, if you are experiencing an issue mentioned in a KB Article, then you will find that the Bug# or the KB# for that issue mentioned in the list of available fixes in the next available Cumulative Update for SQL Server or Service Pack which ever is earlier. So, if you were looking for a bug# that you found mentioned in the KB Article for a Cumulative Update, then that KB Article/Bug# will not be repeated in the next available Cumulative Update or Service Pack. The reason for this is that all fixes that are released for Cumulative Update include all the fixes released in the prior Cumulative Updates.

For example, Service Pack 3 contains all the fixes that were released between time period of Service Pack 3 and Service Pack 1.

So, this is the reason why the Service Pack/Cumulative Update Pack sizes are getting progressively larger. So, another GOTCHA for all those folks who have been cribbing about the update package sizes. 🙂

Related Links:

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

http://support.microsoft.com/kb/935897

Another blog post iterating the above points in a more detailed manner:

http://blogs.technet.com/beatrice/archive/2007/07/11/incremental-servicing-model.aspx