SP2 has just become available today. I think you’ll find this to be a pretty significant service pack, as it contains more than just bug fixes. In this post I want to cover a few security features – nothing groundbreaking, just useful stuff. At the time I am writing this, BOL has not been refreshed yet, but it should soon reflect the new syntax changes.
(1) ALTER USER – addition of WITH LOGIN clause
This new syntax for ALTER USER allows remapping a user to another login, by changing the user’s SID value to match the login’s SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation.
(2) ALTER LOGIN – now supports hashed passwords
CREATE LOGIN already supported creating a SQL login with a hashed password, to support migrating accounts between SQL Server instances. The capability to specify a hashed password has now been added to ALTER LOGIN as well, to support scenarios where SQL accounts have to be kept in sync. Note that this can only be used if the password policy checking setting is turned off. One reason for this is that policy checks cannot be performed on password hashes.
(3) Security ring buffer for error logging
This one is for debugging/support. Most readers will probably prefer to skip this section. But if your application has failed a security operation and you couldn’t understand the reason, then this functionality might help.
A ring buffer is an internal structure that captures various information – there were several ring buffers available in SQL Server 2005 RTM, and information about their contents could be found by querying sys.dm_os_ring_buffers. There is now a new ring buffer in SP2 that captures information about errors that occur during security operations. As a reminder, ring buffers are for internal and debugging purposes only, as mentioned in BOL here.
The security ring buffer is identified as RING_BUFFER_SECURITY_ERROR, so, for querying only this ring buffer, the following query can be used:
select* from sys.dm_os_ring_buffers where ring_buffer_type = ‘RING_BUFFER_SECURITY_ERROR’
Each entry corresponds to a Windows API failure that occurred in the context of some security code. Note that an entry does not indicate necessarily an error. Some entries can be generated during the course of normal operations and are expected failures.
The interesting information resides in the “record” column, in XML format. The important fields are:
– the SPID, which could be correlated with a trace to determine what command generated the entry.
– the API Name and the Calling API Name, which provide a quick idea of the context. Sometimes, due to the way information is (not) collected, these may be filled in as ‘Not Available’ – this is the case for most of the failures in calls made to CAPI.
– the Error Code, which is the code returned by the Windows API that failed. These codes can be looked up on the System Errors Codes page, to obtain additional information about the error.
The final Stack field is just a mini call stack that can be submitted with an error report, to provide more detailed information. The hex values are just function offsets; no information on the function parameters is present, so this information can be submitted without fearing the release of sensitive information.
There are also two traceflags associated with the ring buffer:
4612 – this can be used to disable the ring buffer logging – no new entries will be made into the ring buffer, if this is set.
4613 – this is used to generate a minidump file whenever an entry is logged into the ring buffer.
These traceflags can be toggled at any time. Note that a dump may contain sensitive information, depending on what information was processed at the time the entry was logged.
So, how can you use this? If the error message from a failure isn’t providing sufficient information, have a look at the ring buffer to see if any Windows API call failed; keep in mind that a ring buffer entry doesn’t necessarily indicate that an error occurred. If an entry is found that is suspected to be linked to the failure, check its API Name and Error Code values, and see if these shed additional light; if they don’t help, then when you submit a bug report, include the ring buffer entry information as well. In some cases, you may be asked to turn on traceflag 4613, to obtain a dump for in-depth investigation.