On the 17th of August Cumulative Update 2 for Microsoft SQL Server 2014 SP1 ‚Äčhas been released‚Äč.
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
/****** Object:¬† DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]¬†¬†¬† Script Date: 08/11/2015 16:47:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login]
ON ALL SERVER
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%'
RAISERROR('This login is for application use only.',16,1)
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
ENABLE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
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.
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
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:
Prerequisites to connect an on premise network to Azure:
* A Microsoft Azure account with spending limit or a subscription
* A router that is supported by Microsoft to connect to Azure, in my situation I used the Cisco ASA 5505. A full list can be found here: https://azure.microsoft.com/en-us/documentation/articles/vpn-gateway-about-vpn-devices/
* A fixed IP address from your internet provide. A dynamic (DHCP) IP address wil work but when you get a new IP address from your provider the connection will be broken and some configuration changes must be made on the Azure site of the network. I will explain later what needs to be changed.
* To make a full Domain Network with on premise (virtual) machines and Azure virtual machines it would be nice to have a Domain¬† Controller on the on premise site.
* Java Runtime¬†6.39 to configure the Cisco ASA 5505 (if you are a die hard you can use the console but because my Surface Pro 3 and my work laptop do not have a serial port available I went for the dummy easy way with a GUI)
To configure your Cisco ASA device it turned out that you need to have Java Runtime 7.51 installed. Do not use higher then the ASDM software may not work. An old and not supported version anymore from the date of 15th of April 2015. After you installed Java and the ASDM software you probably need to change the first part of the target in the shortcut to “C:\Program Files\Java\jre6\Bin\javaw.exe”, do not use “C:\Program Files (x86)\Java\jre6\Bin\javaw.exe” because than you keep stuck in “Contacting the device”. But when you use the x64 version of javaw.exe you get the error: “Unable to launch Device Manager from 192.168.1.1”. If you keep using the original first part of the target in the shortcut at “C:\Windows\SysWOW64\javaw.exe” you also keep stuck in “Contacting the device”
On Premise network:¬†¬†¬†¬†¬†192.168.1.0/24
On Premise network gateway (Cisco ASA 5505 router):¬†192.168.1.1
My (old) public IP address from my internet provider: ¬†184.108.40.206
My On Premise network DNS Server name and IP address:¬†HYP01 – 192.168.1.31
First you need to create a Virtual Network in Azure:
Click on +, Network Services, Virtual Network, Custom Create
Image 1: Custom Create Virtual Network
In the Virtual Network Details screen fill in the name for your Virtual Network (any name that describes you virtual network will do) and choose the location for your Virtual Network (West Europe for me because I live in West Europe). You will see the name you entered appear in the Network Preview image and click on the right pointed arrow in the lower right corner of the screen.
Image 2: Create Virtual Network (Details)
In the ‘DNS Servers and VPN Connectivity’ screen you need to fill in the name and IP address of you local DNS Server and optional a second (or third) DNS Server (for example a public DNS Server if you need to get to the Internet from the Virtual Network. Next you need to select ‘Configure a site-to-site VPN’ under ‘Site-to-site Connectivity’ The ‘Network Preview’ should display the network on Azure and On Premise with the DNS Servers. Click on the right pointed arrow in the lower right corner of the screen.
Image 3: DNS Server and VPN Connectivity
In the ‘Site-to-Site Connectivity’ screen the name for the On Premise network, the public IP address from your internet provider and the address space of your On Premise network must be filled in. Click on the right pointed arrow in the lower right corner of the screen.
Image 4: Site to Site Connectivity
In the ‘Virtual Network Address Spaces’ screen the information for your virtual Azure network must be specified. In my case I am using a 10.10.0.0/22 address space with three subnets; 10.10.1.0/24, 10.10.2.0/24 and a gateway subnet 10.10.3.0/29. You can use only one subnet for your Virtual Machines but just to be able to test with different subnets I created two. The third, the gateway subnet, is mandatory to be able to route through the networks to you on premise network. Click on the ‘V’ in the lower right corner of the screen.
Image 5: Virtual Network Address Spaces
In the Azure website under ‘Networks’ and then the Network you just created you would see an almost finished network. There is still missing a public gateway. To create the gateway click on the bottom of the screen on ‘Create Gateway’ and choose a ‘Static Routing’. The Cisco ASA series do not support ‘Dynamic Routing’. Click on ‘Yes’ at the question if the gateway should be created for the virtual network. Creating a gateway may take some time. Just sit it out and behold! When you get the message ‘Succesfully created a gateway for virtual network Azure_Network’ you are ready to go.
Image 6: Completed Azure Network overview
Next step is to configure the Cisco ASA 5505. To do so you need to download the VPN Device script from the ‘azure_network’ page. Click on the link ‘Download VPN Device Script’ Because in this situation I am using this type of router I select in the screen ‘Download a VPN Device Configuration Script’ for ‘Cisco Systems, Inc.’ at Vendor, ‘ASA 5500 Series Adaptive Security Appliances’ at Platform and ‘ASA Software 8.3’ at Operating System and click on the ‘V’. Save the script in your downloads location.
Image 7: Download a VPN Device Configuration Script
Next you can configure the ASA with this script so the connection can be established.