Security! This is the word comes in mind of every concerned person when it comes to
store,
access and
share the
data and
database or
database server. At times when applications are run in the geographically restricted area, there were less chance of its exploitation and with spread of internet and the availability of application over the internet,
security of data, databases and
database servers has become vital.
Why SQL Server Security has been so much important!!
Well, for every organization or institute or company or government has there data that is vital to them, and not to be accessed by the authorized person or entity only. And that is vital to set some security standards for such a critical data so that you can prevent your data as much as you can with proper security mechanism and set of standards within organization and in your scope for MS SQL Server Security.
I have pen down some pointers/criteria that requires serious consideration when it comes to secure MS SQL Server.
1. Guest User Account: It is always good practice to disable guest user account; this will keep our server robust from skilled user that can make use of guest user account to gain access over the server.
Skilled user always looked into a guest user account to establish a NULL session with which, he/she can compromise 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: We would need to rename and disable System Administrator account as best security practice; to do so we need to create integrated account that has SA permissions then create users and assign them appropriate permission based on the fact who need to access what data!! One of the best practice 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 "Built-in\Administrator" 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 helps to us in terms of rework (granting and/or revoking permissions) and easy manageability of sql users. Above all if guest user is not mapped to application role of database, guest user cannot access to database object; we can audit application role’s activity also.
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; sometimes we may found users with NULL/TRIVIAL passwords. Again, this is much more critical if application is accessed over Internet we 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 known to everybody; we would need to change it to another port then default and firewall rule should be created accordingly for exceptions. SQL Server 2005 listens to dynamic port*
7. NTFS file system: NTFS file system is introduced in early 1993 with launch of Windows NT 3.5, this is the file system that has some good feature that were not available in FAT 16 and FAT 32 file system. 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 keep 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 latest SP(s) and hot-fix on regular basis.
9. Audit: We should enable audit for login failures and warning errors and need to be monitored on daily basis so that if any error, login failure or suspected login attempt will be notified and we could take necessary action based on the fact 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 sql box is more secure; integrated/windows authentication* uses domain account to access server, database and database objects. Here, whenever user tries to access sql box his/her account is validated by domain controller first and then permit or denying to access the system without requiring separate login id and password; after this it will check with SQL Server for kind of permission this user(s) has.
The other benefit is one can use encrypted password, 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.
*
http://msdn.microsoft.com/en-us/library/aa905171(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa905172(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa905190(SQL.80).aspx
11. Instances: We can create different Instances to isolate development/production environment from each other; and / or isolating users from accessing database which are not meant for them. This can be done by application roles/fixed DB / fixed server roles but using instances we can hide name of the databases from the users this way they don’t even come to know which databases are there on server and who is accessing which database.
12. Service account: Always use least privileged user account to start the server and agent service on the server. Domain account with the local admin privilege is enough to start the services; domain user account for services is required if we would have to work on some special services which requires 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 installed and allowed network library except those are required.
14. Isolated from IIS: Though we can have both SQL Server and IIS on single machine; it is advisable to keep them on separate machine. The idea is; even if IIS server is compromise our database(s) are secure, the other benefit is if more memory and processer resource is eaten by 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 out eye on SQL Error Log and Windows Event Viewer for any suspicious activity on daily basis so that we can take corrective action(s) as soon as it is identified.
17. Encrypting data: We should use WITH ENCRYPTION option to keep our code safe; and for data we could use symmetric encrypted columns in SQL Server 2005 or third party tools like activecrypt for data encryption in SQL Server 2000.
18. Linked server: Prevent access to linked server from those users who don’t need to 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, modifies or deletes registry information or can manipulate system information.
20. Anti Virus: We should install Antivirus on SQL Box and exclude sql server database files; this way we can be assure from Virus/Trojan/Malware/Spam attacks on our servers that could harm our database files.
Conclusion:
As the time passes Securing data has become the most vital part, and we must agree and honor it. Because 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 and 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 there comments here.
Update: Edited point 3, added a line saying "SA" account should be renamed or disabled according to best practice.
Recently I had a chance to speak my friend Ashish Sharma who is my colleague too. We were discussing Project Management topics (he recently passed PMP and ACP). Our discussion ended at very interesting topic – RISK Assessment.
