Friday, July 26, 2013

SQL Server Security Best Practices


Introduction

One of the primary responsibilities of Database Administrator is to Secure all SQL Server which they manage. SQL Server Security in itself is a very vast topic hence this article outlines some of the SQL Server Best Practices which Database Administrators can follow to Secure SQL Servers in their environment.

Regularly Audit SQL Server Logins

Database administrators should enable login auditing feature of SQL Server on all instances which they manage. Once this feature is enabled SQL Server will write all the failed and successful login information in SQL Server Error Logs. It is always a best practice to auditing failed SQL Server logins. Read the following article which explains “How to Auditing SQL Server Logins”.

Limited Privileges to SQL Server Service Account

Database administrator should always avoid running SQL Server Services under the context of Local Administrator, Local System or a Domain Administrator Account. All SQL Server Services should be configured to run under a minimal privileged Local Domain Account. SQL Server Service account should always be given Full Control Permissions on Data, Logs and Backup Directory to perform read and write activities. In case if you wish to change the default backup directory in SQL Server after installation then read the following article which explains “How to change the default database backup folder location in SQL Server”.

Always Setup Complex Passwords for SQL Server SA Account & SQL Server Logins

If you have configured SQL Server Instance to run under Mixed Mode Authentication then as a best practice one should always set a complex password for a System Administrator (SA) account. As a best practice one should change the SA password at regular intervals. Always use a windows account which has System Administrator to perform day-to-day maintenance activities on SQL Server. If there is more than one DBA in a team then it is recommended to create a Windows Domain Group and add all DBAs to that group and then give the windows domain group System Administrator Privileges on SQL Server. Read the following article which explains “How to identify currently used SQL Server authentication mode”.

Enforce Password Policies and Password Expiration for SQL Server Logins

It is always recommend to Enforce Password Policies and Password Expiration for SQL Server Logins when you use Mixed Mode Authentication. This will allow you to set complex password for a SQL Server Login. Read the following article which explains “How to enforce password policies and password expiration for SQL Server Logins”.

Newsletter Signup

Avoid Using SQL Server Authentication and Promote the Usage of Windows Authentication

As a best practice, to connect to a SQL Server instance one should always use Windows Authentication. The simple reason being when you use windows authentication the user passwords are authenticated by the active directory for windows logins thereby leveraging password policies set by your organization.

Periodic review of Windows and SQL Server Logins

As a best practice database administrators should perform a periodic review of all Windows and SQL Server Logins on every instance of SQL Server which they manage. This way you can remove logins which are no more relevant on the server. DBAs should very clear document all the logins along with each respective privilege within the Disaster Recovery document.

Encrypt SQL Server Database Backups

One of the primary responsibilities of a Database Administrator is to make sure all the databases are backed up regularly and the backups are restored on Disaster Recovery environments to make sure they are usable when they are needed the most. However, at the same time you need to make sure the database backups are encrypted to avoid the misuse. Learn more about Transparent Data Encryption Feature of SQL Server to know how to encrypt the database backups in SQL Server.

Secure Database Backup Folder from Unauthorized Access

Database backups are very critical and its DBAs responsibility to safeguard them from unauthorized user access. As a best practice access to database backup folders should be restricted and only those who really need access should be granted access. Unauthorized access to backup folders can be very dangerous as users can delete critical backup files, copy the backups to unauthorized locations etc.

Running SQL Server in Different Port other than the Default Port

By default SQL Server uses the TCP/IP Port 1433 for the Database Engine. As a best practice DBAs should change the TCP/IP Port on which SQL Server Database Engine will be listening. These changes must always be done using SQL Server Configuration Manager.

Disable SQL Server Browser Service

As a Security Best Practice database administrators should Disable SQL Server Browser Service when running the default instance of SQL Server. If there are any named instances of SQL Server then one must explicitly specify the port number within the connection strings to connect to the named instance. SQL Server Browser Service uses by default UDP Port 1434 for connectivity.

Hide an Instance of SQL Server

Database administrators can also hide an instance of SQL Server. This can be easily done by using SQL Server Configuration Manager. The SQL Server Browser Service basically enumerates instances of the Database Engine installed on the computer thereby enabling applications to browser for a server. Read the following article which explains “How to Hide an Instance of SQL Server”.

Encrypting Connections to SQL Server

Database administrators can enable Secure Sockets Layer (SSL) to encrypt the data which is transmitted across a network between the client application and the instance of SQL Server. SSL encryption increases the security of data which is transferred across the network between an application and SQL Server. However, one need to understand that enabling encryption will slow down the performance a bit. Hence it is always recommended to perform all testing before implementing in a production environment. Read the following article which explains “How to encrypt connections in SQL Server”.

Install Latest Service Packs and Hot fixes

As a best practice Database Administrators should always patch the SQL Servers which they manage with the latest service packs and hot fixes. Read the following article which has a complete list of all the Service Packs, Hot fixes and Cumulative Updates released by Microsoft for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Check the above links to see which patches are missing on your SQL Server Instances.

Dedicated SQL Server Connection

Dedicated SQL Server Connection is one very important feature which DBA’s must consider enabling it across all servers which they manage. This feature can help you troubleshoot issue rather than just restarting SQL Services during emergencies. Read the following article which explains “How to Use Dedicated Administrator Connection in SQL Server”.

Disable All the Unused SQL Server Features

As a best practice database administrators should always disable unused features of SQL Server such as OLE AUTOMATION, XP_CMDSHELL and OPENROWSET etc to reduce surface area attacks. These features can be easily be disabled or enabled using SQL Server Configuration Manager if you are using SQL Server 2005. If you are using a higher version of SQL Server then these features can be easily managed using Policy Based Management Feature which was initially introduced in SQL Server 2008.

Conclusion

SQL Server Security in itself is a vast topic and in this article we have discussed few of the settings which can be easily implemented to improve the overall security of the SQL Servers which you manage day-to-day in your work.


Read more: http://www.mytechmantra.com/LearnSQLServer/SQL-Server-Security-Best-Practices/#ixzz3h4cLiiFs
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook


Question asked by one of my DBA friends.



One of my DBA friends asks me some SQL related Question. Here I am trying to explain it in this article

What are necessary steps should be taken for query execution time faster, suppose a Query when it's executing taking time 3min now I want to execute this query within 30secs how?

Answer: 

There are lots of factors related to make quay execution faster. My strong suggestion is to understand the execution plan to execute your query in better ways.

Some common factors that we all knows, related to SQL query executions are mentioned bellow.

a.    Don't use "SELECT * " in a SQL query. That means use the proper columns name that you needed not overload the query by using *. That added extra expenses to data retrieval.
b.    Don't use extra table join that you don't needed in your SQL statement.
c.    Don't use COUNT(*) in Sub query, instead use EXIST or NOT EXIST Clause.

      -- Do not Use
                    SELECT column_list
                    FROM   table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)
                    -- Use This
                    SELECT column_list
                    FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)

d.    Avoided joining between two types of columns like "INT" and "FLOAT" and  
      don't try to use CONVERT or CAST.
e.   Try to avoid dynamic SQL
f.    Try to avoid Temporary table
g.   Don't use IN or NOT IN clause in SQL statement, instead use EXISTS or
      NOT EXISTS
h.   Avoid LIKE instead you can use full text search.
i.    Try to use UNION or UNION ALL to implement OR operators.
j.    Don't calls any function in SELET statements, I mean try to avoid.
k.   Try to avoid correlated sub query
l.    Try to use stored procedure to execute all your T-SQL statement.
m.  Use VIEWs but always use WITH SCHEMA BINDING options
n.   Last but most important, solid idea related to Index. If necessary use
      guided index in your SQL join operations.

What is the difference between 'SET' and 'SELECT' in SQL?

Answer:

DECLARE @i INT

--Type1
SELECT @i=Roll FROM MyTab WHERE Name='RAJA'
--Type2
SET @i=(SELECT Roll FROM MyTab WHERE Name='RAJA')

Consider the above two SQL statements, the variable @i have the same value. So, what is the difference between two statements?

1.     From SQL server 7.0 Microsoft recommends to use the SET statement only, in such kind of above operations.
2.     SET is ANSI standard way to assign the value of a variable.
3.     BY SELECT we can assigned multiple value to multiple variable within a single statements. Like this.

DECLARE @i INT,
                           @k VARCHAR(MAX)  

          SELECT @i=Roll, @k=SName FROM MyTab WHERE SName='RAJA'

4.     SELECT has some difficulties like, if the above statements return more than one rows it not return any error. But if you use SET in the above example it give you error like "Sub query returns more than one values".

So at the conclusions, I recommended you to use SET and not to go at SELECT when assigning variables.

   What are the basic differences between SSMS 2005 & SSMS 2008?

Answer:

As per me, there are no differences; the difference is the database engine Limitations of SQL 2005 and SQL 2008. SQL 2008 gives you the better functionality and performance then SQL 2005.
Posted by at
Labels: