• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.
  • Advertisements

SQL Server 2008 R2 Security Benchmark Released

The Center for Internet Security (CIS) Security Benchmarks Division released “CIS Microsoft SQL Server 2008 R2 Database Engine Benchmark V1.0.0” on November 16, 2012. The best I can tell, this benchmark can also be used with SQL Server 2008.

CISSQL2008R2This is a consensus-based development of security best practices which have become the de facto security configuration standards.  If you are in charge of your SQL Server security configuration, you need a copy of this document – it is what your auditors will be using soon!  




Database Security Roles in msdb

All databases have a standard set of “fixed” database security roles which have been available since SQL Server 2000 and hopefully, even if you are new to SQL Server, you are familiar with this set:

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin

Back in the day, the msdb database was primarily used for managing backups, SQLAgent jobs, and DTS packages. And, other than the TargetServersRole role for multi-server job management via a master server and target servers, there were no other database roles defined for helping establish a separation of duties security model for tasks controlled via msdb

In SQL Server 2005 some long awaited new roles were added to msdb (http://msdn.microsoft.com/en-us/library/ms188283(SQL.90).aspx) which help with segregating permissions for managing SQLAgent Jobs, DTS\SSIS, Database Mirroring, and Database Mail (the replacement for SQL Mail):

  • DatabaseMailUserRole
  • db_dtsadmin – renamed in later versions as db_ssisadmin
  • db_dtsltduser -renamed in later versions as db_ssisltduser
  • db_dtsoperator – renamed in later versions as db_ssisoperator
  • dbm_monitor – does not appear until database mirroring is implemented
  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Then, in SQL Server 2008 even more roles appeared as msdb took on increased importance in the management of new features such as the CMS (Central Management Server), PBM (Policy Based Management), Data Collection, and MDW (Management Data Warehouse).

Thus, the following new roles may be found in SQL Server 2008+:

In SQL 2008 R2 one more set of roles was added for the Server Utility (UCP) feature:

  • UtilityCMRReader
  • UtilityIMRReader
  • UtilityIMRWriter

The above UCP roles are primarily assigned via the Server Utility configuration wizard and Utility Explorer Security tab. The “Utility Reader role” referenced in the Security tab is equivalent to the UtilityCMRReader role. As you may notice, certain service and administrator accounts are automatically in this role and cannot be changed (grayed out).

And, now in SQL Server 2012, believe it or not, there are no new roles in msdb!  So, if you haven’t kept up with all the changes in msdb in the past few versions, this is your chance to catch up and make sure that you are fully utilizing these features and roles as needed in your environment. For a recap of all the features which utilize msdb, you can also refer to an earlier post I wrote – “What’s in Your msdb?”.

Discontinued Features in SQL Server 2012 – Part 4

In the previous installment on this topic, I looked at discontinued features in SQL Server 2012 which may mostly impact your administration scripts.  Today, we are widening the net and looking at discontinued features which may impact either the application layer or administration programs and scripts. Most of these items have been deprecated as far back as SQL Server 2000, so it isn’t like they have gone away overnight. However, if you are still running on SQL Server 2000, then you really haven’t had a need to go replace them…until now.

SQL Mail is finally gone! It would have been totally fine with me if SQL Mail had been discontinued as soon as Database Mail was added in SQL Server 2005.  However, you do lose functionality (receive mail processing) with Database Mail, but the trade-off is much improved reliability, security, supportability, and scalability. For example, you no longer need an Extended MAPI client (e.g. Outlook) installed; you don’t run the risk of attacks via reading mail; and Database Mail is supported on clusters. So, if you are still using the read\process mail functionality of SQL Mail, you’ll need to find another method for your application to use before upgrading to SQL Server 2012.  If all you are doing are send mail tasks, including notifications from SQLAgent, then the switch to Database Mail is a no-brainer and you can do it now in SQL Server 2005/2008/2008R2. However, this is more than just a configuration change; it does require usage coding changes for example from xp_sendmail to sp_send_dbmail.  

Unless you’ve been stuck on SQL Server 2000, you have probably already started converting applications using SQL-DMO (Database Management Objects) to use SQL-SMO (SQL Server Management Objects) when you found you wanted to access newer features in SQL Server 2005+ that were not available via SQL-DMO.  If you want to use your SQL-DMO scripts against SQL Server 2012 – you must convert to SQL-SMO; SQL-DMO is discontinued.

If you have databases in compatibility mode 80, it might be because the code using them is still using the old non-ANSI outer join syntax “*=” or “=*”.  This syntax is not supported in later compatibility modes and because compatibility mode 80 is not supported in SQL Server 2012, this syntax is no longer recognized in SQL Server 2012 in any scenario.  You’ll need to get all of this code up-to-date with current ANSI join syntax used in the FROM clause.

Another T-SQL syntax which is disappearing is the COMPUTE / COMPUTE BY clause in SELECT statements; you will need to convert these statements to use the ROLLUP option of the GROUP BY clause.

If you tried to speed up the results returned to your client or force a particular query plan by using the FASTFIRSTROW query hint, you’ll need to switch to the OPTION (FAST n) syntax as FASTFIRSTROW is no longer available.  For more info on the overall impact of using this option, see this blog from the SQL Server Query Optimization team and this one from SQL MVP Grant Fritchey. As with all query hints, you should re-evaluate these periodically as they may no longer be as beneficial as when originally added.

In SQL Server 2012, you can no longer invoke an ad-hoc error message number using RAISERROR integer ‘message’ syntax as in: RAISERROR 50111 ‘Sample error message’.  You’ll need to convert to the RAISERROR() syntax:  RAISERROR (‘Sample error message’, 10, 1). But, if you want to really get your code current, convert to TRY…CATCH (available since SQL Server 2005) and use THROW (new in SQL Server 2012). There are some differences in behavior between RAISERROR and THROW and these are documented in BOL’s THROW entry.

I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here.

Happy upgrading!

TSQL2sday #026 – Second Chances

What is TSQL2sday? Back in late 2009, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of TSQL2sday).  This month’s event is hosted by David Howard (blog | twitter) and this month Dave is letting us chose our topic from any of the prior 25 topics! As my first foray into this event wasn’t until the 14th occurrence, I really like this idea and selected “TSQL2sday #007 Summertime in the SQL” as my second chance topic. Okay, so it is January, but it was 70+ degrees in Houston today, so quite balmy. However, that wasn’t why I chose this topic; I really chose it because this topic was about what is your favorite “hot” feature in SQL Server 2008 or R2. I thought about “updating” the topic to SQL Server 2012, but I’m really not sure yet which new “hot” feature of SQL Server 2012 will turn out to be my favorite – and after 3 years, I definitely know which SQL Server 2008 set of features is my personal favorite – CMS and PBM.

The Central Management Server (CMS) and Policy-Based Management (PBM) features have made the overall management of large numbers of SQL Server instances, well, manageable.

The CMS enables us to organize instances into multiple different classifications based on version, location, etc. We rebuild the CMS on a regular schedule based on the data in our asset management system. This ensures that all DBAs have access to a CMS with all known instances. If you are not familiar with the CMS – it does not grant any access to the instances themselves and connectivity using it only works with Windows Authentication, so there are no security loopholes here.

We then use these CMS groups as input into our various meta-data and compliance collection processes. Approximately 90% of our technical baseline compliance evaluation is accomplished via policies in PBM. We’ve incorporated all of this using the EPM (Enterprise Policy Management) Framework available on Codeplex with a few tweaks of our own to work better in our environment.

If you haven’t yet checked out the CMS and PBM features, I encourage you to do so today. I have two previous blog entries relating to this topic – “Managing the Enterprise with CMS” and “Taking Advantage of the CMS to Collect Configuration Data”.  I’d also highly recommend that you watch the SQL Server MCM Readiness Videos on the Multi-Server Management and PBM topics.

And, it is good to know that by the time this entry is posted – we should be back to our normal 50 degree January weather in Houston!  

SSRS Log Files Retention Bug

Last October (2010), I posted the following bug on Microsoft Connect (https://connect.microsoft.com/SQLServer/feedback/details/615123/ssrs-2008-r2-keepfilesfordays-config-file-item-does-not-appear-to-work).  Essentially, the SQL Server Report Server (SSRS) 2008 R2 KeepFilesForDays parameter in the ReportingServicesService.exe.config file isn’t working as advertised in BOL for deleting expired log files.  And, until today, when I went to manually delete them, I still had every log file since I installed in June 2010!

A few weeks ago, Microsoft officially closed my Connect item as “Fixed”. But, no word on when/where it is fixed.  I still see the problem in SQL 2008 R2 SP1 and I don’t see a mention of a fix for this in any of the three (3) CU’s released for SP1 so far.  It also appears that this is likely a problem in SSRS 2008 and SSRS 2005 based on a handful of inquiries I spotted in various forums when researching.  Although in SSRS 2005 the parameter setting and log files are in different locations due to SSRS’s usage of IIS for that version.  I also checked my Denali CTP3 installation of SSRS and sure enough – every log file since I installed (way, more than the default 14 days) still exists.

So, just a word of warning, if you assume that this parameter is working for you – it probably isn’t and you’ll need to find another way to delete your old log files for SSRS. If you have plenty of space on the drive where these are stored, then it isn’t an issue – yet. But, beware!

SQL Server Deprecated Features – Part 4

In part 1 of this series, I discussed how Microsoft notifies us of changes between product versions and provided links to the “Backward Compatibility” sections in BOL (Books Online) for SQL Server 2000/2005/2008/2008R2.  In part 2, I discussed some compatibility issues with deprecated features which I’d run across.  In part 3, I covered tools to detect the usage of deprecated features.  Now, in part 4, I’ll take a look at what is coming in the next release of SQL Server – code-named “Denali” aka SQL 11.

While there is not an announced release date yet for Denali, the first CTP (Community Technology Preview) was released in November 2010 and included the availability of BOL for Denali.  This includes the “Backward Compatibility” section for Denali. When reviewing any of the Denali BOL articles, you’ll note that there is a disclaimer at the top of each page that this is “preview only” and “subject to change”.  So, you may want to check back frequently between now and RTM for any changes – especially as some sections still contain either placeholders or content from 2008/R2 – particularly for components other than the database engine.

If you are currently in the process of upgrading to SQL Server 2008 or R2 and making changes to your applications, then you’ll probably want to go ahead and review the changes announced for Denali and beyond.  If it makes sense, go ahead and start incorporating those changes now.  You should focus first on the discontinued features in Denali – meaning you will have to make a change to move to Denali if you rely on these features; so why not get rid of them now?  (Side note: the good news is that this list is currently shorter than expected based on the SQL 2008 R2 list of “Features Not Supported in the Next Version of SQL Server”. But that could change by RTM since it appears to have been Microsoft’s intent to fully remove them in Denali when R2 BOL was released.)

Currently on the discontinued list:

  • Databases in 80 compatibility mode; must be in 90 compatibility mode to upgrade to Denali (SQL 11)
  • PWDCOMPARE function parameter for to designate a login prior to SQL 2000
  • No AWE support; No support for over 4GB of physical memory on 32-bit OS

Next, tackle the Denali deprecated features list in the section labeled “Features Not Supported in the Next Version of SQL Server”. This would include the features expected for removal in SQL 12.  Yes, that is a long way off, but most of the items in the list have been announced for deprecation since at least SQL 2005, so it’s time to consider their replacement and/or retirement.  Items up for consideration:

  • Databases in 90 compatibility mode; expected to require 100 compatibility mode to upgrade to SQL 12
  • replace the DATABASEPROPERTY function with the DATABASEPROPERTYEX function
  • replacing sp_dboption stored procedure with ALTER DATABASE
  • remove usage of *= or =* (use the ANSI Join syntax)
  • sqlmaint utility
  • And more…considering many of these were carry-overs from SQL 2008/R2 and expected for removal in Denali, they should already be on your priority list as they may still be removed in Denali since the Denali BOL notice still includes the “subject to change” wording.

If you are really ambitious, then start perusing the list of “Features Not Supported in a Future Version of SQL Server”.  And, yes, some of these look familiar as they have been previously announced. Most notable that you may have missed in SQL 2008 deprecation announcements: Not ending Transact-SQL statements with a semicolon.  Yes – you read that right! At some point in a future release of SQL Server, T-SQL statements will finally be required to end with a semicolon (;).  That’s an easy one – this is an ANSI SQL standard and has been supported since at least SQL Server 2000. It is already required for some T-SQL statements (such as the statement prior to a WITH defining a CTE and the MERGE statement), so just start typing that extra character and get used to it.

As RTM for Denali gets closer, I’ll revisit this topic to note any significant changes.

SQL Server Deprecated Features – Part 3

In this third installment of my mini-series on SQL Server deprecated features (see part 1 and part 2), I will look further at tools to help detect the usage of deprecated features.

SQL Server 2000

For SQL Server 2000 instances, you are pretty much stuck with using the Upgrade Advisor tool for the version of SQL Server to which you are upgrading. In addition, you need to read BOL Backwards Compatibility (links available in part 1) as a reference and manually search your code and database configurations.

SQL Server 2005

Starting with SQL Server 2005, Microsoft has added some detection capabilities into the SQL Server Profiler tool.  Two new event classes were added to report “Deprecation Announcement” feature usage and “Deprecation Final Support” feature usage.

Per the text inside Profiler when “Deprecation Announcement” is selected: Occurs when you use a feature that will be removed from future version of SQL Server, but will not be removed from the next major release of SQL Server…

Per the text inside Profiler when “Deprecation Final Support” is selected: Occurs when you use a feature that will be removed from the next major release of SQL Server…

If you setup your profiler trace to record to a table (I named mine Deprecated_Features_Trace_tb), then you can query it as follows:

SELECT * FROM [dbo].[Deprecated_Features_Trace_tb]  
WHERE EventClass IN (125,126)

EventClass 125 corresponds to “Deprecation Announcement” and EventClass 126 corresponds to “Deprecation Final Support”.

Ironically, you may find that you want to use a Column Filter on ApplicationName to exclude the Profiler tool and SQL Agent (and maybe other internal Microsoft features) which are currently using deprecated features themselves.  For example, when recording the profiler results to a table, the table is created using ntext and image datatypes which have been deprecated and are therefore reported events. Alternatively you could filter on DatabaseName and exclude the system databases.  However, you need to be absolutely sure that no one has created non-Microsoft supplied objects in those databases if you chose to filter on database name.

SQL Server 2008

In SQL Server 2008, an explicit performance counter object has been added for deprecated features with specific “instances” of deprecated features. This enables you to use the DMV sys.dm_os_performance_counters to query if the usage of any of these features has occurred within your SQL Server 2008 instance.

SELECT * FROM sys.dm_os_performance_counters 
WHERE [object_name] LIKE '%:Deprecated%' AND cntr_value > 0

Of course, this won’t tell you where the deprecated features are used, but at least you will know whether you need to go hunting in-depth.

Another new feature in SQL Server 2008 which can help with this task is Extended Events. You can setup an Event Session to capture both “deprecation_announcement” events and “deprecation_final_support” events.  If you are new to Extended Events, then you must read Jonathan Kehayias’ old blog and/or new blog – these links point specifically to all of his Extended Event tagged entries.  Jonathan has written a “XEvent a Day” series and much more. If you want to learn about Extended Events – he’s the man!

Final Thought for Detecting Deprecated Features

For all versions, however, no matter what tools you’ve initially used, always, always, always run the Upgrade Advisor.