Microsoft SQL server

INTRODUCTION

The recent evolution when it comes to network security has been moving away from guarding the perimeter where the network is located and moving to protect the data at the source. The primary reason behind this move is because perimeters security is not functioning in the modern world of technology as mentioned by (Larson, English & Purington, 2016). Currently, more than just employees require accessibility to available data. In essence, a customer together with business partners may require access to available data which implies that a database is not only required to be hidden behind a firewall (Mistry & Seenarine, 2012). Certainly, as more database are now becoming more vulnerable and exposed to outside world through the internet, it becomes imperative that these are appropriately secured from attackers form the internet and other locations of the world. This act of securing databases does not only comprises establishment a strong policy, rather instituting adequate access controls (Otey, 2002).

Microsoft SQL server

Microsoft SQL server is usually a relational database management system which has a primary goal of enhancing the Structured Query Language when it comes to an enterprise environment. The system connected to SQL server is used in performing various functions such as updating and retrieval of information stored in the database (Mistry & Seenarine, 2012). Before installation of SQL server, is advisable to have a good understanding of the king of hardware and software required. Some of this things to consider before installing the MS SQL server is, the network available and make sure protocols are able to be enabled, the memory available of machines that will host the server and also speed of the machine is important, which is vital when it comes to performance of the server (Mistry & Seenarine, 2012). There are various editions of MS SQL server, but when it comes to enterprise edition; it’s a suitable for business.

A defense-in-depth approach, availability of various overlapping layers of security; it has always been a better practice of countering security threats. In this paper we are going to address the security mechanism of Microsoft SQL server (Mistry & Seenarine, 2012). SQL offers a security architecture which has been proper design in order to permits database administrators, including developers to have an opportunity of creating a protected database applications thus countering dangers. Each given version of SQL server has always come with improvements compared to previous versions of the same server, which comes with added features and functionality (Larson, English & Purington, 2016). Though, when we talk about security it doesn’t come with a box. Each application has always been unique to each other when it comes to security requirements. Therefore, it’s a good practice for developers to have a good understanding on which arrangement of topographies and functioning are most applicable in countering well recognized threats, and have knowledge of anticipating those threats that might rise in the coming future (Larson, English & Purington, 2016).

The SQL server instance comprises a ranked grouping of objects, beginning with the server. There are various databases contained in each server, and in this database they comprise a collection of securable objects (Petkovic, 2008). Each QSL server securable has been given various associative permissions which can be approved to a principal, and this principle is simply a user, a group or processes which have been approved access to SQL server. The availability of security structure on SQL server has enable managing accessibility to securable entities by authentication together with authorization only (Larson, English & Purington, 2016).

Authentication can be defined as the method of getting access to the SQL server through which a principle entreaty accessibility through submission of identifications that the server assesses. When using authentication, there is establishment of the individuality of the handler or practice that has been requested for authentication (Petkovic, 2008). On the other hand, authorization can be referred to a process of responsible which securable properties a standard has privilege of accessing it, and which kind of operations are permitted for those requested resources. In this paper we will cover SQL server security (Petkovic, 2008).

Security models

Security model is utilized in establishment of the external principles used in analysis of security issues, also this offers context for considering database, such as operations and implementations. There are various DBMSs that have their own security models considered to be highly important when it comes to system designs and operations. For instance in MS SQL Server we have Sea View model and CRL model when you observed, it is realized that security model are used in explaining all the structures that are in existence in DBMSs which is usually used in development and operating the real security systems. They exemplify notions, implementing policies, and providing server for such operations. When any faults occur in a security model, it will either translate rather into insecure operations or clumsy system.

CLR security model

Is a model of SQL server integration with the .Net framework Common Language Runtime. This integration manages and protects accessibility between different types of CLR and non-CLR objects running within the SQL server which can be called by a Transact-SQL statement or another CLR running object in the server. These calls are defined by different types of links

CLR performs specific goals to effect security of databases under it (Brett, 2019). These include; ensures by default here is no integrity and stability compromises of SQL server by running managed user code on the SQL server and also hinders performing operations that could have the potential of compromising the robustness of SQL server, the other goal is to manage unauthorized user codes accessibility to user data in database and this ensures user-defined code is under appropriate security context with the correct privileges endowed, and finally, it restricts user code from accessing resources outside the server thus ensuring it is controlled for local data access and computation, and locking of  user-defined codes from gaining  unauthorized access to system  resources in the running of SQL server resources.

CLR Integration Code Access Security

Is a CLR support code access security model for managed code, which identifies the code before granting permission to assemblies (Hayes, 2003). It supports a security policy that defines the kind of permissions to be granted, which are defined in three places that include; machine policy which oversee all managed code running in the machine that has been installed with SQL server, user policy, which supports the process’s managed code specific to the windows account in the SQL server, and host policy, this is set up by the SQL server to effect running of the managed code.

CLR support access code security mechanism is based on fully trusted and partially trusted code. The protected resources are wrapped by managed application programming interfaces which require corresponding before accessing the resources (Bob, 2012).

SQL Server Host Policy Level Permission Sets

Permission set specified when creating the assembly determines the set of code access security permissions granted to the assemblies by the SQL server. Garcia, (2009), asserts that there are three permission sets, that is, SAFE, EXTERNAL-ACCESS, and UNSAFE, which are specified via PERMISSION SET option of CREATE ASSEMBLY (Transact-SQL). The SQL server host-level policy is a combination of SQL fixed policy for system assemblies and user-specified policy for user assemblies, which are grated full trusts by the fixed policy for CLR assemblies and SQL server system (Larson, 2015).

SAFE

The most restrictive permission set that allows only internal computation and local data access. It maintains that any code executed by an assembly with SAFE permissions is unable to access external system resources such as files, the network, environment variables, or the registry. SAFE assemblies entail two permissions and their corresponding values which include; Security Permission that allows execution of managed code, and Client Permission that allows true and correct context connection and blocks all values with blank passwords (Larson, 2015).

EXTERNAL ACCESS

They work as SAFE assemblies but also have an additional ability to access external system resources like files, network, environmental variables, and the registry. They have various permissions and values including: Distributed Transaction Permission, DNS Permission, Environment Permission, EventLog Permission, and FilelOPermission among others.

UNSAFE

UNSAFE allows assemblies unrestricted access to resources both within and outside SQL server. Codes executed from UNSAFE are able to call managed code and these assemblies are given Full Trust (Brett, 2019).

To sum it up, SAFE is recommendable for assemblies that perform computation and data management tasks without access to the outside of an SQL server, and EXTERNAL-ACCESS for those that access resources outside the SQL server.

Compliance

Best practices, operations, and administrative tasks are essential for creation of a more secure SQL Server (Bob, 2012). They include;

Surface area reduction

Microsoft SQL Server minimizes the surface of attack by defaulting optional features. It allows the administrator to choose what to install; for instance; a database engine, an Analysis service engine and Reporting services like Native and, or SharePoint. Besides, it enables the administrator to review only the product features needed, like in a database engine can choose to install its specific features like SQL Server Replication, Full text and Sematic Extractions for search, and Data Quality Services.

Policy-Based Management

Alongside configuring the surface area, it detects out of compliance conditions. These SQL Server security best practice policies include; Asymmetric Key Encryption Algorithm, and Exec rights, SQL Server login mode, Symmetric Key Encryption for user Databases, and SQL Server Password Policy (Garcia, 2019). This security practice allows the exportation and importation of policies using XML files

Authentication in SQL server

There are two methods supported by SQL server this includes, windows authentication mode and secondly, mixed authentication mode. Whereas on windows authentication comes with the default settings, on other words, it stalks about as incorporated security due to the fact that this SQL server security model has always tightly been integrated with windows (Larson, Hanson & Zwilling, 2015). There is a specific number of individuals who have been trusted to have the ability of logging into SQL server. Those users who have at present been authenticated on the system, they don’t require to come up with additional credentials for accessibility. On the other hand, mixed modes usually supports both of SQL server and windows (Atle, 2019). The user names together with respective passwords are always maintained within SQL server. Between the two, it is suggested to apply windows authentication as it usages a series of encrypted messages in order to authenticate a user in SQL server (Atle, 2019). When authentication credentials have been used on SQL server, this information is always approved across the network, thus making them lesser secure. When using windows you do not require to log into the server again as you have already logged to your machine (Larson, Hanson &Zwilling, 2015).

Access control

Then main aim of having access control on a database is that it has always been clear. When addressing access control it’s much expensive in terms of analysis, designs together with operational costs. This is used in order to realize situations, to have an understanding on standards and finally to achieve well know purpose. Its recommended not apply access control when you have little knowhow of other necessary information. Access control is required to specify an appropriate control to the situation. Access control manages logins and roles in order to restrict data access to required users and in the process prevents unauthorized users from obtaining sensitive information (Hayes, 2003). It includes;

Dynamic Data Masking (DDM)

DDM conceals sensitive data by hiding all, or some of the value with a data mask. This denies non-privileged users access by showing the unmasked version of data rather than the actual values, while the legal users have access as the underlying data for a masked column remains unchanged. This access control applies to users with sensitive information such as Social Security numbers or financial account numbers (Yong, 2019). DDM access control provides several built-in data mask functions like; default, custom ring, and random.

Just Enough Administration

Under this security control system administrators are given minimum access permission unless there is need for elevated permissions. Authorized users acquire elevated permissions to perform continuous tasks that require elevation temporarily under heavy monitoring and control, this helps greatly reduce the risk of internal system attack from rouge administrators or other malicious users.

Privileged Access Management

Control based on Microsoft Identity Manager (MIM), and builds on concept of just-in-time administration. It operates by use of second trusted bastion Active Directory (AD) forest. Bastion forest grants access with a time limit upon request which is approved depending on configured MIM policies. It entails monitoring and logging of the entire process which enables alerts, auditing, and reports of privileged access. This follows a cycle of process, which is Prepare, Protect, Operate, and Monitor in a sequence.

Active Directory Authentication

Involves protection of using security features. Active Directory improves support for the minimal privileges and reduces risk against insider attacks.

Windows Firewall

It prevents unauthorized machine access by limiting the activity on communication ports; TCP and UDP, to a selected set of pre-approved ports and, or applications. Windows Firewall prevents network access to a Database Engine by defaulting it.

Separation roles

This concept of SQL Server security mechanism serves to protect data from being accessed without authorization by former users having unrestricted access to resources and data. It provides minimum access needed for a given task to perform its function (Yong, 2019). This applies to situations when a database requires access to database engines and has the ability to manage database access permissions and schema changes, but requires no access to sensitive operational data in operational databases. It advocates that users with domain or OS administrative privileges have access to the database schema or data.

Stored procedures and views

They make convenient performance of complicated tasks from a sensitive perspective allowing developers review tasks to make sure they are carried out in the correct way. They control access to the underlying tables and hoc querying using stored procedures and views. They also limit users’ ability to order specific actions for execution. The stored procedures identify specific parameters and return scalar values or data sets while the views are read-only pre-defined queries which are optimized for performance for client experience (Florence, 1996).

Impersonation

An access control done through the EXECUTE AS clause, which allows for selection of a specific context which is authorized. It operates in circumstances where certain operations are out of bounds of accepted use for a particular group of users. Impersonation can be applied in any T-SQL execution statement, providing programmatic or as-needed permissions to perform a task under no permission.

Parameterized queries

Uses parameterized queries to protect the query from injection attacks. These queries are strongly typed and abstracted from the SQL statement, packed with the SqlCommand Object. They operate to protect queries at an application level.

Authorization in SQL server

Authorization is associated with the act of granting permission to users who have been authorized to carry out particular transactions, thus changing the state of the database also known as (written item transactions), also this authorized user are able to retrieve data from the database known as (read item transactions). The outcome of authorization, that requires basing on transactions, is a vector:  authorizations (item, auth-id, operation). A vector can be defines as a sequence of data values of which location is well known inside the system, the way this is put into usage in all to DBMSs functionality. When still at the logical level, it is required that the system structure to be authorized on the server, this requires cooperation with auditing server.  Currently there is an issue that result from server to server security and when there is an issue it implies that the security issue becomes bigger than expected as more DBMSs sever are associated in the transactions. In most cases auditing requirement are implemented in a poor manner. In order to be safe, you are required to log all access and log all authorization details with transaction identifiers. When addressing security of database, there is need for frequently audit the system and maintenance an audit trail, in most cases for a long time.

Access Philosophies and Management

Discretionary control is defined as assigning particular privilege basing on certain assets, which are responsible in authorizing users who are allowed to interact with the system in a certain way. The security DBMSs is required to develop an access matrix such as object like relationships, archives, opinions and processes for each user. This implies that each entry splitting create, read inserting and updating privilege. The matric thus turns to be intricate as authorization is varying from one object to the other. The matrix additionally can become bigger, thus its employment regularly needs the kind of physical employment related with spare matrixes. However, it’s more impossible in storing the matrices in the PC main memory.

Required control is authorization by phases or job. A common obligatory plan is the four-level government cataloguing of open, mystery, generally mystery and top mystery. The connected idea is to relate security controls not to people yet to jobs – so the compensation assistant has benefits due to the activity job and not as a result of individual components. The database suggestion is that every datum thing is doled out a cataloguing for read, make, refresh and erase, with a comparable order joined to each approved client. A calculation will enable access to objects based on not exactly or equivalent to the doled out degree of freedom – so a client with leeway level 3 to peruse things will likewise approach things of level 0, 1 and 2. On a basic level, an a lot less complex plan. The Bell-LaPadula model (2005) defines an obligatory plan which is generally cited.

A subject (regardless of whether client, record or program) is prohibited to peruse an item (connection, tuple or see) except if the security classification of the subject is more noteworthy or equivalent to that of the article.

A subject is illegal to compose an article except if the security characterization of the subject is not exactly or equivalent to that of the item.

Note that a significant level of leeway to peruse suggests a low degree of freedom to compose – generally data flows from high to low levels. Its considered exceptionally secure frameworks, not allowed. Required security plans are generally straightforward and, in this manner, moderately simple to oversee and review. Optional security is difficult to control and accordingly missteps and oversights are anything but difficult to make and difficult to recognize. You can make an interpretation of this difficulty into costs. There are maybe two other key standards in security. One is revelation, which is frequently just on a need-to-know premise. This fits in better with optional.

Security than obligatory, as it suggests neither any earlier existing level nor the requirement for explicit task. The other guideline is to separate obligations. The DBA liable for security the board is him/herself a security chance. The board moves toward that include one individual or a gathering of individuals that have associations in their work speak to a comparative hazard. This accentuates the significance of security inspecting and the significance of related strategy plan.

Security levels of SQL Server

SQL Server supports four types of security levels, Windows level security, SQL Server level security, Database level security, and Schema level security.

Windows Level security

This layer has two levels of principals, windows local login and windows group login. Account creation is via GUI, where a user creates an account at windows level and log in into user account and enters into SQL server. It also contains windows group login, accounts are also created through using GUI. An account is created at windows level, two or more accounts are created and two or more users are added, then login to group user to access SQL server.

SQL Server level security

At this security level of security has a SQL server log in principal which is created by default when an instance is installed.  The account is created with a login name and password, and login into SQL server. It is created via GUI and CUI.

This level has fixed server roles which enhance convenience and backward compatibility and delegates more permission whenever possible and these permissions are unchangeable (Bob, 2012).  SQL Server provides nine fixed server roles.  These fixed roles include: sysadmin; members of this server role are allowed to perform any activity in the server, bulkadmnin; users of this role can run the BULK INSERT statement, dbcreator; it allows its members to create, alter, drop, and restore any database, diskamin; is used to manage disk files, serveradmin; allows users to change server-wide configurations and shut down the server, securityadmin, users manage logins and properties. They can GRANT, DENY, and REVOKE server-level permissions. Otey, (2002), asserts that they can also GRANT, DENY, and REVOKE database-level permissions incase of access to a database, and can reset passwords for SQL server logins, processadmin; allows its users to kill the server processes by enabling them end processes that are running in an instance of SQL server, setupadmin; members are allowed to add and remove linked servers by using Transact-SQL statements, and public; grants permission to users who have been denied specific permissions on a securable object.

Database Level Security

SQL Server provides security principals that group other principals to help users easily manage the permissions in their databases. These are fixed database roles that are predefined in the database and flexible database roles that are created. Fixed database roles include: db-owner; this role performs all configuration and maintenance activities on the database, and can also drop the database, db-securityadmin; it modifies membership and manages permissions, db–accessadmin; adds or remove access to the database for windows logins, windows groups, and SQL Server logins, dbbackupoperator; which backs up the database, db-ddladmin; allows users to run any Data Definition Language (DDL) command in a database, db-datawriter; this role adds , delete, or change data in all user tables, db-datareader; a fixed database role that can read all data from all user tables, db-denydatawriter; a fixed role where users cannot add, modify, or delete any data in the user table within a database, and db-denydatareader; a fixed role that cannot read any data in the user tables within a database (Wagner, 2008).

Security basics of MS SQL server

There are two authentication options supported by Microsoft SQL Server supports

Windows authentication, this option depends on Active Directory (AD) in order to authenticate users before being granted access to SQL, this is the recommended authentication mode since when it comes to management of passwords policies together with users AD is preferred, in addition to group access organizations based applications.

SQL Server validation operates through keeping username and passwords on the database server. In a situation where Active Directory is not available, this option can be used. The two options can be used at the similar time (mixed mode), though, if probable, windows validation is recommended to be used exclusively. When you are only allowed to use SQL server authentication, you are required to ensure that the default sa account has strong password updated regularly or it has been disabled; this is usually due to the report being targeted by hackers. Accounts-related to SQL Server such as sa (when enabled) can easily be accomplished using SQL server management studio, or another alternative is ALTER LOGIN Transact-SQL (TSQL) command as referenced here (Larson, Hanson & Zwilling, 2015).

Server Logins and Roles

Irrespective of which authentication option has usernames, there exist only two types of logins that can only be configured on SQL instance: this is the user login and server login. I am going to elaborate on user logins in the following part. Server logins allow user found a connection to a SQL server instance. Every single server logins are allocated one or extra server roles that will enable it to accomplish definite functions on the instance. Server logins are usually allocated a public server role, and this role often offers basic access to the instance. Other tasks can be attributed to server logins such as bulkadmin, sercurityadmin, dbcreator and serveradmin.

Two methods can be used in creating server logins, T-SQL, or the SQL server management studio. A default database is usually specified when creating server logins. When in the default database the server logins are associated with user logins. This is important to note that the name of severing login together with its allied on user logins is not mandatory for them to match. When there is no associated user objective when it comes to the default database, there will be a denial accessing the database unless the role of server login assigned to it can access the entire database. It becomes possible to map server logins to any user in various given databases, and it’s potential for creation of user when setting up server logins as explained by this author (McCray, 2009).

Database Users, Schema and Roles

During the creation of a user log in, it’s mandatory to stipulate the database that it will be connected with; a username is the first step, then next step is to use default schema which will also be used to all object a user has created and concerned only if no schema has been specified. Schemas from SQL server are a collection of object, similar to table and views, logically being separated from object on another database, making it easy for access management and implies that it is not required usage of schema name when running  T-SQL then running in contradiction of database

Securable and Permissions

When roles assigned to users from server logins or the database are giving variable access, this can be overridden through assigning one or more securable instead. This securable is present at the server, schema and database levels. This is considered the resources of the server together with user logins. For instance, by application of securable, you will be able to grant a server login accessibility to a definite table or function solitary, this level of granularity is not probable through passing on a role to a login. In order to access MS SQL server securable, permission are always granted. Consent can be granted only to view data in the database or permission that you will be able to modify data can be given. The statement used in performing these tasks includes GRANT, DENY and REVOKE T-SQL, though on many occasion permission becomes complicated. For instance, when you configure the SQL statement such as DENY to a securable this will prevent permission inheritance at a low level object. However, the column-level GRANT configuration on the SQL server always overrides DENY at the object level, therefore when a DENY permission is used to a table is ever replace by GRANT permission when it comes to a column-level. Due to complexity of some permission, it’s a good practice to check the effectiveness of permission through use of T-SQL (Larson, Hanson & Zwilling, 2015).

Data Encryption

MS SQL Server encryption options:

Secure Sockets Layer (SSL)

SSL is used to encrypt traffic that’s traveling between client application and the server instance; this is much just like the internet traffic which is usually secured at the server and the browser. In addition the client has the ability to validate the identity of the server by application of server certificate. Just like websites the secured traffic moving between the server and the browser, SQL server can also be given configurations to use SSL in encryption of traffic as they travel from server instance to client application. Besides, the client can validate the identity of the server by use of server certificate. Secure Sockets Layer is only used in protecting data as it moves across the network, though, unlike most of the other forms of SQL Server encryption, Secure Sockets Layer is always available in all supported versions of SQL server (Larson, Hanson & Zwilling, 2015).

There is a requirement of installing a certificate to the SQL server before the Secure Sockets Layer can be enabled. The better way of getting this Secure Sockets Layer certificate is through sending a request to your enterprise certification authority. MS SQL server can always be configured as a CA, and you are required to set up client in order to gain trust the certificate which is the issue. Another way of getting Secure Sockets Layer certificate is through using self-signed certificate, but this is the best stated when you are in a test environment (Meier, 2002).

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a form of encryption used to encrypt data available on the disk. To be precise, TDE is used for encrypting all the data including log files. When it comes to client applications there is no need to be changed when TDE has been enabled. TDE when in SQL Server it’s used in protecting data that is at rest through encrypting the database data together with log file available on the disk. TDE always functions independently to existing client applications. Thus there is no need to be changed when Transparent Data Encryption is active. Transparent Data Encryption applies real-time encryption when still at page level, it happens that pages are usually encrypted first before encryption is done at disk level, this does not increase the size of the data together with log files. Transparent Data Encryption is always available when it comes to enterprise editions of SQL server (Larson, Harson & Zwilling, 2015).

Transparent Data Encryption (TDE) can be represented through the use of hierarchical structure, as windows data protection API being on the top of the hierarchy and is used in encrypting the service master key. The credential also can be encrypted using SMK, also linked password and database master key sitting on various databases. The SQL server database master keys are considered to be symmetric keys used in protecting the private keys of certificates whereas the asymmetric keys are usually stored in the database (Larson, Hanson & Zwilling, 2015).

MS SQL Server can be used as I generation of self-signed certificates which can be used with Transparent Data Encryption, or you can send a request to CA which is the most used method. When you consider enabling Transparent Data Encryption, the certificate is supposed to be backed up together with the private key associated with that certificate. There will be a need for restoring or attaching the database to a different SQL server. When the Transparent Data Encryption is enabled on the SQL server database, there is also encryption of the tempdb database. When Transparent Data Encryption has been disabled, the private key, together with the certificate should be kept because parts of the transaction log can still be encrypted until a full backup is done (Anley, 2002).

Transparent Data Encryption, on the other hand, needed a database encryption key that will either be a symmetric key that is usually protected by a certificate and placed in the master database. Also, this can be asymmetric key that is protected by a serviceable key that is using Extensible Key Management. Transparent Data Encryption backup files on an active environment are encrypted using DEK; thus, at restoration point, there must be availability of the certificate protecting the DEK.

When it comes to symmetric keys, you are supposed to use a similar password for encryption and decryption of the data. Whereas asymmetric applies only one password for encrypting data, which is public key, and a different password is used when decrypting data, which is known as the private key. CREATE CERTIFICATE command can be used in creating certificates, while the database encryption can be created by use of this command Transact-SQL, CREATE SYMMETRIC KEY and CREATE ASYMMETRIC KEY (Kothari, 2006).

Backup Encryption

Encryption backup is way same as TDE though SQL backups are always encrypted rather than of the active data and log files. Encryption backup can be found on MS SQL server 2014 and later. You are at a will to postulate AES 128, AES 192, AES 256 or Triple DES encryption. Also you can decide to use asymmetric key storage or either use a certificate (Mistry &Seenarine, 2012). In addition, there is a great possibility that TDE can be enabled together with backup encryption together concurrently, though, you are required to apply a different certificate or keys.

When backup encryption is enabled, just like TDE the certificate or the key is supposed to be backup. When you don’t have the certificate or the key, the file use for backing up cannot be used in restoring the files. Backup encryption can also be used when using MS SQL Server Managed Backup. Thus it’s more important to note that if at any instance you are using a certificate for backing up of data, when restoring the same encrypted data you are required to have the original certificate or key. This implies that the certificate being used to restore data must have the same thumbprint as when the backup was done. When you are renewing the certificate, it may cause the thumbprint to change.

Column/Cell-Level Encryption

Column or cell encryption has been used to ensure that definite data has been encrypted in the database and remains intact when kept in the memory. When exhausting, this encryption data is always decrypted using a function and needs swapping to client application too. This feature is in almost every version of SQL server, and this kind of encryption can be applied on columns with sensitive information. The data is always encrypted on the disk and it will remain on this state in the memory up to when decryption key will be used to decrypt that data.  Thus, though the SQL data is always encrypted, in most cases it is not secure elsewhere simply using a function in the user context to decrypt it. Besides, a function is required for decryption of the data thus client application has to be adjusted for it to work well with cell level encryption (Mistry &Seenarine, 2012).

Encryption Key Management

This encryption requires creation of a master key before you can get the permission to use cell-level encryption. Various ways are applied to encrypt data using cell-level encryption. They include; by use of a passphrase key for encrypting and decrypting data, which requires encryption of the stored outline together with functions. Or else the passphrase key can easily be accessible on metadata (Mistry & Seenarine, 2012). In addition, asymmetric key offers a strong security layer though can have an effect on function ability. Also, symmetric key in most instances are strong thus provision of a virtuous balance among security and enactment. As a final point, certificates are also used in provision of a virtuous balance among security and performance, thus they are likely to be associated with a database user.

Row-Level Security

Row-Level Security (RLS) permits a company to have full control on which user have the ability to see rows in the database. For instance, the administrator can decide to put a restriction on user seeing only rows that has information regarding their clients. Row-Level Security comprises of three major parts; security policy, predicate function and security predicate. The predicate function is used in checking whether the user has executed a database query and is able to access row basing on logic. For example, you will be able to check if the username of the client who is executing the query equals area in one of the rows columns. On the other hand, a predicate function together with security predicate are always defined altogether in a function for silently filtering the outcomes of a given query errors or blocking with an error if rows being accessed has been denied. In conclusion, security policies are used in binding the functions to a table (Larson, English &Purington, 2016).

Always Encrypted

Has been used to protect data in motion and at rest, it also adds security at several protection levels. ADO-NET is used to encrypt additional sensitive data before it is sent across the network. Storage of encrypted data for inserts and update is allowed without decryption (Larson, 2015).  This type of data encryption assigns decryption only to authorized clients with the correct encryption key, and this ensures that sensitive data is obfuscated to any user other than the intended clients.

Always Encrypted (AE) offers protection in three perspectives (Bob, 2012):  from an application angle which renders sensitive data useless if the correct encryption key is not applied under a compromised data environment, network layer perspective where AE encrypts sensitive data at the client before it is transmitted over the network and consequently decrypting it at the client after data is received. This gives encrypted communications additional security and allows the master encryption key to secure sensitive data even under insecure information transfer. The third perspective is data protection layer that enables protection of data at all stages. It involves treatment and storage of sensitive data in a binary data format the server without accessing the column encryption key.

Bit locker Driver Encryption

This type of security involves encryption of drive volumes in order to protect them from being accessed physically. A Trusted Platform Module (TPM) of the host machine or Hardware Security Module (HSM) connected to host machine contains the encryption key used to protect data under Bit locker (Grobauer, 2011). This guarantees no accessibility of data on the encrypted volume when a drive is connected to another machine, and in case of wrong encryption key, the data is completely locked.

Connection Encrypting

Total layer security provides secure communications by creating private connections using symmetric cryptography to encrypt data permissions. Protection of data involves endpoint authentication with a third party certificate store, shared secret negotiation, and message integrity checks.

Proactive monitoring

Data network protection focuses on securing communication between the database and clients. Proactive monitoring entails intense logging of failed authentication attempts for use in access auditing, as well as raising alerts on anomalous activity which may indicate a security threat.

Auditing

An SQL audit comprises of several elements which are combined into a single package for specified servers on database actions. The combination of these components produces an audit as the output. SQL Server Audit object monitors a collection of single instance server and database-level actions, and groups of actions. After defining an audit, its destination is located and the actions are applied manually as its state is always disabled. This activates its destination to receive data (Wagner, 2008).

Server audits alert administrators of any attempt to breach database security, enabling them to provide solutions with the latest in secure data communications. They also allow tracking and logging of events occurring at a database, or SQL Server level, with an inbuilt audit tracking suspicious connection attempts. These enable administrators to create custom audits for various events to help them track and avert these malicious activities within a database (Florence, 1996).

Using SQL server permits developers to define events to write custom information in an audit log when tracking potentially malicious actions by authenticated users (Grobaeur, 2011). Such actions could be extremely large transactions, unusual frequent transfers in a short time for commerce or banking scenarios or triggers on timing of results such as a series of transactions occurring outside of standard business hours.

Server Audit Specification

This is an object belonging to an audit. Each audit creates one server specification, both created at the SQL Server instance scope.  This specification collects several server-level action groups raised by the Extended Events feature. Audit action groups occurring at the database engine are then defined and recorded in the target by the audit.

Database Audit Specification

It collects the database-level audit actions raised by Extended Events feature.  Audit action groups or audit events are each added to this audit specification, and then sent to the audit that records them in the target which could be a file, the Windows Security event log, or the Windows Application event log. Otey, (2002) suggests that these logs require periodical review and archive to create enough space in the target in order to write additional records. However, the Application log is less secure compared to the Windows Application log as it requires lower permissions unlike the latter where only authenticated users can access it. Writing to the Windows Security log requires addition of SQL server account to the Generate security audits policy which is configured by a security snap-in (secpol.msc) (Anley, 2002).

Permissions

SQL server audit features and command have specific guarantees. Server principals need the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission in order to create, alter, or drop a server audit or serer audit specification (Grobauer, 2011). Besides the database principals require the ALTER ANY DATABASE AUDIT PERMISSION or, the ALTER or CONTROL permission on a database in order to create, alter, or drop a database audit specification. Moreover, for principals to connect to the database, or ALTER ANY SERVER AUDIT or CONTROL SERVER they need such guarantees (Kothari, 2006). However, principals in the sysadmin role can tamper with any audit component and those in the db–owner role can interfere with audit specifications in a database.

Conclusion

Conclusively, network security is an important aspect in the recent past especially at the data source. Secure mechanisms like SQL server should be enforced to the Microsoft world of internet in order to serve all network users with confidence and trust of their data being secured and privatized to only permitted sources. Moreover, there is still room to come up with new inventories to suffice the security architecture of SQL server in the Microsoft world.

References

Brett, M. D, Samuel, M. (2019). Cloud-based services for electronic civil registration and statistics systems. Journal of health Population and nutrition.

Yong, F. S. H, Rumpu, W. (2019). A state of analysis of PHP Vulnerabilities based on token and deep learning technology. PLoS ONE Vol 4.

Atle, F. S. (2019). Efficient storage of heterogenous geospatial data in spatial databases. Jounal of Big Data

Hayes, M. (2003). Quest for Quality-Improving Software Quality a high priority at many companies Retrieved from

https://search.proquest.com/compscijour/docview/910803028/3CAB99369C8D4461PQ/20?accountid=14375

Florence, O. (1996). Software guards model integrity. Government Computer News Retrieved from,https://search.proquest.com/compscijour/docview/1038681376/3CAB99369C8D4461PQ/18?accountid=14375

Larson, B., English, D., & Purington, P. (2016). Microsoft SQL Server 2016 Reporting Services. McGraw-Hill Education.

Larson, P. A., Hanson, E. N., & Zwilling, M. (2015, April). Evolving the architecture of SQL Server for modern hardware trends. In Data Engineering (ICDE), 2015 IEEE 31st International Conference on (pp. 1239-1245). IEEE.

Mistry, R., & Seenarine, S. (2012). Microsoft SQL Server 2012 Management and Administration. Sams Publishing.

Otey, M. (2002). SQL Server 2012 Editions. ITPro Today. Retrieved from http://www.itprotoday.com/microsoft-sql-server/sql-server-2012-editions

Petkovic, D. (2008). Microsoft SQL Server 2008 A Beginner’S Guide. McGraw-Hill Osborne Media.

Bob, B. (2012). SQL Server 2012 Security Best Practices– Operational and Admnistrative Tasks. SQL Skills

Grobauer, B, Walloschek, T, Stocker, E. (2011) Understanding Cloud Computing Vulnerabilities.IEEE Security Privacy

McCray, J.(2009). Advanced SQL injection. In: DEFCON Hacking Conference

Garcia et.al. (2009). Anomaly-based network intrusion detection: techniques, systems and chalanges. Computers and Security 28, 18-28 Retrived from, http://www.protegrity.com/data-security-platform

Wagner, P, J. (2008). Database System Security. In: University of Mennesota Summer School for Information Assurance, UMMSSIA

Kothari, S,C. (2006). Web applications security SQL injection attack. USA. Electrical and Computer Engineering Department, Iowa State University.

Anley, C. (2002). Advanced SQL injection in SQL server Applications. NGS Software Insight Security Research Publication Retrieved from, http://scholar.google.com/

Meier, J. D. (2003) Improving Web Application Security. Threats and countermeasures, Microsoft press