Category: Microsoft SQL Server

On the 12th of October 2015 Gartner has released the new “Magic Quadrant for Operational Database Management Systems”. Microsoft’s SQL Server is now the leader in the Magic Quadrant.

Quotes from the Gartner site:

  • Market vision: Microsoft’s market-leading vision consists of NoSQL (Azure Document DB and Azure Tables), cloud offerings (including hybrid cloud), the use of analytics in transactions (HTAP) and support for mobility. Its vision for in-memory computing across products, hybrid cloud implementations and a “cloud first” strategy is ahead of its competitors.
  • Strong execution: Microsoft SQL Server is an enterprise wide, mission-critical DBMS capable of competing with products from the other large DBMS vendors. Gartner’s 2014 market share data shows Microsoft as the No. 2 vendor in terms of total DBMS revenue.
  • Performance and support: Reference customers were very positive, with the performance of SQL Server, documentation, support, ease of installation, integration and operation all rated highly.

Of course there are some cautions too. The first is the perception that Microsoft SQL Server is not used in mission-critical enterprise wide applications.

  • Market image: Although SQL Server is an enterprise-class DBMS, Microsoft continues to struggle to dispel a perception of weakness in this area. Inquiries from Gartner clients demonstrate a continuing perception that SQL Server is not used for mission-critical enterprise wide applications — a view that inhibits wider use of SQL Server as a primary, enterprise-class DBMS.
  • Lack of an appliance: Microsoft still lacks an appliance for transactions (one comparable to its Microsoft Analytics Platform System, formerly Parallel Data Warehouse). By contrast, its major competitors (IBM, Oracle and SAP) all offer one, as does one new entrant to the Magic Quadrant (Fujitsu).
  • Pricing: Microsoft received below-average ratings for pricing suitability, a problem that stems from the pricing model changes implemented in SQL Server 2012. Microsoft’s cloud offerings appear to be partially mitigating this concern.

On the 17th of August Cumulative Update 2 for Microsoft SQL Server 2014 SP1 ​has been released​.

On the 17th of August Cumulative Update 9 for Microsoft SQL Server 2014 RTM has been released​.

SQL Server Support Information: http://support.microsoft.com/ph/2855

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.