Secure your SQL Server Data
Security! This is a top-of-mind word for every concerned person when it comes to storing, accessing, and sharing data, databases, or database servers. When applications are running in a restricted area, they are less likely to be exploited. However, with the increasing popularity of the Internet and the availability of applications, the security of data, databases, and database servers has become vital.
Why is SQL Server Security so important?
Every organization, institute, company, or government has data that is vital to them and must only be accessed by an authorized person or entity. Setting some security standards for such valuable data is necessary to protect it as much as possible with proper security mechanisms and a set of standards within your organization and in your scope for MS SQL Server Security.
I have penned 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 guest user accounts. This will keep your server robust in the face of skilled users that can make use of guest user accounts to gain access to the server. Skilled users always look into a guest user account to establish a NULL session which allows them to compromise the server.
2. Public Account: Do not grant permission to 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: Rename and disable System Administrator accounts as a best security practice. To do this, create an integrated account that has SA permissions, then create users and assign them appropriate permission based on who needs to access what data.
4. Application Roles: While answering threads in Microsoft/Non-Microsoft Forums for SQL Server, I’ve found that developers/users/dba(s) usually just avoid creating and/or maintaining schemas/application roles. Actually, it helps us in terms of rework (granting and/or revoking permissions) and easy manageability of SQL users. Above all, if guest users are not mapped to the application role of database, guest users cannot access the database object. You can also audit the application role’s activity.
5. Strong Password Mechanism: This is the most vulnerable element. If your password mechanism is not strong enough, someone can easily get into your box and steal your data or do whatever he/she wants to do with it. Sometimes, you may find users with NULL/TRIVIAL passwords. Again, this is much more critical if the application is accessed over the Internet: You are openly inviting thieves!
6. Access Port: By default, SQL Server 2000 listens to TCP port 1433 and UDP port listens to 1434, which is common knowledge. You would need to change it to another port, then create default and firewall rules according to the exceptions you created. SQL Server 2005 listens to a dynamic port. Whenever a SQL Server 2005 named instance starts, it is assigned a port automatically, but, as always, you may change it to a static port. Refer below:
7. NTFS File System: The NTFS file system was introduced in early 1993 with the launch of Windows NT 3.5. This file system 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 allows you to keep your SQL Server files secure by assigning appropriate permissions.
8. Patching Server: Microsoft regularly releases service packs (SP) and hot-fix to keep software more secure, robust, and bug free. It is recommended to update your box with the latest SP(s) and hot-fix on a regular basis.
9. Audit: You should enable audit for login failures and warning errors. They need to be monitored on a daily basis so that any error, login failure, or suspected login attempt will be identified and necessary action can be taken. This way, you can foresee any probable vulnerabilities or avoid them.
10. Integrated Logins: By using this feature, you can be assured that the SQL box is more secure. Integrated/windows authentication* uses domain accounts to access servers, databases, and database objects. Here, whenever a user tries to access the SQL box, his/her account is validated by a domain controller first, which then permits or denies their access to the system without requiring a separate login id or password. After this, it will check with the SQL Server to see which kind of permission this user has.
Other benefits are encrypted passwords and various handshake methods like PKI, Kerberos, EAP, SSL Certificates, NAP, LDP, and IPSec policy. This will ensure that your highly valuable data is being sent securely over the network.
Corporate Environment Security Example
11. Instances: You can create different instances to isolate development/production environments from each other and/or prevent users from accessing databases they are not meant to. This can be done by application roles/fixed DB/fixed server roles, but by using instances, you can hide the names of the databases from the users. This way, they won’t even know which databases are on the server and who has access.
12. Service Account: Always use less privileged user accounts to start the server and the agent service on the server. A domain account with the local admin privilege is enough to start the services. Furthermore, a domain user account for services is required if you have to work on some special services that require network access 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 or allow network libraries except those that are required.
14. Isolated from IIS: Though you can have both SQL Server and IIS on a single machine, it is advisable to keep them on separate machines. The idea is that even if th
2 Comments. Leave new
tank you
Super tips…very useful