SQL Server vNext items being worked on
Better version of the slide from aka.ms/bobwardms -> SQL2017
Always Encrypted currently can either use Randomized or Deterministic Encryption
Deterministic Encryption is based on the ability to perform operations on Encrypted Values
With Always Encrypted there is a key hierachy, data is Encrypted with a Column Encryption Key which is in turn encrypted by a Column Master Key. It is the Column Master Key which the server side does not know.
For the server to perform searches the client driver actual encrypted the search values and ask the server to match on the encrypted value!
This means the server can either at best do equality joins and group by (when using Always Encrypted Deterministic encryption) or point queries (when using Always Encrypted Randomized encryption)
We can see this via an extended event
Always Encrypted using Enclaves has a secure "enclave" on the server side which the server or even administrators cannot access and when can run code but the code has to be signed first!
With Always Encrypted using Enclaves the engineers have refactored SQL Server to push query processing code accessing the Encrypted values into the secure enclave!
(Image is cropped, enclave is off-screen on the right, see next photo!)
The enclave also has to be trusted hence we have enclave attestation this is either done via Intel SGX CPU Extensions (which also need to be enabled in the BIOS/firmware) or via a trusted hypervisor (TPM 2.0 also required)
To enable use of the when creating the Column Master Key we have to use a new option "Allow enclave computations"
This results in "enclave enabled" Column Master Keys
FYI This was done on a SSMS special build which is not out yet!
The client sends an signed encrypted "search pattern" to the server which in turn sends this to the secure enclave
The enclave can then decrypt this and get a description of what query operations to perform
Since the enclave contains trusted code which can do query operations we now can do range and like queries on encrypted columns(!)
There is an Early Access Preview for Always Encrypted using Enclaves, NOTE: This is currently Azure only - not on-prem.
I was able to talk to Jakub Szymaszek @JakubSzymaszek after the session
Another advantage of Always Encrypted using Enclaves is that to take an unencrypted column and make the column encrypted (or change encryption keys e.g. key rotation) does NOT require shipped all the data to the client to be encrypted
Microsoft therefore expect the encryption process to be faster when Always Encrypted using Enclaves is used,TO BE CONFIRMED!
Slso do not need to worry about client cpu and network bandwidth to client when encrypting/re-encrypting columns
How does the signed code get to the Secure Enclave initially? This is done via a secure tunnel into the enclave
The enclave has a limited surface area!
On first use the client driver and enclave negotiate a shared secret and then setup the secure tunnel
Currently about 20 second pause on first use for this but this has not been tuned yet, this really is an "Early" Preview!
After this it is normally speed to run range queries,like etc
Intel SGX Documentation - Intel® Software Guard Extensions (Intel® SGX) SDK
Azure Confidential Computing uses "a Trusted Execution Environment (TEE - also known as an enclave),"
Initially 2 TEEs ae supported - "Virtual Secure Mode and Intel SGX. Virtual Secure Mode (VSM) is a software-based TEE that’s implemented by Hyper-V in Windows 10 and Windows Server 2016.".
i.e. either hardware (Intel SGX) or hypervisor (Hyper-V) based enforcement Introducing Azure confidential computing
Windows API for Secure Enclaves Trusted Execution Reference
To check if enclaves are supported on your machine call IsEnclaveTypeSupported function and "If the function fails, the return value is NULL" then call GetLastError and check if ERROR_NOT_SUPPORTED is returned
There are 2 types of enclave - ENCLAVE_TYPE_SGX (Intel SGX based) and ENCLAVE_TYPE_VBS (a virtualization-based security (VBS) enclave)
There were at least 2 Windows implementatons of SGX enclaves - Intel SGX SDK under Windows 10 threshold 2
"Pre- Windows Update 2, the SGX Ring0 Instructions were implemented using the SGX Driver included in the SGX PSW and was installed as a registered ACPI device."
"Windows 10 Update 2 introduced SGX OS APIs which replaced the functionality provided by the SGX Driver (the SGX ACPI device is suppressed as well). The SGX Runtime System detects the underlying OS and leverages the SGX Driver if pre-Win10 Update 2, or not."
"When a Windows OS is updated to WIn10 update 2, a reboot is required to restart to properly leverage the new OS API support."
"If this does not work and if Windows Update 2 was installed after SGX SDK, you want to remove the SGX SDK and restart the install."
Having a Skylake CPU does not guarantee have Intel SGX support on the CPU - Product Change Notification
"Intel is initiating new S-Spec and MM numbers for 6th Generation Intel® Core™ i7 & i5 desktop and the Intel® Xeon® E3-1200 v5 family processors for a minor manufacturing configuration change to allow customers to enable Intel® Software Guard Extensions (Intel® SGX) when using these processors."
"The stepping will not change for these processors; it remains R-0."
"The CPUID Processor Signature will not change for these processors; it remains 0x506E3.
A blackhat review of Intel SGX - SGX Secure Enclaves in Practice - Security and Crypto Review
This says that enclaves are even protected from System Management Mode (SMM aka ring -2) and Intel Management Engine (ME aka Ring -3)!
This also says that attested enclave programs are bound to a CPU
So much from Ken's session on Saturday..first from the session
mssql-cli had a new release in the last week!
This is the idea for running mssql-cli in the secure environment, no need to have SSMS GUI - can use just ssh from a jumpbox!
sqlpackage is coming on Linux - Ken demo'ed in a Docket container on Linux!
Extracting a dacpac using sqlpackage in a Docket container on Linux!
SQL Operations Studio - actually based on VS Code!
SQL Operations Studio is getting new pages to manage SQL Agent and a Profiler page is in the works too!
SQL Operations Studio upcoming SQL Agent page
SQL Operations Studio upcoming SQL Agent page - drilling into job and can run it!
The UI team for the upcoming SQL Agent pages are contactable (Ken said they cannot say there are done, they need to come out of "invory tower" and get feedback from users as UI is subjective!)
Please check out the UI Teams Video and provide feedback! - Seeking community feedback on SQL Agent UX prototype
FUTURE PLANS - NO COMMITMENT TO GUARANTEE FUTURE FEATURES/CHANGES
mssql-cli check out customizable smart commands
mssql-cli is written in a modern language (python) compare to sqlcmd which is contains old c++ code and therefore is harder to work on
With mssql-cli Microsoft developers do not have to worry about pointers and memory management!
When teh tools team were looking to do a cross platform modern cli tool they look around and found dbcli
dbcli already includes items like automatic table formatting hence adding an extension to dbcli for SQL Server was the way to go
Apparently the initial implementation of hooking the microsoft backend to dbcli took a handful of engineers only a short period of time (days/weeks) rather than months! Of couse it needed polishing before release!
mssql-cli now provides save queries and join suggestions, check them out.
Sqlpackage is being ported from .net full to .net standard so it can run on Linux!
The idea is to have dacfx platform independent to ease deployments on Linux!
Also the tools team are looking to port (SMO?) to Powershell 6 and in future to open source the modules!
SQL Operations Studio there are plans for 3rd parties/community to be able to build extensions
SSMS has no supported extension model - 165 extentions out there but no support - if SSMS breaks first thing support say is turn off extentison!
SQL Operations Studio from day 1 the plan was for extensability- built on Visual Studio language servcies which has a very small core of just javascript/typescript with the rest of the service being extensions!
In fact the core of SQL Operations Studio is not sql server specific - think about that for other Microsoft database products (Ken went quiet here - I am guessing Cosmos db, maybe!)
The idea is to have data service extension and insight extensions i.e. front/bad end extensions and 1 more in the middle I missed!
Did you know that Activity Monitor in SSMS in extensibile via an undocumented extensibility model?
Activity Monitor load assemblies and talk a hidden API with them!
The problem is to write these extensions requires C# coding skills
The idea with SQL Operations Studio is that people with just T-SQL knowledge can extend the dashboards with some templated json to describe the extension
The idea is that there will be an extension manager and a Github extensions repository is coming!
Soon you will be able to use VSPE to package up items into a VSIX extension - not released yet but soon!
Currently you can create a query, create a chart and click a button to create an insight which can be added to a dashboard
The plan is to allow UI Extensibility
An extension for ops studio to manage sql agent is coming (maybe preview March and GA April - DO NOT HOLD MICROSOFT TO THESE DATES!)
The idea is that the dashboard will become a "Command Centre"
Not in Vs code - typescript+c#
Dashboard tabs
Will be able to see a list of extensions - initially just trusted Microsoft extensions
In the furture there will be a marketplace with signed extensions but that is in the last phase of the current plan
SSMS 18.0 is nearly ready - uses VS 2017 shell
SSMS has to be made accessibility ready - team had to fix 300 accessibility issues across the various dialogs!
Team spent the last year building many test suites!
The SSMS setup is being rewritten - currently shares components with the SQL Server engine and Visual Studio and puts lots in the GAC
Can lead to hard to RCA issues when shared components version conflicts due to SQL Server engine+Visual Studio+SSMS upgraded and something breaks!
The new SSMS installer will use an installation directory(!) where all components sit
SSMS 17.6 will be next then as well as 18.0
Working on SMSS+.NET 7.2 compatability
SSDT 2015 - already no more feature updates
Database projects ship as part of Visual Studio now
The Analysis services VSIX come from the marketplace now (VS?)
The Integration Service VSIX is seperate due to different dependencies in fact the IS team do their own developement and just give Ken the package!
The plan for for SSMS updates to be integrated in WSUS and to NOT require a reboot
The Tools team are thinking about a cross platform SMO-Core similar to .NET Core/Standard i.e. to not port the whole thing to Linux in 1 go much like .NET Full still does not run on Linux
Talking with Ken after the session he was saying how he is much freer to talk about future plans now - the phrase "no more secrets" was used, wow!
The idea is not to get revenue from tools (otherwise just sweat value out of SSMS) - KPI for the Client Tools group is adoption which then clearly reduces the barriers to adopting SQL Server!
PS Slava Oks - SQL Server engineering manager and SQLOSv2 expert is also Ken's boss and contributes ideas to the Client Side Tools as well! Excellent integration between Client Tools/drivers guy and engine guy - no organizational barrier! Slava covers from SQLOSv2 Debugging to Client Tools, Slava does it all!
The entrance to SQLBits 2018
Friday - Connor Cunningham and Bob Ward
Meet the lady with pink hair - Jo Douglass!
Jenga!
Jenga tower at it's tallest, we know what is coming next..
Jenga crash, "someone has to lose"!
Also met Pinal Dave!
