Security! This is the word that comes to mind of every concerned person when it comes to storing, accessing, and sharing the data and database or database server. At times when applications are run in geographically restricted areas, there is less chance of their exploitation. However, with the spread of the internet and the availability of applications over the internet, the security of data, databases, and database servers has become vital. Because the Data is new GOLD. In this very article, I will share a few pointers you can consider while hardening your SQL Server. In this article I will share details on How to Secure SQL Server.
Please do not forgot to visit this place next week as I am going to share a checklist that you can use while you are working on hardening your SQL Server.
Why SQL Server Security has been so important!!
Well, every organization, institute, company, or government has their data that is vital to them, and not to be accessed by the authorized person or entity only. That is vital to set some security standards for such critical data so that you can protect your data as much as you can with proper security mechanisms and a set of standards within the organization and in your scope for MS SQL Server Security.
I have penned down some pointers/criteria that require serious consideration when it comes to securing MS SQL Server.
1. Guest User Account: It is always good practice to disable a guest user account; this will keep our server robust from a skilled user who can make use of a guest user account to gain access to the server.
Skilled users always look into a guest user account to establish a NULL session with which he/she can compromise the server.
2. Public Account: Do not grant permission to the Public role, as every single user is a member of this role; hence if you grant any permission to this role it will be available to every user and that will breach security standards.
3. System Administrator Account: We would need to rename and disable the System Administrator account as best security practice; to do so we need to create an integrated account that has SA permissions then create users and assign them appropriate permission based on the fact who needs to access what data!! One of the best practices is also to rename or disable the SA account, it is advisable that we follow this best practice. However, make sure you have one account having equivalent permissions as of SA, also advisable to remove the “Built-inAdministrator” group.
4. Application Roles: While answering threads at Microsoft/Non-Microsoft Forums for SQL Server most of the time what I’ve found is developers/users/dba(s) just avoid creating and/or maintaining schemas/application roles. Actually, it does lots of help to us in terms of rework (granting and/or revoking permissions) and easy manageability of SQL users. Above all if a guest user is not mapped to the application role of the database, the guest user cannot access to database object; we can audit the application role’s activity.
5. Strong Password Mechanism: This is the most vulnerable thing; if our password mechanism is not that strong, one can easily get into our box and steal our data or can do whatever he/she wants to do with it; Sometimes we may find users with NULL/TRIVIAL passwords. Again, this is much more critical if the application is accessed over the Internet we are openly inviting thieves!!!
6. Access port: By default SQL Server listens to TCP port 1433 and UDP port listens to 1434 which is known to everybody; we would need to change it to another port then default and a firewall rule should be created accordingly for exceptions.
7. NTFS file system: The NTFS file system was introduced in early 1993 with the launch of Windows NT 3.5, This is a file system that has some good features that were not available in the FAT 16 and FAT 32 file systems. File and Folder level security is the key benefit of this file system which also benefited us to keep our SQL Server files secure by assigning appropriate permissions.
8. Updating Server: Microsoft keeps releasing service packs (SP) and hot fix time-to-time to keep software more secure, robust, and bug-free. It is recommended that we should update our box with the latest SP(s) and hot-fix on a regular basis.
9. Audit: We should enable audit for login failures and warning errors which need to be monitored on a daily basis so that if any error, login failure or suspected login attempt is notified we can take necessary action based on the facts available in Log, this way we can foresee any probable vulnerability or can avoid it to be happened.
10. Integrated Logins: Using this feature one can assure him/herself that the SQL box is more secure; integrated/Windows authentication* uses a domain account to access server, database, and database objects. Here, whenever a user tries to access the SQL box his/her account is validated by the domain controller first and then permitted or denied to access the system without requiring a separate login id and password; after this, it will check with SQL Server for the kind of permission this user(s) has.
The other benefit is one can use encrypted passwords, and various handshake methods like PKI, Kerberos, EAP, SSL Certificates, NAP, LDP, and IPSec policy; this will ensure our highly critical data are being sent securely over the network.
11. Instances: We can create different Instances to isolate the development/production environments from each other; and/or isolate users from accessing databases that are not meant for them. This can be done by application roles/fixed DB / fixed server roles but using instances we can hide the names of the databases from the users this way they don’t even come to know which databases are there on the server and who is accessing which database.
12. Service account: Always use the least privileged user account to start the server and agent service on the server. A domain account with the local admin privilege is enough to start the services; a domain user account for services is required if we have to work on some special services that require network access also like replication, log shipping, mirroring, cluster, remote procedure calls, backing up-restoration on or from network and remote data access.
13. Network Library: Don’t install and allow network libraries except those that are required.
14. Isolated from IIS: Though we can have both SQL Server and IIS on a single machine; it is advisable to keep them on a separate machine. The idea is; that even if the IIS server is compromised our database(s) are secure, the other benefit is if more memory and processor resource is eaten by the IIS server SQL box will not suffer.
15. Stored Procedure: We should have to make a practice to wrap DML statements in SP(s) to avoid SQL injection.
16. Monitoring SQL Server and Windows Event Viewer: We shall keep our eye on SQL Error Log and Windows Event Viewer for any suspicious activity on a daily basis so that we can take corrective action(s) as soon as it is identified.
17. Encrypting data: I recommend you leverage Transparent Data Encryption aka TDE for all the user databases you have on your system.
18. Linked server: Prevent access to the linked server from those users who don’t need access by assigning proper privileges.
19. System Stored Procedure(s): System stored procedures (SP) like xp_cmdshell, xp_regread, xp_regwrite needs to be restricted to access. Using this SP(s) one can easily read, modify, or delete registry information or can manipulate system information.
20. Anti Virus: We should install an Antivirus on SQL Box and exclude SQL server database files; this way we can be assured of Virus/Trojan/Malware/Spam attacks on our servers that could harm our database files.
Conclusion:
As time passes Securing data has become the most vital part, and we must agree and honor it. This is the information that contains our financial, social, business, and historical data; and as a DBA it is our prime responsibility to make sure that this has been taken care of and is secure enough. These are the key points that I’ve collected so far, If anybody would like to draw my attention to some point that I might have missed out, can write back their comments here.
Photo Credit : Free Stock photos by Vecteezy