- Physical and network security
- Attack surface, service accounts, and least privilege
- Authentication, authorization, and SQL injection
- Disaster recovery and auditing
- Ensure that the Windows server has proper network security configured.
- Decide which network protocols to allow, and disable any that are not required.
- Ensure there is a firewall set up (such as Windows Firewall) and configure it to allow access to SQL Server (as shown in Figure 1).
- Decide whether to encrypt connections to SQL Server and configure appropriately.
- If Kerberos will be used, register a Server Principal Name. Kerberos is an authentication mechanism that underpins Windows authentication (which I describe later in this article), but it is poorly understood. A clear and concise explanation was provided by Rob Greene, a Support Escalation Engineer, in the blog post "Kerberos for the Busy Admin." I recommend checking it out.
- Decide whether to use the SQL Server Browser Service to help clients find installed SQL Server instances, and decide whether you want to hide some instances. Hiding an instance means client applications and users will need to know the connection details of the SQL Server instance, but it prevents people from trawling the network to look for SQL Server instances.
-- To allow advanced options to be changed EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for -- advanced options RECONFIGURE; GO -- To disable xp_cmdshell EXEC sp_configure 'xp_cmdshell', 0; GO -- To update the currently configured value for this -- feature RECONFIGURE; GO
DECLARE @password VARCHAR (20); DECLARE @input VARCHAR (20); DECLARE @ExecStr VARCHAR (1000); SELECT @password = 'SecretSecret'; -- assume application gets input 'OR''=' SELECT @input = '''OR''''='''; SELECT @ExecStr = 'IF ''' + @password + ''' LIKE ''' + @input + ''' PRINT ''Password Accepted'''; EXEC (@ExecStr); GO
Msg 33111, Level 16, State 3, Line 1 Cannot find server certificate with thumbprint '0xFBFF1103AF133C22231AE2DD1D0CC6777366AAF1'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.