HIPAA or EPA compliance SQL Server Database
If you look around in IT market, you will find so many software product available for different type of laboratory like environment, pharmaceutical and many more. You will also find so many software product available for health care, hospitals, insurance and doctors. I have observed many times that while developing all these application, many of the US government agency rules are ignored. If you are selling your product in USA, you shouldn’t ignore the rules & regulation provided by agencies. EPA (Environmental Protection Agency) and HIPAA (Health Insurance Portability and Accountability Act) are two of the example of those agency who controls environmental laboratories and health care company respectively.
I have personally observed , in my few of the past consultation project , that Software company, many times, ignore rules given by these kind of agencies while developing product. This ignorance or lack of knowledge will fit you in critical situation along with the user of this software product. Not only HIPAA or EPA but almost each government agencies always enforce security for database. They will not allow any security breach in database. There may be different level of security and rules given by different agencies but my intention is to define some generic advice to make the database & SQL Server security little more tighten. According to me the security advice are given in this article should be regardless of which software product you are using/developing or which government agency is going to audit your environment. You can apply even more security then given in this article but these advice should be implemented first to make sure that your server is not exposed to security breaches.
“SA” Account: SA is one of the most powerful login with “SysAdmin” permission. This MUST be either disable or renamed because by having access of SA, you are guaranteed that you can do almost anything you wish which is not at all acceptable. Each individual/application should have their own login with the least permission which really requires. I have even observed that most of the DBA keeps using SA account so that they can do anything they want which is again not at all acceptable. Everybody should use login with only “ReadOnly” permission so that you can save yourself from accidental update/delete or schema change. Quit a few times it happens that you are using account like SA in SSMS and accidentally rename table/SP/View name which is resulted in application crash. If you intend to make any change, you should use other login.
Windows Authentication: If your database/application or users are part of same network, you should use windows authentication only so that your access to SQL Server is controlled by your Windows Operating System account or group, which is authenticated when you log on to the Windows operating system on the client.
Enforce Password Policy: It is seen that many people used to check off “Enforce Password Policy” check box while creating new login in SQL Server. There may be many reason behind this behavior like don’t want to remember complex password or long password, don’t want to keep changing password etc. but let us not discuss why people do it. The main point is, keep enforcing user to choose complex password. Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the criteria defined.
Security Certificate: it is one of the good way to go for trusted certificate authority to get your TLS/SSL certificate signed rather creating your own, especially when your product is enable to public.
“Guest” login : Guest login should be revoked with CONNECT permission to all user defined database.
Seek Integrity and Encryption:
Integrity: Database design MUST support referential integrity with help of Primary Key and Foreign Key along with Unique Index & Check Constraint whenever required. This will ensure integrity of your data.
Backup: There should be strong backup policy which takes Full, Transaction and differential backup. Full backup should be compressed and encrypted too.
Encryption: all sensitive information in database should be encrypted and all certificates/key used for encryption should be backed up properly for future purpose. I have written few articles for subject ” Encryption Decryption “, have a look at that, if you are interested to learn more.
Login: Each successful and failed login attempt should be recorded for future use of auditing.
Database Trigger: Set database trigger to keep an eye on the changes of schema in database object. know more details about database trigger, click here.
Capture data change: enable CDC (Change Data Capture) in SQL Server to keep a track record of the change being made in your table for future auditing purpose. to know more about CDC, click here.
Well, these are some generic guideline to make your SQL Server and database little more secure. It is highly advisable that you study the specification along with rules/regulation list provided by the government agency to make your SQL Server fully compliant. It is really MUST HAVE thing in today’s digital age. I am thankful to all the companies I have worked as a consultant in past for accepting the suggestions I have given to make server more secure.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles .