Learn Something New (or Old) Every Day

I’m a big proponent of constant learning – looking to learn something new every day. It doesn’t have to be some big revelation. Sometimes it is just a tip on how to do something more efficiently (like in PowerShell) and sometimes it isn’t even new!

Recently, I was in a discussion about proposing a best practice recommendation in the CIS SQL Benchmarks to ensure deleted Active Directory Windows logins are also removed from all SQL Server instances where they were granted a login. One of the team members did a little research and found a reference to a system stored procedure which might help – sys.sp_validatelogins. My first question was – is it a documented procedure? Microsoft warns against using undocumented commands as they could get changed with most any update. Yes, it is officially documented in BOL (2008+). Second question – since which SQL Server version? Since at least SQL 2000 per this BOL! I have to confess, as many years and versions that I’ve been working with SQL Server and researching various security aspects, I was surprised that I didn’t recall this procedure – especially when the first non-BOL reference that I found in my own search turned up an article written by my friend Tim Ford for MSSQLTips!

In large, complex environments, both the processes (coordination between teams with varying responsibilities) and the technical aspects (how to identify these logins) can be, well, complex! But, depending upon your AD structure and the trusts in place, you as a DBA could periodically run this system stored procedure on your instances to find Windows logins or groups which are SQL Server logins but no longer exist in AD. You can then do a more thorough search of the specific instance’s databases and remove the login from all databases where it is a user, ensure that it isn’t a database owner, and ultimately remove the login from the instance. There is no currently known security risk to leave these orphaned logins on your SQL Server, but just like cleaning up orphaned users in your databases which do not have a specific instance login, it is considered a best practice to perform this task. And, for those of us who are neat freaks – it just makes your instance “cleaner” to get rid of the clutter of obsolete logins!

While it would be tempting to automate this check and just drop the database users, then drop the login, I did find that Thomas LaRock documented an anomaly he found several years ago which would make me always want to manually double-check the AD for any accounts reported as orphaned to ensure the account is really no longer valid. But at least you have narrowed down the search by using this procedure.

So, when looking to learn something new – don’t forget sometimes what you may learn is old – both Tim’s and Tom’s blogs were from 2009!

SQLSaturday #362 Austin – I’m Presenting

SQLSaturday is finally happening again in Austin (technically in Round Rock) on January 31, 2015 and I’m glad to announce that I will be presenting. My topic is “CIS Benchmarks: Your Guide to Consensus Security”. The Austin SQL folks have been great participants at the past SQLSaturdays that we’ve held in Houston, so I’m glad to promote and participate in their event. There is a great lineup of speakers and topics which you can see here. There are also a couple of pre-conference seminars on Friday. Check out all info for content and registration here.

If you aren’t familiar with SQLSaturday, this is a FREE training event put on by local SQL Server User Groups. The event concept has grown greatly over the past several years and on almost every Saturday of the year, there is one or more SQLSaturdays occurring somewhere in the world.

There are just a few more days to register – hope to see you there!

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. 

This 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)!

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.

This 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+:

• dc_admin – see BOL for description of permissions for Data Collector & MDW roles
• dc_operator
• dc_proxy
• mdw_admin
• mdw_reader
• mdw_writer
• PolicyAdministratorRole – members can manage policies
• ServerGroupAdministratorRole – members can manage the CMS
• ServerGroupReaderRole – members can read from the CMS

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?”.

PASS Summit 2011 – What I Learned

In prior posts, I reflected on the things that I think make the PASS Summit such a great value – content, volunteers, Microsoft support, and networking opportunities.  This post will concentrate on the content provided at this year’s Summit and basically what I learned over those 3 days. 

In no particular order – here’s the brain dump (from my notes, of course, with occasional commentary thrown in!):

• SQL Server Code Name “Denali” will officially be SQL Server 2012 and released in the first half of 2012.
• Project Crescent is now Power View.
• Microsoft is now promoting both on-premise databases and cloud-based databases as co-existing for the foreseeable future; but the cloud is getting bigger for SQL Azure (150 GB databases supported by year-end).
• Big Data has arrived and Microsoft has taken notice – and action – by releasing the Microsoft SQL Server Connector for Apache Hadoop … with more coming in this arena.
• I run the Houston Area SQL Server User Group so these next items are of importance to me personally:
  • PASS may finally establish a speaker bureau to help local chapters find speakers
  • PASS will be increasing services to chapter leaders, providing DNN (DotNetNuke) training, etc.
• At (Principal Architect Escalation Engineer in Microsoft CSS) Bob Ward’s “Inside Tempdb” half-day session:
  • The model database in Denali SQL Server 2012 will be 4MB up from the 2MB it has been forever. Why is this important? Well, recovery isn’t complete until tempdb is started and the default size for tempdb (if you don’t change it) is the size of model. 4MB isn’t much these days, but it is a change you should be aware of.
  • tempdb is the “garbage dump of the engine”. It is used for user objects such as temp tables, table variables, temp procs, user-defined objects and online index space. It is also used by internal objects – sorts, work tables, work files (used for hash joins) and version store. 
  • Consider moving tempdb to its own storage volume if it has heavy I/O. SSD is an option, but as expensive as that is ensure you can’t make better use of it somewhere else first.
  • Bob’s rule of thumb for how many data files to allocate for tempdb:
    • Less than 8 CPUs (cores), 1 file per CPU
    • 8 or more CPUs, start with 8 files and increase by sets of 4 until contention resides
    • Also see Paul Randal’s blog for a slight variation on this theme.
• At the “What’s New in Manageability in Denali” Microsoft session:
  • Since SSMS is now built on Visual Studio 2010, you will get multi-monitor support! Hooray!
  • There is a new Database Recovery Advisor which will be able to build a restore plan for you based on available backup files (database, log, etc.) in a folder even if you don’t have any msdb backup history. It can also handle split backups.  I’d cheer for this too, but seriously – this should have been in the product years ago!
  • Log viewer will now work with offline instances and has improved performance.
  • SCOM Management Pack (will be released at the same time as SQL Server 2012)
    • Ability to discover Availability Groups
    • Detailed knowledge of AlwaysOn tasks
    • Performance counter collections for AlwaysOn
    • Policy-Based Management integration, yes, integration – it’ll pull your PBM policies right into SCOM and alert on failures
    • Enhanced mirroring support – discovers and diagrams
    • 20 new rules for replication
    • Support for mount points (another “hooray/finally!”)
• At the “AlwaysOn: Active Secondaries” Microsoft Session:
  • You can offload backups to any replica so as to not impact production. The LSN is communicated back to the primary which then notifies all replicas so that all log backups in all replicas form a single log chain.
  • If that sounds like a nightmare for a restore, remember the Database Recovery Advisor mentioned previously and restore is simple!
  • Differential backups are not supported on secondaries.
  • Only “Copy Only” full backup is supported on secondaries.
  • Advisable to store backups centrally (so you can just point the Database Recovery Advisor to a single location to create the restore plan).
  • Declarative policy to determine where backups occur automatically. This is advisory only, not enforced. Implement via a system function which returns a Boolean indicating if this is the preferred backup location.
  • And, of course, probably the number one reason for multiple secondaries (besides general DBA paranoia) is to offload reporting workloads without having to use database snapshots.
• In MVP Tim Ford’s “Periodic Table of DMOs” session:
  • Using the format of the Periodic Table of the Elements from general science, Tim gives us a creative way to organize all the DMOs (Dynamic Management Objects – views and functions) now available to DBAs for troubleshooting and general understanding of what is happening within a database engine instance. I might actually use this to remember when and what to use.
• SQL Server guru Paul Randal (blog) busted more DBA Myths in a spotlight session. As usual, not all myths were blatantly true or false, there were a few “it depends”! This session lasted until 6:30pm and I think most would have stayed until 9:30pm to listen to Paul’s in-depth, but humorous explanations about why these myths were true/false or especially “it depends”.  
• Allan Hirt, Clustering MVP, demonstrated “Denali on Windows Server Core”:
  • Currently Denali is only supported on W2K8R2 SP1, no Windows 8 support announced yet (as of October 14, 2011).
  • You’ll need to add .NET and Allan will post on his blog how to do this effectively
  • You’ll need to add PowerShell 2.0. See KB976736 and use option 2!
  • If installing a Windows Cluster, then all nodes must be Server Core or all must allow GUI.
• Microsoft Senior Program Manager for the Database Engine Security features, Il-Sung Lee, presented “What’s New in Security for SQL Server Code Name Denali”:
  • Windows groups can now have a default schema. The first group with the lowest principal id will be chosen if a user belongs to more than one group.
  • User-defined schema roles
  • All SKUs will now have the ability to specify server audits; database audits still require Enterprise Edition or higher.
  • User-defined audit events (via sp_audit_write)
  • Database Authentication (for contained databases)
  • Lots of cryptography changes – deprecating older methods, supporting newer, more secure ones
  • Il-Sung’s team blogs at: http://blogs.msdn.com/b/sqlsecurity/
• SQLCAT brought in speakers from 4 companies who are early adopters of Denali & AlwaysOn to discuss their HA/DR requirements and migration paths to AlwaysOn. I’ll definitely be revisiting their solutions in the future and suspect there will be some whitepapers posted on SQLCAT’s website soon.
• In “Where should I be encrypting my data”, MVP Denny Cherry provided a good refresher on some encryption basics:
  • Encryption will almost always increase the size of stored data
  • Encryption will decrease the usefulness of data deduplication
  • Encryption will add CPU load
  • Enabling TDE for an application database will also enable it for tempdb
  • TDE is for “data at rest” including backups
  • For “data on the wire” use IPSEC or SSL
  • If your storage uses MPIO, then “data at rest” protection at the LUN level. If you copy a file to another server, it would be readable; if you detach and attach the LUN elsewhere it would not be readable.
• I also attended one of the “Lightning Talk” sessions, where 8 speakers had 5 minutes each to convey their topic to the audience. Some were humorous, some were serious, and one was seriously humorous! Grant Fritchey’s “Backup Testing, The Rant” is not something any of us present will ever forget – nor will we ever forget the importance of actually testing your backup! I really wish that had been caught on video, because the audio on the DVD just won’t do the presentation justice. Two other speakers in the session covered topics near and dear to me – “Thinking of Hosting a SQLSaturday?” (John Sterett) and “Build Up” (Niko Neugebauer). Both encouraged attendees to get involved in their local SQL Server community activities – and if you don’t have a local group – start one!

Now to start making the list of all the interesting sessions I couldn’t get to that I’ll want to watch (or re-watch) as soon as the Conference DVDs arrive! I foresee team “lunch ‘n learns” being scheduled.

Password Generation Using PowerShell

While using Windows Authenticated Logins easily falls into security best practices, beginning with SQL Server 2005 installed on Windows 2003 servers, SQL authenticated logins became easier to manage to enterprise security guidelines. 

To ensure that SQL Authenticated logins follow the same password policies as defined for Windows for password complexity, account lockout, and password expiration when creating SQL Logins using T-SQL, DBAs should specify the CHECK_POLICY=ON option and the CHECK_EXPIRATION=ON option of the CREATE LOGIN command. 

CREATE LOGIN <mySQLLogin>

     WITH PASSWORD = <enterReallyStrongPassword123!>,

     CHECK_EXPIRATION = ON,

     CHECK_POLICY = ON

If creating the login using SSMS, then be sure the options “Enforce password policy” and “Enforce password expiration” are checked. Of course, if you use the “Enforce password expiration” option, users must have a mechanism for changing their password. 

Back in the SQL 2000 days, I wrote a lengthy VBScript to generate a random password to use when changing the sa login (which should be disabled in SQL 2005 and beyond).  However, the basics for this task (generating the password) can now be performed in two simple lines of PowerShell!  And since DBAs will need to set a complex password when creating SQL Authenticated Logins, these two lines can come in handy to ensure a random password is generated for the login.  And, of course, you can use it next time you need to create a new  password for any other account, too!

[Reflection.Assembly]::LoadWithPartialName(”System.Web”) | Out-Null

[System.Web.Security.Membership]::GeneratePassword(16,2) 

The above 2 lines will load the necessary .NET Framework assembly containing the System.Web namespace.  The GeneratePassword method with the specified parameters will generate a random password 16 characters long, with at least 2 non-alphanumeric characters.

Put the two lines above into file with a filetype of .ps1 – for example, Gen-Password.ps1.  You can then run it by typing from a command prompt: powershell <PathToScript>\Gen-Password.ps1.  Occasionally, the password generated will contain a character not allowed in SQL Server passwords, so be sure to double-check the value generated.