• 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.

SQL Server 2012 Security Benchmark Released

The Center for Internet Security (CIS) Security Benchmarks Division released “CIS Microsoft SQL Server 2012 Database Engine Benchmark V1.0.0” on January 6, 2014. 

CIS_SQL2012_Benchmark_V1This 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! 

I am currently serving as one of the editors for the SQL Server benchmarks. We are in progress with updating the SQL Server 2008 R2 benchmark previously released in late 2012.  If you discover any items we should update\add\delete in that document or in the newly released 2012 benchmark, please either leave a comment here on my blog or better yet join the benchmark community consensus team (http://benchmarks.cisecurity.org/community)!

SQLSaturday #150 – Baton Rouge: Presentations Uploaded

My presentation slide decks and demo scripts from SQLSaturday #150 have been uploaded.

Thanks to the planning team for selecting my sessions and thanks to everyone who attended my sessions – I enjoyed the opportunity to share my passion.   

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!

Discontinued Features in SQL Server 2012 – Part 3

Part 1 discussed some discrepancies between documentation of discontinued features in SQL Server 2012 and actual implementation. Part 2 discussed the database compatibility level minimum requirement. Now, in this installment, let’s look at some features which have disappeared from SQL Server 2012 which may impact your administration scripts.

The first one, the DATABASEPROPERTY function, I already covered in Part 1. While it doesn’t appear to be completely gone, the documentation states that it is gone. So, as you edit your scripts for all the other items mentioned next don’t forget to make the change to use DATABASEPROPERTYEX instead of DATABASEPROPERTY.

Next up, sp_dboption is gone. That’s right – it’s not there… You get a nice red error message in SSMS when you try to use it.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

Everything you needed to set using sp_dboption is now available via the ALTER DATABASE syntax – and pretty much has been since SQL Server 2000. But, if you are as “experienced” as I am (going back to 4.21), then you may still have some old scripts laying around with sp_dboption in them.  Unless you still have some 7.0 or earlier systems on which to use those scripts – update them to use ALTER DATABASE and they should work on SQL Server 2000 and up (with an exception for the database ownership chaining option which was added in SQL Server 2000 SP3 and only available to set via sp_dboption in SQL Server 2000).

Now – how about a review of your Backup and Restore scripts?  Did you make use of the WITH PASSWORD or WITH MEDIAPASSWORD options on your BACKUP DATABASE and BACKUP LOG commands? If so, you’ll need to remove those options for use on SQL Server 2012. These options have been deprecated since SQL Server 2005, so in theory you should have stopped using them at that point in time. You can still use these options with the RESTORE command for those old backups which used a password, but you can’t make any new backups using the password options.  Keep in mind though that the ability to use the RESTORE command for backups with passwords is deprecated and will be removed in future version.

This next one will really date you (and me!) – do you have RESTORE DATABASE…WITH DBO_ONLY syntax in any scripts?  If so, you should have replaced WITH DBO_ONLY as WITH RESTRICTED_USER starting with SQL Server 2000.

Do you run scripts to dump the meta-data from the system tables\views?  Do they include the sys.database_principal_aliases view? If so, it is gone now, too. You’ll receive error 208 (invalid object name) if you reference it.  Aliases have been replaced by roles.

OK – so now you know what to look for, but how about an easy way to find where these keywords are used in your scripts?  Here’s a quick PowerShell command example (adapted from http://guyellisrocks.com/powershell/powershell-grep/) to help: 

Get-ChildItem -include *.sql -recurse | Select-String “sp_dboption”

Have fun hunting down these obsolete keywords!

Discontinued Features in SQL Server 2012 – Part 2

Previously I discussed a couple of features where there is a discrepancy between the documentation and the implementation as to their status as either discontinued or deprecated in SQL Server 2012. Today, I’m looking at another discontinued feature which you will need to identify and address before upgrading to SQL Server 2012 – database compatibility level 80.

A database’s compatibility level is used by the database engine to ensure that most behavior is consistent to that version for the specific database.  This database-level setting is intended as a work around to allow applications to upgrade to a newer version of the database engine for which usually all databases can derive some benefits without requiring the application to make any changes (in theory). If an individual application database is using a feature or syntax previously supported for which the behavior changed in the later version, then you can set a down-level compatibility to keep the original behavior from that version….until your application development team or your vendor can make the code compatible with the current version of SQL Server.  

Beginning with SQL Server 2008, Microsoft’s announced life cycle policy for SQL Server was that a new version will attempt backwards compatibility with only the 2 previous versions. Since databases in SQL Server 2012 are compatibility level 110, then only compatibility level 100 (used for both SQL Server 2008 and SQL Server 2008 R2) and compatibility level 90 (SQL Server 2005) are additionally supported.  Thus, database compatibility level 80 (SQL Server 2000) will not be supported in SQL Server 2012 and is therefore classified as a discontinued feature.

If you are a fairly new DBA who inherited your database systems and don’t know their history, you’d better check all the databases carefully for compatibility level.  While SQL Server 2008 and SQL Server 2008 R2 both support databases in 80, 90, and 100 compatibility mode, SQL Server 2005 supports databases in 70, 80, and 90 compatibility modes (and allows 60 & 65!).  Thus, although you can upgrade a SQL Server 2005 instance to SQL Server 2012 – you need to check that all of the application databases are at compatibility level 90. You might be surprised (or not) to find that you still have databases which exist with compatibility level 6x on your SQL Server 2005 instances! 

So, how do you find these? One way is to use a multi-server query using a CMS group containing all your servers…

Select [name],[compatibility_level] From sys.databases

Where [compatibility_level]< 90;

How do you fix it? Well, ultimately you have to run one of these commands:

— If source system is SQL 2005

ALTER DATABASE reallyold_database


— If source system is SQL 2008 or 2008 R2 and compatibility_level is 80,

— then you might as well take it as far as you can go!

ALTER DATABASE reallyold_database


But, first you’ll have to figure out what is keeping it from being at the higher compatibility level already.  Check the SQL Server documentation under “ALTER DATABASE Compatibility Level (Transact-SQL)” for details of differences between compatibility levels. Be sure to check the SQL Server 2008 version of the documentation for differences between the 80 and 90 compatibility levels. In some cases, it is simply that the database was upgraded and the former DBA didn’t realize that they needed to change the compatibility level after the upgrade.  And, of course, don’t you now forget to change upgraded databases to compatibility level 110 after you complete the upgrade to SQL Server 2012!


Discontinued Features in SQL Server 2012 – Part 1

Now that SQL Server 2012 has officially been released, it is time to start revisiting deprecated features.  But, first, a definition reminder:

  • “Discontinued” means that the feature is not available in SQL Server 2012.
  • “Deprecated” means that the feature will be removed in a future version.  “Deprecated” also typically means that there are no updates to this feature in the current release.

Thus, I will first review a couple of the features which you may have used in prior versions which were deprecated in earlier versions and now discontinued in SQL Server 2012 – or are they?

SOAP/HTTP Endpoints

Per the note in the CREATE ENDPOINT BOL link this feature is discontinued. However, it is also currently listed in BOL under “Deprecated SQL Server Features in SQL Server 2012” which implies it won’t go away until a release after SQL Server 2012.  So which is it – gone already or still waiting to go?  A test of running sample code to create such an endpoint in SQL Server 2012 results in the error message shown below supporting it is now actually discontinued, not deprecated for removal later. Thus, if you have used these endpoint types, you will need to find them and convert to Windows Communication Foundation (WCF) or ASP.NET before upgrading to SQL Server 2012. Check this BOL reference for assistance.

Here’s the sample code that was executed which was copied from SQL Server 2008 R2 BOL:


This function has been replaced and superseded with the DATABASEPROPERTYEX function since SQL Server 2005.  Intellisense in SSMS 2012 will flag that it doesn’t recognize this as valid syntax (see image below).  The unexpected thing here is when you execute a query using DATABASEPROPERTY in SQL Server 2012 – you don’t get an error!  It seems to still work (see results image below). I don’t know about you – but this was not the result I expected.  If DATABASEPROPERTY is no longer valid as indicated in SSMS (and BOL) – shouldn’t the execution return an invalid function error?  At any rate, it appears you may have a little longer to track down your usage of this function and update it to use DATABASEPROPERTYEX as DATABASEPROPERTY does still work for the time being.   

Obviously, it doesn’t hurt to start replacing the usage of deprecated features as soon as possible. Then, you don’t have to worry as much about which future version actually discontinues the feature, if you have already discontinued your usage of it.