Best Practice to Secure the SQL Server

Best Practice to Secure the SQL Server(s)

  • Start with the physical server room where the Servers are kept
  • Insure that only selected IT staff has access (Network Admins, System Admins, and SQL DBAs)
  • Keep the backups not only on site, but keep a copy of backups off site
  • Install all service packs and critical fixes for Windows Operating System and SQL Server Service Packs
  • (Test on test server first before applying to production)
  • Disable the default admin the BUILTIN\Administrators group from the SQL Server
  • Use Windows Authentication mode as opposed to mixed Mode
  • Use service accounts for applications and create a different service account for each SQL Service
  • Create service accounts with the least privileges
  • Document all user permission with the script and keep track of which user has what permissions
  • Disable all features via the SQL Server Configuration Manager that are not in use
  • Install only required components when installing SQL Server (don’t install ssrs, ssis, ssas)
  • Disable the SA account and rename it
  • Remove the BUILTIN\Administrators Windows Group from a SQL
  • Use SQL Server Roles to limit logins accessing database (as shown in previous videos)
  • Use permission to give only that is needed to the SQL Login or User
  • Hide all databases from logins
  • Be proactive in securing the SQL Server and databases
--Drop the BUILTIN\administrators

EXEC master..xp_logininfo 
@acctname = 'Builtin\Administrators',
@option = 'members' 

USE MASTER
IF EXISTS (SELECT * FROM sys.server_principals
WHERE name = N'BUILTIN\Administrators')
DROP LOGIN [BUILTIN\Administrators]
GO

--Verfiy the Builtin\Administrators group has been dropped

EXEC master..xp_logininfo 
  @acctname = 'Builtin\Administrators',
  @option = 'members'     


--AddBuiltin\Administrators 

EXEC sp_grantlogin 'BUILTIN\Administrators'
EXEC sp_addsrvrolemember 'BUILTIN\Administrators','sysadmin'


EXEC master..xp_logininfo 
  @acctname = 'Builtin\Administrators',
  @option = 'members' 


  --Enable Windows Authentication (requires a restart of services)

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', 
REG_DWORD, 1
GO

--Enable Mixed Mode Authentication (requires a restart of services)

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', REG_DWORD, 2
GO


--Get infor on all SQL Logins and Windows users

SELECT name AS Login_Name,TYPE, type_desc AS Account_Type
FROM sys.server_principals 
WHERE TYPE IN ('U', 'S', 'G')
ORDER BY name, type_desc


--make sure you have a login that is part of the sysadmin role before doing this!!!!

USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'Password#12345'
GO
ALTER LOGIN [sa] DISABLE
GO


USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'Password#12345'
GO
ALTER LOGIN [sa] ENABLE
GO

--Hide all databases

    
USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

--unhide all databases

USE MASTER
GO
GRANT VIEW ANY DATABASE TO PUBLIC;
GO