It’s of great importance for those webmasters who run websites on a Windows server to come up with some measures to ensure the security of SQL Server databases. If you search online, you will probably find a number of relevant methods. In below, we mainly summarized the most practical ones that work.
Improve Security of Operating System
.jpg)
A secure server and an operating system are very important for the security of SQL Server. Hence, you’d better:
- Use the newest service packs and configure all the important updates to fix bug and enhance security.
- Prepare firewall for your SQL Server. Similar to a separator, a firewall operates between a reliable network and an unreliable network. It takes effect to keep those bad traffic away. You may use either Windows Firewall or use different firewall rules to SQL Database. To configure a firewall calls for a high-level skill, if you are not knowledgeable enough about firewall, we suggest you look for help from people who are experienced in this aspect.
- Control the surface area of your SQL Server by operating the necessary services just with the newest privileges, which lessens the paths that may be taken use of by attackers to attack your system.
Carefully Manage Accounts and Passwords
User accounts and passwords are to a database what doors and keys are to a house, because they play a role of protector. The suggestions below should be of great help for securing SQL server databases.
Make the system administrator account disabled
An SA account, created automatically in the process of SQL Server installation, is widely recognized to the world and constantly targeted by attackers. If hackers intrude to your SQL Server with some SA privileges, he or she is able to do anything he or she likes. Therefore, you must guard the most significant procedure of your database against attackers. To do this, you are recommended to create a fresh account and distribute the administrator privileges to this newly established account, then make the SA account disabled and reset its name.
Decrease administrators and restrict their privileges
Cut down user accounts of SysAdmin and the db_owner database, and only give permissions to people who are required to administrate the related tasks. Distribute the proprietorship of different databases to several admin accounts so some of the databases can preserve when one of the administrator accounts is disabled.
Do not use guest user accounts
Those guest user accounts, on the one hand, can be convenient for enabling other users to do some contributions to your website, but on the other hand, they can also be deficiencies that enable expert hackers to enter your SQL Server.
Reinforce the password system
Strong passwords are of great importance, especially passwords administrator accounts. Therefore, you must use complex passwords for those user accounts that can access to SQL Server. Generally speaking, a strong and trustworthy password can not be a short one and should be a combination of numbers, letters and symbols.
Utilize Windows Authentication Mode
Windows Authentication SQL Server 2012/2014 has two authentication modes for approaching to databases: Windows Authentication mode and SQL Server Authentication.
Windows Authentication mode generates by default in the process of SQL Server installation and should always be turned on. Its security is stronger than SQL Server Authentication. With Windows Authentication mode, user credentials get confirmed by Windows and then trusted by SQL Server. The administrator of database can take measures, such as applying for a strong password, account lockout policy and password aging. The access to SQL Server can be administrated at the domain level, so there is no need to have unnecessary extra SQL Server accounts.
SQL Server Authentication administrates logins within the SQL Server, instead of basing on Windows. A strong and secure password is needed for every login. When comparing it with Windows Authentication, SQL Server Authentication has some deficiencies in security such as fewer password schemes, lack of Kerberos security protocol, and even divulgence of password when an application connects automatically. Even so, SQL Server Authentication is still needed when you use applications that call for the support for other operating systems apart from Windows.
Alter the TCP Port Number for Your SQL Server
SQL Server listens on a default TCP port number, which is recognized broadly and is the target of attackers. Hence, it is recommended to configure SQL Server to a port number which cannot be figured out easily. Besides, if you find the named instances listen on changing ports, you should appoint a specific port for them to restrict ports in firewall better.
Using SQL Server Configuration Manager is a simple way to alter the TCP port number. The following steps below will help you turn to a fresh static port.
1. Open the “SQL Server Configuration Manager”, and expand the “SQL Server Network Configuration”. Then choose and open the “Protocols for the MSSQL SERVER” instance in the below area that needs to be configured.
2. Double click “TCP/IP”, and you’ll see a dialog box of TCP/IP Properties appears on the screen, then click the “IP Addresses”. There is a list with IP1 at the beginning and IPA2 in the end.
3. Except for IPA2, leave out the values of “TCP Dynamic Ports” and “TCP Port” of all IPs. Then you can alter the TCP port number of IPA2 to any one you want to use. By the way, for each IP, you can put the identical port number. You don’t need put anything beside “TCP Dynamic Ports”. Click “Apply” and you can apply those changes.
4. Click “SQL Server Services” in the SQL Server Configuration Manager. Then you need to find the SQL Server instance that you have allocated before, and right click on it and click “Restart “.
Encrypt Valuable and Sensitive Data
If database covers data that are valuable and sensitive, such as credit card with essential information. To secure it, you’d better consider taking an encryption measure. SQL Server enables you to employ one or more mechanisms to encrypt.
Symmetric Keys
A symmetric key can be used for encryption and decryption as well. Compared with other mechanisms, it’s less secure. In spite of this, they are still highly recommended for encrypting sensitive data because of their high performance.
Asymmetric Keys
An asymmetric key comprises two keys: a private key and a public key. You may encrypt data with one of the two keys and decrypt it with the other. An asymmetric key is more secure than a symmetric key, but the former could take up more resources of server.
Certificates
Certificates are intended to secure SQL Server connections and do data encryption. You can get one by using SQL Server or order one from a reliable third-party market.
Transparent Data Encryption (TDE)
TDE conducts a database level encryption. Before data is written to the disk, TDE encrypts the whole database and decrypts it when it is viewed. As the whole course is disposed by the database engine, the only thing you need to do is to turn TDE on.
In fact, if you want to come up with an excellent encryption method for SQL Server database, you’d better have much knowledge about as many terms as possible, such as Database Master Key, Server Master Key and Extensive Key Management module, etc.
Audit Both the Failed and Successful Logins
In order to monitor the activities on your server, SQL Server audits and tracks log events. After you have turned on audit, you are enabled to make a choice among the three options under the “Login auditing”.
You’re suggested to audit “Both failed and successful logins” for security. Auditing login faults help you recognize baleful login attempts and take some actions to prevent possibly potential attacks. Auditing successful logins allow you to monitor the activities of your sensitive data. This can help you find undetected security problems raised by unconscious omit of an administrator’s login credentials.
It’s not difficult to enable checking for all logins. The following steps below will show you how to configure SQL Server.
1. Open the “SQL Server Management Studio” and right click “SQL Server” that you need to configure. Then select Properties.
2. On the left area of the “Server Properties”, you can see the “Select a Page”. Click “Security” to open it, and you will find the “Login auditing” section. Then choose “Both failed and successful logins” and click “OK”.
3. Reboot the SQL Server and activate the alteration.
