Sql Server features affecting security

A number of security benchmarks (e.g. CIS v1.0.0, FedRAMP, ..) those days we are recommending to disable Microsoft Sql Server features such as remote access, contained database authentication, cross db ownership chaining, allow updates, .. unless we actually have a real requirement for those features.

The rationale is that disabling those features, we would shrink the surface attack area.

A first step we can take is to get a report of which features are actually enabled in our database systems. The following query will do the deed (per instance):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE TABLE #Database ( [Name] VARCHAR(255), [Feature] VARCHAR(255) ) EXEC sp_MSforeachdb N' BEGIN INSERT INTO #Database SELECT ''?'' AS [Name], NAME AS [Feature] FROM sys.configurations WHERE NAME IN ( ''allow updates'', ''cross db ownership chaining'', ''contained database authentication'', ''remote access'') AND Cast(value AS INT) = 1 END ' SELECT * FROM #Database DROP TABLE #Database

What if we find out that some of those features affecting security is actually enabled?
Here is a query which will reconfigure all the databases in a given instance, disabling remote access, one of those features:. 

1 2 3 4 5 6 7

EXEC sp_MSforeachdb N' BEGIN EXEC sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure ''remote access'', 0 RECONFIGURE EXEC sp_configure ''show advanced options'', 0 RECONFIGURE END '

As usual:

Caveat: generally, don't use the above or similar scripts in Production, as long as you don't understand and accept the consequences. 

Caveat: always read the message log.

Caveat: sp_MSforeachdb is undocumented, and AFAIK unsupported.

Caveat: the code above is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. in no event shall the author be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the code above.