May 30, 2014

Today, I want to discuss some of the security enhancements in SQL Server 2014, but before I do, have you ever considered how SQL Server compares to other database platforms? Glad you asked. According to the National Institute of Standards and Technology (NIST) Common Vulnerabilities and Exposures (CVE), the Oracle database platform has recorded 335 security vulnerabilities in the period 2003 to 2011, the highest total of any major vendor. In comparison, Microsoft SQL Server has recorded 27 security vulnerabilities over the same period. The difference is nearly 12.5 times more for the Oracle database platform than SQL Server. SQL Server well and truly deserves its top ranking. To be honest, the situation is much direr for Oracle DBAs. The patching process is quite complicated, to the point where, anecdotally at least, many of the vulnerabilities in Oracle systems remain unpatched. Microsoft continues to invest significantly to improve their security; they take it quite seriously in fact. You can find a very interesting comparison here.  This is just a single comparison, but SQL Server is ranked first in this area for good reason. Let’s look at some of their enhancements in SQL Server 2014. There are quite a few areas that have seen changes in SQL Server 2014. A number of these improvements actually occurred in SQL Server 2012. I’ll cover just a few things that I consider “interesting.” Account Provisioning There are a couple of noteworthy changes here. Firstly, with previous versions of SQL Server the ‘BUILTINAdministrators and Local System (NT AUTHORITYSYSTEM)’ were automatically added to the sysadmin role. This is no longer the case. This effectively means that you need to be more careful. You can certainly add any designated accounts to the sysadmin role; you just don’t get them by default. Secondly, you can specify a managed service account during setup. A managed service account is a domain account that is assigned to a specific member computer and used to run services on that computer. You cannot log on to a computer by using a managed service account, and its password is created and managed automatically by a domain controller. New Permissions SQL Server 2012 added 19 new permissions. SQL Server 2014 adds 4 new server-level permissions. You can always check those permissions with:

SELECT * FROM sys.fn_builtin_permissions(”);

New Transact-SQL Role Management The stored procedures ‘sp_addrolemember’ and ‘sp_droprolemember‘ have been deprecated (this means they’re still there for backwards compatibility, but they might disappear wholly in a future version). Instead, you should now use the ‘ALTER SERVER ROLE’ statement with either the ‘WITH ADD MEMBER’ or ‘WITH DROP MEMBER’ clauses. This leads me to a very interesting development… User-defined Server Roles We’re quite familiar with the concept of working with roles (hopefully). In the past, we have had fixed server and database roles as well as the ability to create custom, or user-defined, database roles. I have always found it strange, to be honest, that we haven’t had the ability to create a custom server role; before now that is. There is a principle in security that states that you only give the permissions that are absolutely required and nothing more. This is known as the ‘Principle of Least Privilege.’ In the past, it has been difficult to reconcile using server roles with this principle since using any of the fixed server roles meant that a member of those roles would inherit an immutable set of privileges, regardless of whether or not they were required. Well, fear not, you can now rest soundly knowing that now, you can apply the “Principle of Least Privilege” with impunity. That’s right, you can create User-Defined Server Roles…WOOHOO!!! I know you’ve been waiting for this just like I have. Contained Databases Have you ever moved a database from one instance or server to another? Do you remember what happened? Yep, mismatched SIDs. Do a search; I just did, you’ll get over 1.5 million hits. It’s well known, and it’s a pain. So Microsoft decided to do something about it. They created the concept of the Contained Database. That’s a database that doesn’t have any dependency on the server on which it resides. So there’s no login, only a user – a “Contained User” to be precise. That means that you will be authenticated by the database and not the server. Very cool. You can move these suckers around all you like and you won’t hit the mismatched SIDs problem. Awesome!