Reviewing Security Configuration of SQL Server
Security is one of the most important aspects for any technology. As SQL Server is an RDBMS product which stores users data which is important and critical, so it is common to see many attempts been made to compromise and get access to the important user or business related data. This brings responsibility of the DBAs, developers, management to plan and implement best security practices to avoid any such attacks from succeeding. It is important to periodically review the security settings to make sure that there are no gaps or possibilities of security breach. Below are list of items or objects which a DBA team may want to periodically review and verify to make sure that the security configuration is as per best practices.
Database Owners: By default, any users who creates the databases becomes the owner of the database. Mostly DBAs who create the databases become the owner of that database, so it is important to immediately change the database owner to SA or other standard account. Periodically review the database owners to make sure there are no user specific domain accounts being the database owners.
Windows or SQL Server Logins: Periodically review all the windows logins, SQL Server logins and windows groups which has access to the SQL Server instance. Remove any logins which does not require access anymore. It happens that some user leaves the company but the logins still remain. Sometimes access is given on temporary basis for a project work and would not removed after the completion of the project. Periodic review of the logins should resolve this issue of unauthorized logins being present at server level or at SQL Server level.
Server Level Permissions: There are different kinds of server level and database level roles provided by SQL Server. Make sure that only the required permissions are granted to the users. Providing more permissions than what is required will cause serious problems. It is important to periodically review and revoke any additional accesses granted to any users.
SQL Agent Jobs Owned by Windows Domain Accounts: The DBA who creates a SQL Server Agent job by default becomes owner of that job, so make sure to change it to SA
.SQL Server Client Protocols: By default SQL Server allows communication through ports(TCP/IP, Shared Memory, Named Pipes, VIA). Mostly it is not required for all these protocols to be enabled, some of them can be disabled when application does not use them, so that any one trying to gain access through that protocol would be blocked.
SQL Server TCP/IP port: Default instance of SQL Server uses port 1433 by default, so it is advisable to change to this some other port other than 1433 as per best practices. But making this change may cause applications to fail connecting to SQL instance, so care has to be taken to make changes to application connection string to use the port number used by SQL Server or to enable SQL Browser service. SQL Server Named instances can be configured to use either permamnent statis port or a dynamic port which may change after SQL or server restart. It is advisable to configure static port for SQL Server Named instances.
The above checks or recommendations are high level and there are mode advanced check that are to be performed for more critical servers. There are compliances like SOX HIPAA which put rules to be followed for critical servers. Also, care should be taken to implement the recommendations and proper testing has to be done prior to any implementation to make sure applications does not break due to the changes.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings