SQL Server Security

SQL Server Security

This is first post about SQL Server Security, in this post we will speak about Sever Configuration:

First you need clear plan about your installation; what services you want, and what you aren’t, if you need SSAS, SSRS, Filestream and so on
.

In SQL server you need service account to install services in the server for database engine and SQL server agent and so on, those service accounts must be created with least permission – the permission needed to run the service - if you provide the service account with more than required permission this may affect your computer if an attacker compromise your account. This is first step in the security of SQL server.

Another important thing is don’t install services that you doesn’t want it; this will minimize the vulnerability in your system.

Steps to stop, start or restart SQL Server services:

·         Go to start menu -> SQL Server 2008 R2 -> Configuration Tools

·         Click on SQL Server Configuration Manager.

·         Double click on the desired service.

o   Instead of double click you can right click and choose stop, restart, or start.

·         A window will open in this window you can change service account, stop and start service.

In SQL Server Configuration Manager you can enable and disable remote access to your SQL server server by:

·         SQL Server Network Configuration -> Protocols for InstanceName

·         Double click TCP-IP and in enable choose no.

This will prevent you other computer from access to SQL server.

In SQL server there are two types of authentication:

·         Windows authentication:

In this mode you won’t prompted for user name and password, SQL server validates the account name and password using the Windows principal token in the operating system (http://msdn.microsoft.com/en-us/library/ms144284%28v=sql.105%29.aspx ) Windows authentication is the default and more secure than Mixed mode.

·         SQL Server and Windows authentication mode (Mixed mode)

In this mode you can connect using Windows authentication or SQL server user name and password in case of SQL server authentication you must provide user name and password.

Steps to change Authentication Mode:

In installation you will be prompted for authentication mode and the default is Windows authentication mode.

1.       Go to start menu -> SQL Server 2008 R2.

2.       Click on SQL Server Management Studio (SSMS).

3.       You will be prompted for connection provide your server name and connect.

4.       After you connected successfully right click on your server and choose properties.

5.       In the left pane choose Security.

6.       And then choose the authentication you want.

7.       Restart SQL Server server.

If you for some reason configure your SQL server to use Mixed Mode:

·         Make sure that you provide strong password.

·         Do not enable sa account except if you necessary need it (for example with policy).

·         If you enable sa rename the sa account.

·         Create strong password for sa account.

To check if the sa account is set with no password you can use the following query

select * from OPENROWSET('SQLOLEDB','servername;'sa';'','SELECT * FROM sys.databases')

Reduce the attack surface area:

You need to disable any unused feature, run this command to check for enabled and disabled feature

SP_CONFIGURE

Check those links to know more about server configuration

http://msdn.microsoft.com/en-us/library/ms188787.aspx

http://msdn.microsoft.com/en-us/library/ms189631.aspx

This is a general configuration for your SQL server

In the upcoming topics we will speak about securing data in your database by using encryption, encrypting column, or the whole database event in the backup tape.

Written by:

                Elmozamil Elamir Hamid