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.

Simplify and Shrink

The following scripts sets the non system databases to simple recovery model, and shrinks the related log files:

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''Recovery'')=''FULL''
    and   DatabasePropertyEx(''?'', ''Status'')=''ONLINE''
    and ''?'' not in (''tempdb'') and ''?'' not in (''master'') and ''?'' not in (''model'') and ''?'' not in (''msdb'')
begin
  exec ('' print char(13) + char(10) + ''''Set recovery model to simple for '''' + ''''?'''';
 alter database [?] set recovery simple with NO_WAIT;'')
end'

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''Status'')=''ONLINE''
    and ''?'' not in (''tempdb'') and ''?'' not in (''master'') and ''?'' not in (''model'') and ''?'' not in (''msdb'')
begin
 exec ('' use [?];
 declare @logFile varchar(128);
 select @logFile= mf.name from sys.master_files mf inner join sys.databases db on mf.database_id = db.database_id where type=1 and db.name = ''''?'''';
 print char(13) + char(10) + ''''Shrink ''''+ @logFile + '''' log file of ?'''';
 dbcc shrinkfile (@logFile , 0)
 '')
end'

Caveat: generally, don't use the above or similar scripts in Production, as long as you don't understand and accept the consequences. The Simple recovery model is usually fine in Test or Development environments, but again, it may not be appropriate in many scenarios.

Caveat: always read the message log.

Caveat: the second script assumption is that there is no more than 1 log file per database. If there are multiple log files per database, it would be more sensible to look at a solution which doesn't use sp_MSforeachdb.

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.

A T-SQL query to get the TCP ports used by the current sessions

The other day I was having some difficulty getting to a Sql Server in a different subnet, so I thought the issue could be in some firewall/gateway/proxy/other-chap-in-the-middle.

As part of my investigation, I quickly wrote this bit of T-SQL, which should retrieve, with other useful stuff, also the TCP ports used by the various sessions connected to a Sql Server:

SELECT
  connections.session_id as [Session Id],
  connections.net_transport as [Net transport protocol],
  connections.local_net_address + ':' + cast (connections.local_tcp_port as varchar) as [Server net address and port],
  connections.client_net_address + ':' + cast (connections.client_tcp_port as varchar) as [Client net address and port],
  sessions.login_name as [Login name],
  sessions.host_name as [Host name],
  sessions.program_name as [Application name]
FROM sys.dm_exec_connections AS connections
INNER JOIN sys.dm_exec_sessions AS sessions
  ON connections.session_id = sessions.session_id
ORDER BY
  connections.net_transport,
  connections.local_net_address,
  connections.local_tcp_port

It may be useful to know if a non standard port is being used (the standard port is 1433), as firewalls or other stuff in the middle may not like those.

This is i.e. the result of such a query on a Dev Sql Server:

Session Id,Net transport protocol,Server net address and port,Client net address and port,Login name,Host name,Application name
52    Shared memory    NULL    NULL    NT AUTHORITY\SYSTEM    CERES    Report Server
54    Shared memory    NULL    NULL    NT AUTHORITY\SYSTEM    CERES    SQLAgent - Generic Refresher
57    Shared memory    NULL    NULL    NT AUTHORITY\SYSTEM    CERES    Report Server
51    TCP    10.25.81.63:1433    10.25.81.0:2628    sa    APOLLO    Microsoft SQL Server Management Studio - Query
55    TCP    10.25.81.63:1433    10.25.81.64:3384    sa    MITRA    Microsoft SQL Server Management Studio - Query
56    TCP    10.25.81.63:1433    10.25.81.0:2844    sa    APOLLO    Microsoft SQL Server Management Studio
53    TCP    10.25.81.63:1433    10.25.81.64:3363    sa    MITRA    Microsoft SQL Server Management Studio