Always Encrypted with Secure Enclaves

SQL Server 2019 preview Always Encrypted uses an enclave technology called Virtualization Based Security (VBS) memory enclaves. A VBS enclave is an isolated region of memory within the address space of a user-mode process.

The capabilities this brings are

• In-place encryption. Encrypt column, rotate a column encryption key, or change an encryption type of a column, without moving your data out of the database
• Rich computations. The engine can delegate some operations on encrypted database columns to the enclave. It can decrypt the sensitive data and execute requested operations in a query on plain text values.

Always Encrypted with secure enclaves allows computations on plain text data inside a secure enclave on the server side. Microsoft define a secure enclave as a protected region of memory within the SQL Server process. It acts as a trusted execution environment for processing sensitive data inside the SQL Server engine. A secure enclave is a black box to SQL Server and other processes on the server. It is not possible to view any data or code inside the enclave from the outside, even with a debugger.

You can now try and evaluate Always Encrypted with secure enclaves in the preview of SQL Server 2019.

This shows what an admin would see when browsing the enclave memory using a debugger (note the question marks, as opposed to the actual memory content).

Reading

Always Encrypted with Secure Enclaves – Try It Now in SQL Server 2019 Preview! 
Always Encrypted with Secure Enclaves

SQL Server 2016 Service Pack 1

SQL Server 2016 SP1 is released with key innovations accessible across all SQL Server editions. Microsoft want to make it easier for developers and partners to build and upgrade applications that take advantage of advanced performance, security, and data mart capabilities. Full details are here http://bit.ly/2eH8VGJ  

Features now available in all versions

Mainstream Support Ends

Mainstream Support for SQL Server 2008 and SQL Server 2008 R2 will end on  8 July 2014.  The definition of Mainstream support is shown in this table. 


Taken from http://support.microsoft.com/lifecycle

Service Pack 4 for SQL Server 2005

The next service pack for SQL Server 2005 has been released. Service pack 4 includes cumulative update 1 to 11, some customer requested fixes as well as database Engine support for DAC operations.

It can be downloaded here.

Microsoft SQL Server 2008 Service Pack 2 is released

Microsoft SQL Server 2008 Service Pack 2 is released and can be downloaded

Service Pack 2 contains updates for

SQL Server Utility
Data-Tier Application (DAC)
Integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies
Support for a maximum of 15,000 partitions in a database
It includes SQL Server 2008 SP1 Cumulative Update 1 to 8

Data Collection Security

There are 3 roles

Mdw_admin
Grants read and write to the Management Data Warehouse (MDW). It allows users to purge and clean up jobs to manage the amount of data
Mdw_reader
Grants users with read only access
Mdw_writer
Allows write and upload of data to the MDW

Stop the login SID mismatch

To stop the user SID's mismatch in SQL Server 2005 for databases that are continually moved and restored from one database server to another

Run a script to collect the current sid of the user after a user database has been restored

username = bennett
USE ;
GO
SELECT sid FROM sysusers WHERE name = 'bennett';
GO

Then create a server login. Use the SID from the above query output and replace the username and password with the relevant details.

USE master
CREATE LOGIN [bennett]
WITH PASSWORD = 'Ax$ef6!f', SID = 0xA2AE403E43ED084C8B3021E7E8DFD61C,
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Then create a database user for bennett, with the default schema dbo.

USE ;
CREATE USER [bennett] FOR LOGIN [bennett]
WITH DEFAULT_SCHEMA = dbo;
GO

When you restore the database again the database user continues to work with no further action required.

Login Failed Error States

The common security error states and their descriptions for the error 18456 severity 14 state 8
are listed below:-

ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

SQL Server security issues

Microsoft Security Bulletin http://www.microsoft.com/technet/security/bulletin/ms09-004.mspx issued in February 2009 is not an issue if you have SQL Server 2005 SP3 installed. If this is problematic to be installed, due to the fact that rollback requires the uninstall and reinstall of SQL Server, a fix to the vulnerability is

USE master
GO
DENY EXECUTE ON sys.sp_replwritetovarbin TO public
GO
NOTE: don’t use this with Transactional Replication with Updateable Subscriptions

The other fairly recent security issue from July 2008 is
http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx

Types of patches

Quick-Fix Engineering (QFE)

The QFE is a Microsoft term for the delivery of individual service updates to its operating systems and application programs. Formerly called a hotfix, QFE can be used to describe both the method of delivering and applying a patch or fix, and also to refer to any individual fix.

General Distribution (GDR)

A GDR is typically associated with a critical patch release the product groups feels should be provided to and installed by the whole customer base. GDRs are often associated with security releases, or other critical fixes needed to mitigate a problem the majority of customers will experience when using our product.

Cumulative update package (CU)

Multiple cummulative updates are released between Service Packs and contains all of the applicable hot fixes at the time.

Service Packs (SP)

When there are a large enough collection of changes they are gathered together to be released as a service pack. A service pack can also deliver new product features.

SQL Injection

SQL Injection is a common vulnerability which can lead to the database being disabled, the web site displaying malicious contnet or even the the database can be destroyed. . Attacks come primarily through Web applications that allow an attacker to execute their own SQL commands on the application database. The defense against SQL-injection attacks must be code-based.

Protecting your code

A few simple steps you can take to protect your Web applications from SQL-injection attacks.

-Principle of Least Privilege
The account an application uses to connect to the database should have only the privileges that application requires.

-Validate All Input
Make the input is what you expect it to be.

-Avoid Dynamic SQL
Dynamic SQL is a great tool for performing adhoc queries, but combining dynamic SQL with user input creates exposure. You should replace dynamic SQL with prepared SQL or stored procedures .

-Use Double Quotes
Replace all the single quotes that your users' input contains with double quotes. Single quotes often terminate SQL expressions and give the input more power than is necessary.


Tools to help prevent and identify SQL Injection

Ensure the SQL Server is fully patched to include all security vulnerabilities

HP Scrawlr

Scrawlr, developed by the HP Web Security Research Group in coordination with the MSRC, is short for SQL Injector and Crawler. Scrawlr will crawl a website while simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities. Scrawlr is lightning fast and uses our intelligent engine technology to dynamically craft SQL Injection attacks on the fly.
Download here
https://h30406.www3.hp.com/campaigns/2008/wwcampaign/1-57C4K/index.php?mcc=DNXA&jumpid=in_r11374_us/en/large/tsg/w1_0908_scrawlr_redirect/mcc_DNXA

urlscan
UrlScan version 3.0 Beta is a Microsoft security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process.
Download here
http://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1697


Microsoft Source Code Analyzer for SQL Injection tool
The tool is a static code analysis tool that helps you find SQL injection vulnerabilities in Active Server Pages (ASP) code.
Download here
http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en

SQLInjectionFinder
SQLInjectionFinder (v1.5.0) Tool to help determine .asp pages targeted by recent SQL Injection attacks
Download here
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436

Policy Based Management

Terminology

Facet - Defines a management area within the policy based framework for a predefined set of properties
Condition - contains a single condition that you want to enforce that evaluates to True or False; i.e. the state of a Facet
Policy - defines the permitted stated for properties of a single facet and is a set of conditions specified on the facets of a target.
Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.

Order creation

Select a facet that contains properties you want to configure
Define a condition that specifies the permitted states of the facet
Define a policy that contains the conditions and sets one of the evaltion modes

Policy evaluation modes

• On demand - The policy is evaluated only when directly ran by the administrator.
• On change: prevent - DDL triggers are used to prevent policy violations.
• On change: log only - Event notifications are used to check a policy when a change is made.
• On schedule - A SQL Agent job is used to periodically check policies for violations.

Best Practice for Security

The white paper entitled
Best practices for setting up and maintaining security in SQL Server 2005
can be downloaded here

” http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

SQLPing

SQLPing 3.0 performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise.

Download from
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx