Secure your SQL Server (12 Tips on how to make SQL Server more secured)
Security for a database is always a very serious business. Fortunately SQL Server is classified most secured commercial database system available in the market (yes… it is true that SQL Server is more secured than it’s competitors and this research is backed by third party companies).
Though default installation of SQL Server itself is enough secure, still you should not keep any unnecessary feature enabled. While enabling a feature adds new functionality to SQL Server, it always opens a possible door for security breach also.
Once you installed SQL Server, you should follow below points to make the server installation more secured:
1. Authentication: Use windows integrated authentication only. Do not allow mixed mode authentication unless absolutely needed. If you are forced to use mixed mode authentication, use complex password requirement and enforce password expiration policy. Also encourage developers and business to enhance their application to take advantage of Windows authentication in near future and revert back to Windows authentication only as soon as you can.
2. Remote access and network protocol: After the installation, you need to configure the network protocols to allow remote connections. Do not keep any unnecessary protocol enabled. (For example, if you do not use Named Pipe, make sure it is disabled)
3. Ad Hoc Distributed Queries: Keep this feature disabled unless absolutely necessary to enable it. This feature allows a user to execute OPENROWSET and OPENDATASOURCE and passwords are embedded into the text of the query. This may expose a login and password to an attacker. If you need to frequently access remote data sources, you should use a linked server instead.
4. CLR: Keep this feature disabled unless absolutely necessary to enable it. Common Language Runtime (CLR) enables triggers, functions, and stored procedures that have been written in .NET languages such as C#.NET to execute within your SQL Server engine.
5. Cross Database Ownership Chaining (CDOC): Keep this feature disabled unless absolutely necessary to enable it.
6. Database Mail: Keep this feature disabled unless absolutely necessary to enable it.
7. External Key Management: Keep this feature disabled unless absolutely necessary to enable it.
8. Filestream Access Level: Keep this feature disabled unless absolutely necessary to enable it.
9. OLE Automation Procedures: Keep this feature disabled unless absolutely necessary to enable it. Practically you can replace any OLE automation procedure with a CLR procedure which is more secured and stable.
10. Remote Admin Connections: Keep this feature disabled unless absolutely necessary to enable it. It is best to use DAC after you first login to the server itself.
11. SQL Mail: Keep this feature disabled unless absolutely necessary to enable it. This is for backward compatibility only and you should work with business and development to modify their application to remove dependency on SQL Mail. They can use Database mail instead.
12. Xp_cmdshell: Keep this feature disabled unless absolutely necessary to enable it. This enables the use of xp_cmdshell so that you can execute operating system commands.
Though default installation of SQL Server itself is enough secure, still you should not keep any unnecessary feature enabled. While enabling a feature adds new functionality to SQL Server, it always opens a possible door for security breach also.
Once you installed SQL Server, you should follow below points to make the server installation more secured:
1. Authentication: Use windows integrated authentication only. Do not allow mixed mode authentication unless absolutely needed. If you are forced to use mixed mode authentication, use complex password requirement and enforce password expiration policy. Also encourage developers and business to enhance their application to take advantage of Windows authentication in near future and revert back to Windows authentication only as soon as you can.
2. Remote access and network protocol: After the installation, you need to configure the network protocols to allow remote connections. Do not keep any unnecessary protocol enabled. (For example, if you do not use Named Pipe, make sure it is disabled)
3. Ad Hoc Distributed Queries: Keep this feature disabled unless absolutely necessary to enable it. This feature allows a user to execute OPENROWSET and OPENDATASOURCE and passwords are embedded into the text of the query. This may expose a login and password to an attacker. If you need to frequently access remote data sources, you should use a linked server instead.
4. CLR: Keep this feature disabled unless absolutely necessary to enable it. Common Language Runtime (CLR) enables triggers, functions, and stored procedures that have been written in .NET languages such as C#.NET to execute within your SQL Server engine.
5. Cross Database Ownership Chaining (CDOC): Keep this feature disabled unless absolutely necessary to enable it.
6. Database Mail: Keep this feature disabled unless absolutely necessary to enable it.
7. External Key Management: Keep this feature disabled unless absolutely necessary to enable it.
8. Filestream Access Level: Keep this feature disabled unless absolutely necessary to enable it.
9. OLE Automation Procedures: Keep this feature disabled unless absolutely necessary to enable it. Practically you can replace any OLE automation procedure with a CLR procedure which is more secured and stable.
10. Remote Admin Connections: Keep this feature disabled unless absolutely necessary to enable it. It is best to use DAC after you first login to the server itself.
11. SQL Mail: Keep this feature disabled unless absolutely necessary to enable it. This is for backward compatibility only and you should work with business and development to modify their application to remove dependency on SQL Mail. They can use Database mail instead.
12. Xp_cmdshell: Keep this feature disabled unless absolutely necessary to enable it. This enables the use of xp_cmdshell so that you can execute operating system commands.
Comments