SQL Server Security

Introduction of SQL Server Security

A defence-in-depth strategy, with overlapping layers of security, is the best way to counter security threats. SQL Server provides a security architecture that is designed to allow database administrators and developers to create secure database applications and counter threats. Each version of SQL Server has improved on previous versions of SQL Server with the introduction of new features and functionality. However, security does not ship in the box. Each application is unique in its security requirements. Developers need to understand which combination of features and functionality are most appropriate to counter known threats, and to anticipate threats that may arise in the future.

Security Features in SQL Server

Server role based security

Two types of Server role based security

·         

   1. Fixed Server Roles: Fixed role is available in server by default, we can say   these  are system’s server roles

   2. User Define Server roles : Here user can create individual/group server roles   to  assign to user login.

·       Sysadmin:can perform any activity, and has complete control over all database functions.

·       ServerAdmin : can change server configuration parameters and shut down the server.

·    setupadmin : can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption.

·     Securityadmin :can create and manage server logins and auditing, and read the error logs.

·         Processadmin :can manage the processes running in SQL Server.

·         Dbcreator :Can create, alter, and resize databases.

·         Diskadmin:Can manage disk files.

·         Bulkadmin: can run the BULK INSERT statement

Encrypted Backup based security

This is the only option for protecting sensitive data when it is transported on tape or disk for off-site storage. Luckily, like other mainstream commercial database systems, SQL Server 2014 provides two reliable approaches for encrypting database backups. By using these two SQL Server 2014 encryption features you can reduce the likelihood of unauthorized disclosure of confidential customer and business information, even if the SQL Server database backups have been stolen due to weak network security. The importance of encrypted database backups

BACKUP DATABASE AdventureWorks
TO DISK='C:\XYZ.BAK'
WITH MEDIAPASSWORD='Password'

Secure the database backup folder by removing unwanted users

A database administrator should make sure that access to the database backup folder is restricted and permission should only be granted to users who really need it. Unauthorised access can result in the database backup folder being vulnerable to backup files being copied to remote servers. Unauthorised access can also result in accidental deletion of critical backup files, thereby breaking the database restore sequence. Hence, it is very critical as a database administrator to make sure only the right people have access to the database backup folder.

Always Encrypted feature (SQL Server 2016)

With the Always Encrypted feature enabled, your SQL Server data will always be encrypted within SQL Server. Access to encrypted data will only be available to the applications calling SQL Server. Always Encrypted has enabled client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server. By doing this you can keep those nosey Database or Windows Administrators from poking around sensitive client application data In-Flight or At-Rest. This feature will now allow you to sleep at night knowing your confidential data stored in a cloud managed database is always encrypted and out of the eyes of your cloud provider.

 Row Level Security (SQL Server 2016)

With Row Level Security the SQL database engine will be able to restrict access to row data, based on a SQL Server login. Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filter predicates get executed for every SELECT or DELETE operation. Implementing row level security at the database layer means application developers will no longer need to maintain code to restrict data from some logins, while allowing other logins to access all the data. With this new feature, when someone queries a tables that contains row level security they will not even know whether or not any rows of data were filtered out.

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Encrypting a UDF :To encrypt a user-defined function, simply add WITH ENCRYPTION to the CREATE FUNCTION statement (after the RETURNS element). Throughout this tip, I will be building an encrypted UDF (and decrypting it) to demonstrate the principle.

Decrypting a UDF: Firstly, open a Dedicated Administrator Connection (DAC) to SQL Server. Using SQL Server Management Studio, this is easily done by prefixing admin: to the connection string upon connection of a query window. Note that the DAC can only be used if you are logged onto the server and using a client on that server, and if you hold the sysadmin role. You can also get to it by using SQLCMD with the -A option. Note: DAC won't work unless you're using TCP/IP; you'll get this rather cryptic error (in both SQLCMD and SSMS):

 

Encryption Symmetric vs.  Decryption Asymmetric Keys

 

What is Asymmetric or Symmetric?

So why then is there a recommendation to use symmetric keys to encrypt data? Quite simply, performance. Symmetric key algorithms tend to be mathematically simpler, and as a result, faster.  The difference in speed can be significant even into the 100x faster range. Therefore, symmetric key algorithms are the way to go when encrypting data.

We can see this difference quite clearly with a simple example. We can set up two tables and create two keys. One set will be for an asymmetric key algorithm. The other will be for a symmetric key algorithm. And we can run through a number of rows of data and determine how much time it takes between the two algorithms. What we should see is that the symmetric key encryption is performed a noticeable amount faster.

Figure 1 shows a visual example of encrypting data using a symmetric key algorithm. Figure 2 shows an example of decrypting the data. Note that there is only one key in both cases.

Figure 1:

Figure 2:

Ownership and User-Schema Separation in SQL Server

A core concept of SQL Server security is that owners of the objects have irrevocable permissions to administer them. You cannot remove privileges from an object owner, and you cannot drop users from a database if they own objects in it.

·         User-Schema Separation

User-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.The four-part naming syntax for referring to objects specifies the schema name.Server.Database.DatabaseSchema.DatabaseObject

 

·         Schema Owners and Permissions

Schemas can be owned by any database principal, and a single principal can own multiple schemas. You can apply security rules to a schema, which are inherited by all objects in the schema. Once you set up access permissions for a schema, those permissions are automatically applied as new objects are added to the schema. Users can be assigned a default schema, and multiple database users can share the same schema.

By default, when developers create objects in a schema, the objects are owned by the security principal that owns the schema, not the developer. Object ownership can be transferred with ALTER AUTHORIZATION Transact-SQL statement. A schema can also contain objects that are owned by different users and have more granular permissions than those assigned to the schema, although this is not recommended because it adds complexity to managing permissions. Objects can be moved between schemas, and schema ownership can be transferred between principals. Database users can be dropped without affecting schemas.