SQL Server Licensing: CORE vs CALs. Do not waste your money.

Just recently on July 8th Microsoft stopped Mainstream Support for SQL Server 2008 and 2008R2.
Finally organizations are moving in direction of SQL Server 2012 and even 2014.

During this migration, Microsoft clients who used SQL Server Enterprise edition with CAL licensing model, face not easy dilemma: Switch to CORE licensing model or keep the old one based on CALs.

The difficulty of that decision is not only in predicting the future, but also in the fact that this decision is supposed to be final.

Will start from the very beginning.
In SQL Server 2008 licensing model you could buy server licenses on enterprise edition per CORE or based on User/Device CALs (Client Access License).
In SQL Server 2012 there is no option to have CAL licensing model for Enterprise edition. Also, CORE licenses sold in bundles: At first you have to by "4 cores" license and then you can add by two.

For some businesses that would be an additional spending.
Here is possible scenario:
- Company has server with two cores and want to install Enterprise edition of SQL Server for very limited number of users.
With SQL Server 2008 they could purchase one server license with 25 CALs and that would cost them about $13K.
With SQL Server 2012 they have to purchase Enterprise license for four Cores, which doubles their expenses.

However not everything is so bad. If you previously had SQL Server 2008 Enterprise edition + CALs and have Software Assurance (SA) from Microsoft you can upgrade you Enterprise server to version 2012 or 2014 and still use your CALs as in old licensing model.

There is a catch (#1): You will not be able to purchase additional CALs.
Would say company is growing in size, even just a little bit, if they have not enough CAL licenses they would have to purchase brand new SQL Server Enterprise.

Again, not everything is so bad. Companies with Microsoft Software Assurance, while they are on SQL Server 2008 Enterprise, can purchase CALs in advance and use them with the newer version!!!

And there is another catch (#2): CAL based licensing model supports only up to 20 cores. That means if you have only one user and 24 cores server you'd have to pay more than $160K forSQL Server Enterprise CORE licenses.

Read stories in internet how one company purchased 10000 CALs (over a million dollars spent) and then realized that their brand new 64 cores server can't be used.

So, for those who right now facing the decision to switch to CORE model or to buy more CALs I'd recommend to look at diagram I come up with*:

- Scale at the bottom represents number of cores. Diagram shows only 20 cores, if you have more cores and want to use them, or have more than 600 devices/users - forget about this article, you have to use CORE licensing model.

- Scale at the left is approximate amount of money you will spend for Enterprise edition licensed by COREs and Red line represents that number.

- Scale at the right represents number of CALs that would be equivalent in price with Enterprise model based on COREs. Green line represents that number.

Generally speaking, if you below green line in your CAL usage you better stay with CALs, if you are abowe, than it is more beneficial to go with CORE licensing model.

Situation You have 8 cores, which costs about $45K. That is an equivalent of about 220 CAL licenses.

If you have only 100 users/devices you can safely buy another 120 CALs to get the same price as with CORE and be able to expand your server for the same price.
If you already have 220 or more users, then most probably it would be better to go with CORE model.


Staying with CALs in that situation put you at risk of not having enough CALs in the future to accommodate all your new users.

Also, you have to always put into the account that you already spent a lot of money for existing CALs and Server licenses which you might to throw away....

Let stop for a second.
In the worst case scenario your CAL licenses won't be usable anymore....
You have an opportunity to build another 20 core server for BI purposes which will serve limited number of people in your company.


Also, SQL Server still has Server + CAL licensing model available for its Standard edition.
Here is diagram to compare CORE vs CAL licensing models:

 As you can see, with the same CAL pricing and cheaper Standard edition having Server+CAL licensing model is about three times less profitable than for Enterprise edition, but that is a good way to use your old CAL licenses.
Moreover, you can install several SQL Servers with Server+CAL model and all your devices/users covered by CALs will be able to access them.

Never waste your money!

* Diagram is not perfect it is based on approximate prices, which I've could find in the internet:
- SQL Server CAL license - $171.

- SQL Server Enterprise edition Server license - $7,026;
- SQL Server Enterprise edition one core license -$5,622 (you need at least four of them);
- SQL Server Standard edition Server license - $734;
- SQL Server Standard edition one core license -$1,466 (you need at least four of them);

Additional links (I've uploaded these documents to my Google Drive in case they disappear from MS site):
SQL Server 2008 - Licensing Quick Reference Guide
SQL Server 2012 - Licensing Guide
SQL Server 2012 -  Pricing Scenarios, Virtualization and License Mobility
SQL Server 2014 - Licensing DatasheetSQL Server 2014 - Licensing Guide
SQL Server Multiplexing - CAL requirements

Catch SQL Server security breach

Just recently, by overlooking one of the servers, I've found one very weird account.

It was used by an application to access a database in which that  account did not have any rights.

That was weird and after an investigation I've found that account in the database and the one used by the application are completely different, even though they were the same.

Here I will show you how that could happened:

DO NOT RUN FOLLOWING SCRIPTS IN PRODUCTION!!!

1. That script creates new 'TestDB' (you get an error if already have one with the same name) and creates a 'TestLogin' user with no password in that database.

USE [MASTER]; GO CREATE DATABASE [TestDB]; GO CREATE LOGIN [TestLogin] WITH PASSWORD=N'', DEFAULT_DATABASE=[TempDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO USE [TestDB]; GO CREATE USER [TestLogin] FOR LOGIN [TestLogin];

2. Here we restrict that user from reading and writing within the database.

EXEC sp_addrolemember N'db_denydatareader', N'TestLogin'; GO EXEC sp_addrolemember N'db_denydatawriter', N'TestLogin'; GO

3. (You can skip that script) In anther tab login to your SQL Server under newly created "TestLogin" account and try following script to get an error:

USE [TestDB]; GO SELECT * FROM sys.objects GO

Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'tables', database 'mssqlsystemresource', schema 'sys'.

4. Now in the main delete that account from the server. (you  have to close "TestLogin" tab before).

GO USE [MASTER]; GO DROP LOGIN [TestLogin]; GO

5. Note: We do not delete that account from the database!!!
Login is still sitting in the database.

SELECT * FROM [TestDB].sys.database_principals WHERE name = 'TestLogin' GO

6. Will create the same account again. This time will not assign it to the database, but will give some extra permissions:

CREATE LOGIN [TestLogin] WITH PASSWORD=N'', DEFAULT_DATABASE=[TempDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO GRANT CONTROL SERVER TO [TestLogin] GO

7. Go to another tab and login to your SQL Server under newly created "TestLogin" account, then try following script. You won't have an error!!!

USE [TestDB]; GO SELECT * FROM sys.objects GO

8. Run following script to verify that your "TestLogin" is still restricted user in "TestDB" database:

SELECT mp.name, rp.name FROM [TestDB].sys.database_principals AS rp INNER JOIN [TestDB].sys.database_role_members AS rm     ON rp.principal_id = rm.role_principal_id INNER JOIN [TestDB].sys.database_principals AS mp     on rm.member_principal_id = mp.principal_id WHERE mp.name = 'TestLogin' GO

You've got a situation when in "TestDB" you have restricted user "TestLogin" which is not allowed to do anything, but it does!

----------------------------------------------------------------------------

9. Here is the script to find out if you possible have similar situation with other accounts:

That script is safe to run in production.

SET NOCOUNT ON GO DECLARE @Database_Name NVARCHAR(128); DECLARE @UnassignedUsers TABLE(    Database_Name NVARCHAR(128), [Database User] NVARCHAR(128), [Server User] NVARCHAR(128) ); DECLARE @SQL NVARCHAR(1000); DECLARE database_list CURSOR FOR SELECT name FROM sys.databases; OPEN database_list; FETCH NEXT FROM database_list INTO @Database_Name; WHILE (@@fetch_status <> -1) BEGIN   SET @SQL = N'SELECT ''' + @Database_Name + ''' COLLATE DATABASE_DEFAULT as Database_Name, '     + ' d.name COLLATE DATABASE_DEFAULT as [Database User], '     + ' s.name COLLATE DATABASE_DEFAULT as [Server User] '     + ' FROM [' + @Database_Name + '].sys.database_principals AS d '     + ' INNER JOIN [master].sys.server_principals AS s '     + '     ON d.name COLLATE DATABASE_DEFAULT = s.name COLLATE DATABASE_DEFAULT and d.sid != s.sid '     + ' WHERE d.name != ''public'' ';   INSERT INTO @UnassignedUsers(Database_Name, [Database User], [Server User]) EXEC(@SQL);   FETCH NEXT FROM database_list INTO @Database_Name; END CLOSE database_list; DEALLOCATE database_list; If EXISTS( SELECT TOP 1 1 FROM @UnassignedUsers)     SELECT *, 'Account collision Detected' as Info FROM @UnassignedUsers; Else     PRINT 'No Account collisions on the server'; GO SET NOCOUNT OFF GO

10. Now clean the mess on your SQL Server (otherwise you have account with full server control and empty password)

USE [MASTER]; GO DROP LOGIN [TestLogin]; GO DROP DATABASE [TestDB]; GO

That kind of situation on the server appeared after restoring a database and not fixing orphaned accounts.

There is a easy fix for that:

EXEC sp_change_users_login 'Auto_Fix', 'useraccount'

People who did not know that solution and created new super-user did not have anything in mind, but imagine the whole it opened in SQL Server security.


These investigations inspired me to create another script to report all server users and their rights on the server. Run it and verify that you do not have too many administrators in your system:

That script is also safe to run in production.

SELECT IsNull(l.name,pr.Name) as [Login/User Name]     , pr.type_desc as [Login/User Type]     , pr.default_database_name     , IsNull(SUBSTRING((         SELECT ', ' + p.name  FROM master.sys.server_role_members as rm         INNER JOIN master.sys.server_principals as p             ON rm.role_principal_id = p.principal_id and p.type = 'R'         WHERE rm.member_principal_id = pr.principal_id         FOR XML PATH('')     ),3,8000),'NO ROLE ASSIGNED') as [Assigned Roles]     , SUBSTRING((         SELECT ', ' + permission_name + '(' + ip.name + ')'         FROM master.sys.server_permissions as sp         INNER JOIN master.sys.server_principals as ip ON ip.principal_id = sp.grantor_principal_id         WHERE sp.grantee_principal_id = pr.principal_id         FOR XML PATH('')     ),3,8000) as [Special Permissions]     , IsNull(CAST(pr.is_disabled as varchar),'N/A') AS is_disabled     , IsNull(CAST(l.is_policy_checked as varchar),'N/A') AS is_policy_checked     , IsNull(CAST(l.is_expiration_checked as varchar),'N/A') AS is_expiration_checked     , CASE         WHEN PWDCOMPARE(l.name,l.password_hash) = 1 THEN 'Login With Password Same AS Name'         WHEN PWDCOMPARE('',l.password_hash) = 1 THEN 'Login With Empty Password'         ELSE 'No Problem Found'     END as [Password Problem] FROM master.sys.sql_logins as l RIGHT JOIN master.sys.server_principals as pr ON l.principal_id = pr.principal_id WHERE pr.type in ('S','U','G')

In our case it would return following record:




Login, without password and not assigned to any role has control over the server!!!

Hope you do not have accounts like this on any of your servers.