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