Category: Microsoft SQL Server

Today I got the question if it is possible to create a login trigger that only is triggered on SQL Server login’s (except SA of course)​ that are used from SQL Server Management Studio to connect to a SQL Server instance.

So the first thing you do in cases like this is to check if someone else has solved this already for you. I found the following post​ that had only part of the solution.​

​So I changed the code so that it does only block login attempts from SQL Server Management Studio with a SQL Server account. Domain accounts and domain groups keep working as requested and also SA keeps working.

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'TR_Block_Logon_Via_Management_Studio_SQL_Login')
DROP TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
GO

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login]
ON ALL SERVER
FOR LOGON
AS

BEGIN

DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE login_name <> 'sa'
AND nt_user_name = ''

IF @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
GO

There is however a way to bypass this solution so it is not completely fool proof. It is meant as a first blocking.

SQL Server licensing is a beast of its own. There are quite a few options to choose from and it is difficult to determine the right choice for your organization. As a good habit as a consultant the answer is always: “It depends…”

If you end up having questions on the licensing always ask your license reseller for help. Every license reseller should have a license specialist who has been trained by Microsoft on licensing.

This information is from the Microsoft SQL Server 2014 Licensing Datasheet (Licensing Datasheet / original link) with a little bit less marketing blabla.

Editions Overview

First an editions overview on what editions of SQL Server 2014 are available:

  • Enterprise Edition for mission critical applications and large scale data warehousing
  • Business Intelligence Edition for premium corporate and self-service BI
  • Standard Edition for basic database, reporting and analytics capabilities

Enterprise Edition includes all product features available in SQL Server 2014, and the Business Intelligence (BI) Edition includes premium BI features in addition to Standard Edition database features.

Note: SQL Server 2014 is also available in Developer and Express editions. Web Edition is offered in the Services Provider License Agreement (SPLA) program only.

SQL Server 2014 Licensing Models

SQL Server 2014 offers customers a variety of licensing options aligned with how customers typically purchase specific workloads. There are two main licensing models that apply to SQL Server:

Server + CAL:

Provides the option to license users and/or devices, with low cost access to incremental SQL Server deployments.

  • Each server running SQL Server software requires a server license.
  • Each user and/or device accessing a licensed SQL Server requires a SQL Server CAL that is the same version or newer – for example, to access a SQL Server 2012 Standard Edition server, a user would need a SQL Server 2012 or 2014 CAL.
  • Each SQL Server CAL allows access to multiple licensed SQL Servers, including Business Intelligence Edition, Standard Edition and legacy Enterprise Edition Servers

Per Core:

Gives customers a more precise measure of computing power and a more consistent licensing metric, regardless of whether solutions are deployed on physical servers on-premises, or in virtual or cloud.

  • Core based licensing is appropriate when customers are unable to count users/devices, have Internet/Extranet workloads or systems that integrate with external facing workloads.
  • To license a physical server, customers must license all the cores in the server. Determining the number of licenses needed is done by multiplying the total number of physical cores by the appropriate core factor found in the core factor table.  A minimum of 4 core licenses is required for each physical processor on the server.

SQL Server 2014 Editions availability by licensing model:

Note: SQL Server 2014 Developer Edition is licensed under the Developer Tools model, which is ‘Per User’ based.

Special Note for Enterprise Edition Users:

With the introduction of SQL Server 2012, Enterprise Edition was removed from the Server + CAL model and new server licenses are no longer available.  However, customers with active Software Assurance (SA) coverage can continue to renew SA on Enterprise Edition servers and upgrade to SQL Server 2014 software.  Note: for customers who upgrade to SQL Server 2014, a 20 core limit applies to the software.

Licensing for Virtualization

SQL Server 2014 offers virtualization rights, options and benefits to provide flexibility for customers deploying in virtual environments. There are two primary virtualization licensing options in SQL Server 2014: the ability to license individual virtual machines and the ability to license for maximum virtualization in highly virtualized and private cloud environments.

Individual Virtual Machines

As hardware capabilities grow, it continues to be more common for each database to use a fraction of its server’s computing power. When deploying databases on Virtual Machines (VMs) that use just a fraction of a physical server, savings can be achieved by licensing individual VMs.

  • To license a VM with core licenses, purchase a core license for each virtual core (virtual thread) allocated to the VM (with a minimum of 4 core licenses per VM).
  • To license a single VM with a server license (for Business Intelligence or Standard Edition only), purchase a server license and matching SQL Server CALs for each user or device.
  • Each licensed VM covered with SA can be moved frequently within a server farm, or to a third-party hoster or cloud services provider, without the need to purchase additional SQL Server licenses.

Note: When licensing VMs under the Server + CAL model, the number of virtual cores does not affect the number of server licenses required.

High Density Virtualization

Further savings can be achieved by operating a SQL Server private cloud. This is a great option for customers who want to take advantage of the full computing power of their physical servers and have very dynamic provisioning and de-provisioning of virtual resources.

  • Customers can deploy an unlimited number of VM’s on the server and utilize the full capacity of the licensed hardware, by fully licensing the server (or server farm) with Enterprise Edition core licenses and SA coverage based on the total number of physical cores on the servers.
  • SA enables the ability to run an unlimited number of virtual machines to handle dynamic workloads and fully utilize the hardware’s computing power.

Licensing for High Availability

SQL Server software can be configured so that if one server fails, its processing will be picked up, recovered and continued by another server.  Beginning with SQL Server 2014, each active server licensed with SA coverage allows the installation of a single passive server used for fail-over support.

  • The passive secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work” such as additional backups from secondary servers, then it must be licensed for SQL Server.

  • The active server license (s) must be covered with SA, and allow for one passive secondary SQL Server, with up to the same amount of compute as the licensed active server, only.

Business Intelligence Server Access

Similar to other SQL Server products offered under the Server + CAL licensing model, Business Intelligence (BI) Edition generally requires a SQL Server CAL for each user or device accessing the server software.  New with SQL Server 2014, use terms for BI Edition server software now allow batch processing of data without requiring CALs for those data sources supplying the data.

  • ‘Batch Processing’ is defined as an activity that allows a group of tasks occurring at different times to be processed together at the same time.
  • Non-batch processing access to BI Edition servers still requires CALs be assigned to those users and/or devices accessing the server software.
  • The general multiplexing policy still applies to SQL Server Standard and Enterprise Edition software licensed under the Server + CAL model.

©2014 Microsoft Corporation.  All rights reserved.  This document is for informational purposes only.  Microsoft makes no warranties, express or implied, in this summary.  For more information on how to buy SQL Server 2014, please visit http://www.microsoft.com/en-us/servercloud/products/sql-server/ .

Other links related to licensing SQL Server:

Core Factor Table

Introduction to Per Core Licensing and Basic Definitions

SQL Server 2012 Licensing Quick Reference Guide

SQL Server 2012 Licensing Datasheet

Microsoft Assessment and Planning (MAP) Toolkit link, to help with planning a SQL Server architecture and particularly useful for licensing calculations

During my first installation of Microsoft SQL Server 2016 CTP2 on a VM I created for this I got an error message stating that Java Runtime was not installed… (during an full install – selected all features)

Error message: “Oracle JRE 7 Update 51 (64bit) or higher is required”

And when you click on “Failed”:

Yes, of course, I do not want any dependencies on Java Runtime on my SQL Server boxes. So the Java Runtime is not installed on my default machines. The bigger question is of course:

“Why has SQL Server all of a sudden the requirement of Java Runtime since SQL Server 2016 CTP2?”

It turns out that a new feature of SQL Server 2016 CTP2 is PolyBase. “PolyBase is a T-SQL front end that allows customers to query data stored in HDFS”. With PolyBase you can query, using T-SQL, Hadoop or Azure Blob Storage and query it in an adhoc fashion. It also lets you query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for data warehousing workloads and intended for analytical query scenarios.

So the new feature is a great addition in accessing data like Hadoop and Azure Blob Storage together with SQL Server.

So if you need PolyBase then you must install Java Runtime too. I really hope that the Java Runtime dependency will be rewritten to a .Net variant. Or I need to get used to install Java Runtime together with SQL Server. Of course it is better to install the PolyBase feature on a standalone VM in a larger environment.

Azure Backup is a service from the Azure environment that can be used to back up on premise machines, on premise virtual machines and cloud virtual machines. My first test was if an IAAS VM from Azure could be backed up by Azure Backup. The Azure Backup service is not advertised to do the last option but a customer is planning on moving the Hyper-V environment partly to Azure and there also needed to be a backup in place for the environment. Of course you can use Data Protection Manager or other systems but the drawback is that you need an extra VM for DPM and it is not really with the cloud in mind if there is a service that is able to back up your environment. So I started to test Azure Backup with my home/test environment. No extra charges when you have a Visual Studio Ultimate subscription with MSDN. It has a 115 EURO free of charge limit for testing purposes.

Below is the complete process of configuring the Azure Backup solution. For cost reasons I have used a Self-Signed Certificate instead of a certificate that needs to be bought. So if you would like to try this out yourself the only thing you need is an Azure account with some spending room. There are test Azure offers where you are limited in what you can use. If you would like more spending room you can always get your credit card and test some further… It is all up to you.

In short the following steps are taken:

  1. Create a Self-Signed Certificate
  2. Create the Backup Vault and upload the certificate to Azure
  3. Export the certificate from MMC
  4. Import the certificate on the VM
  5. Run WBInstaller.exe on the Virtual Machine
  6. Register Server
  7. Schedule backup

 

The following steps need to be done once:

 

Create a Self-Signed Certificate

Start Visual Studio Command Prompt

%comspec% /k “”C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC\vcvarsall.bat”” x86_amd64

Use the following statement to create a Self-Signed Certificate

makecert.exe -r -pe -n CN=AzureBackup -ss my -sr localmachine -eku 1.3.6.1.5.5.7.3.2  -e 12/12/2040  -len 2048 C:\Temp\AzureBackup.cer

 

Create the Backup Vault and upload the certificate to Azure

  1. Create new Backup Vault in Azure by clicking + and choose Data Services, Recovery Services, Backup Vault and Quick Create
  2. Enter a name for the Backup Vault and choose a region near you (Western Europe in my case) and click on Create Vault
  3. After the message is displayed that the Vault is created select the Vault
  4. Click on the Manage Certificate icon in the screen and browse to the CER file you created and click (V)
  5. After this is completed succesfully click on Download Agent (WBInstaller.exe)

 

Export the certificate from MMC

Start MMC and add Snap-In Security on the machine where the Certificate is created

  1. Right click on the AzureBackup certificate in Certificates\Personal\Certificates and choose All Tasks and Export
  2. Click Next in the Welcome to the Certificate Export Wizard screen
  3. Select Yes, export the private key in the Export Private Key screen (if this screen does not show, delete the AzureBackup certificate and start all over) and click Next
  4. Select Personal Information Exchange (PKCS #12 (.PFX) and Include all certificates in the certification path if possible in the Export File Format screen and click Next
  5. Select Password and enter a password twice and click Next
  6. Browse to the file location where you would like to save the exported file and name the file in the File tot Export screen and click Next
  7. Check the choices in the Completing the Certificate Export Wizard screen and click Finish if all is ok
  8. On the Certificate Import Wizard message: “The Export was successful” click OK

 

The following steps need to be done on every Virtual Machine that needs to be backed up by Azure Backup

 

Import the certificate on the VM

  1. Copy the PKF certificate file and the WBInstaller.exe to the virtual machine or create a network share accessible from the Virtual Machine where you place the files.
  2.  Import the certificate (AzureBackupExport.PFX) in the Security MMC console on the Virtual Machine that needs to be backed up
  3. Start MMC and add Snap-In Security on the Virtual Machine
  4. Right click on Certificates(Local Computer)\Personal and choose All Tasks and Import
  5. Select Local Machine in the Welcome to the Certificate Import Wizard and click Next
  6. Browse to the file location where you saved the exported file and select the file type Personal Information Exchange (PFX) in the File tot Import screen and click Next
  7. Enter the Password for the PFX file and select Include all extended properties in the Private key protection screen and click Next
  8. Select Place all certificates in the following store [Personal] in the Certificat Store screen and click Next
  9. Check the settings in the Completing the Certificate Import Wizard screen and click Finish
  10. On the Certificate Import Wizard message: “The Import was successful” click OK

 

Run WBInstaller.exe on the Virtual Machine

  1. Click I accept the terms of the Supplemental Notice in the Supplemental Notice screen and click OK
  2. In the screen Prerequisites Check click on Next
  3. Change the Installation folder and the Cache Location at will or leave it default and click on Next
  4. Select Use Microsoft Update when I check for updates (recommended) or I do not want to use Microsoft Update and click Install
  5. Wait untill the WBInstaller completes and leave Check for newer updates in the Installation screen and click Finish
  6. In the Before You Begin screen click Next
  7. In the screen Upgrade Process click Finish
  8. Check for Updates and install these

 

Register Server

  1. When finished installing updates start Windows Azure Backup (shortcut on the Desktop)
  2. Click on Register Server in the top right of the application
  3. Click on Next in the Proxy Configuration screen
  4. Click on Browse and select the correct certificate in the Vault Identification screen and click Next
  5. Select the Backup Vault in the Vault Identification screen and click Next
  6. Click on Generate Passfrphase or create one your own and Enter a location to save the passphrase in the Encryption Setting screen and click Register
  7. Click Close in the Server Registration screen

 

Schedule backup

  1. Click on Schedule Backup in the top right of the application
  2. Click Next in the Getting Started screen
  3. Click on Add Items and select what you would like to backup in the Select Items to Backup screen
  4. Click on Exclusion Settings an select what files you do not want to backup
  5. Click Next in the Select Items to Backup Screen
  6. Select the days and times the backup needs to run in the Specify Backup Time screen and click on Next
  7. Select the number of days retention time in the Specify Retention Setting screen and click Next
  8. On the Confirmation screen click Finish
  9. Click Close in the Modify Backup Progress screen

 

Backup Now

  1. If needed click on Backup Now in the top right corner of the application
  2. In the Confirmation screen click Back Up
  3. You may close the Backup progress screen if needed. The backup will continue

 

Good luck! If you have any questions please let me know! Next blogpost will be on restoring your data.