14 Security

In this chapter, we will explore some of the common mistakes that are made around the security of SQL Server. We will start by looking at the principle of least privilege. This principle states that users should only have enough permission to perform their day-to-day duties, but it is often not adhered to. We will explore the reasons for this and why it is so important. We will also explore the sa account, which is SQL Server’s built-in administrator account. Many accidental DBAs leave this account enabled, and we will discuss why this is not a good idea and how we can rectify it.

We will then turn our attention to service accounts. We will explore common mistakes that are made when defining our service account strategy. We will also discuss how to use a modern approach to implementing service accounts and how this can make our environment more secure and easier to manage.

We will look at the controversial subject of xp_cmdshell, which is an extended stored procedure that allows us (and bad actors) to interact with the operating system from inside a SQL Server instance. We will debunk the myths that lead database administrators (DBAs) to open their enterprise up to unnecessary attack.

14.1 #94 Not implementing the principle of least privilege

14.2 #95 Not disabling the sa account

14.3 #96 Using the wrong granularity of a service account

14.4 #97 Enabling xp_cmdshell

14.5 #98 Failing to audit administrative activity

14.6 #99 Exposing the business to whole-value substitution attacks

14.6.1 Preparing an encrypted environment

14.6.2 Understanding and preventing whole-value substitution attacks

14.7 #100 Exposing the business to SQL injection attacks

Summary