Sql-server – Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities

compatibility-levelsql servert-sql

Can anyone help settle what I thought was a simple question.

A fellow DBA friend and I were talking about compatibility levels in general, and their relation to security specifically. He posited that a good reason to bring them as current as possible was to reduce security vulnerabilities caused by them. I countered that formerly SPs and currently CUs and hotfixes would surely patch those vulnerabilities, i.e. running a database in 2005 compatibility mode in a 2014 SQL Server instance surely does not revert that database back to 2005 SP4 security levels/vulnerabilities.

I've been searching for documentation to back this claim up but I've been unable to find any. Does anyone know of any authoritative documentation that would show me either correct or in error?

Best Answer

It does not introduce past vulnerabilities, and applies mostly to T-SQL syntax and T-SQL features. As the documentation states,

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.

Later on the documentation goes on to list the differences between each mode and you'll see no mention of security or hotfixes, just features/behaviors that will be differently implemented or unavailable.

Exploits would generally be at the server and DB engine level, so that isn't really a concern here. It would be a pretty huge security risk if I always kept my MSSQL instances patched but had one database in an older mode that was never able to take advantage of my diligent patching efforts. Although that would be a great way to get people onto the newest version!

You could argue that using old T-SQL code constrained by old version syntax might cause one to use work-arounds to inelegantly achieve certain requirements, which in turn might cause code to be more at risk for having vulnerabilities, but that onus is on the developer and nothing inherent to MSSQL. I can't even think of an example where this applies, but I'm sure they exist.

Related Solutions

Sql-server – Does the compatibility level for sql server work reliably for scripts

No. The compatibility level only affects some features that exists in both versions. To give an example from ALTER DATABASE Compatibility Level:

Compatibility-level setting of 90 or lower: MERGE is not enforced as a reserved keyword. Compatibility-level setting of 100: MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels.

As you can see, you can still write a script that uses MERGE, a post SQL Server 2008 only feature, and run it in 90 compatibility mode, and it will work. However, the script will break on SQL Server 2005. The comptability level 90 only affects the enforcement of the keyword (which affects parsing, in 100 mode a table named MERGE would have to be enquoted in brackets as [MERGE]), but not the execution.

There are many more features like this. So your only option is to run against the target SQL Server.

Sql-server – How to query the SQL Server Compatibility Level into a variable

You could query the system table sys.databases:

-- for SQL 2005+
declare @level tinyint;
select @level = [compatibility_level]
from sys.databases
where name = 'AdventureWorks'

Select @level
GO

-- for SQL 2000+
declare @level tinyint;
select @level = [cmptlevel]
from sysdatabases
where name = 'AdventureWorks'

Select @level
GO

Best Answer

Related Solutions

Sql-server – Does the compatibility level for sql server work reliably for scripts

Sql-server – How to query the SQL Server Compatibility Level into a variable

Related Question