Moving to the cloud brings its own challenges and perceived perils. So, even if you are still wishy-washy about moving your SQL server to the cloud, you know it’s coming.  That’s why most SQL cloud adopters are finding a home at Amazon Web Services (AWS), which offers two options:  Amazon EC2 or Amazon RDS.  Amazon EC2 (Elastic Compute Cloud) is the fully installed SQL Server version most of us are accustomed to.  On the other hand, Amazon RDS (Relational Database Service) is a relational database-as-a-service that is fully managed by Amazon.

After all, security on the cloud isn’t really that different than securing your SQL database in the data center. You just need to make sure you take into account all the facets.

 

Securing SQL Server – On the Cloud and Off

One easy way to protect your SQL Server database is by using a dedicated server, whether local or on the cloud. This prevents security leaks and breaches by ensuring that only you and your team have access to the server.

You should set up your Microsoft SQL Server to use Secure Sockets Layer (SSL) to encrypt data. After all, it increases the security of data transmitted across networks between instances of SQL Server and applications.

Here is how to configure SSL for SQL Server:

  • Install a certificate in the Windows certificate store of the server computer.
  • Click Start, in the Microsoft SQL Server program group, point to Configuration Tools, and then click SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration, right-click the protocols for the server you want and then click Properties.
  • On the Certificate tab, configure the Database Engine to use the certificate.
  • On the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted.
  • When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.
  • When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.
  • Restart SQL Server after changing the ForceEncryption setting.

Protecting SQL Instances

Attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances.

Here’s how to hide them:

  • From the Start menu, choose All Programs, Microsoft SQL Server 2016, Configuration Tools, SQL Server Configuration Manager.
  • Expand the SQL Server 2016 Network Configuration node and select Protocols for the SQL Server instance to be configured.
  • Right-click Protocols for [Server\Instance Name] and choose Properties.
  • In the Hide Instance box on the Protocols for [Server\Instance Name] Properties page select Yes.
  • Click OK.
  • Restart the services for the change to take effect.

 

SQL Server Security Monitoring

Tools such as database activity monitoring can be a huge help in mapping database access from different sources (users, administrators, third-party contractors, applications, etc.). Then map any access to the database to get a better understanding of your cloud database security requirements.

Unfortunately, ongoing monitoring of security events can be challenging in cloud environments: while most IaaS vendors offer solid audit data on management operations, traffic logs and network visibility options are typically much more limited and may require additional efforts (e.g., third-party software) to overcome these limitations.

Compliance on the Cloud & Off

If you are on the cloud, compliance requires that you have a third-party security infrastructure on your database. Based on your jurisdiction, you may have different visibility requirements. As a result, let your provider do the work – ensure it has the appropriate certifications for your location and/or industry.

Ultimately, though, you are the one responsible for compliance. Therefore, you need to ensure that you:

  • Map regulated data
  • Ensure separation of duties
  • Manage and control access
  • Ensure full identity management, including provisioning and de-provisioning accounts
  • Encrypt data appropriately, according to “in motion” and “at rest”
  • Make sure you have a paper trail of ongoing security efforts both for the regulators and potentially for your insurance company when things go wrong.

Reinforcing Auditing

Auditing is scenario-specific. Balance the need for auditing with the overhead of generating additional data. In particular, one area that you must regularly audit is access, ensuring that only the appropriate authorized individuals have admin privileges.

SQL Server security auditing monitors and tracks activity to log files that can be viewed through Windows application logs or SQL Server Management Studio. SQL Server offers the following four security levels with regards to security:

  • None—Disables auditing (no events are logged)
  • Successful Logins Only—Audits all successful login attempts
  • Failed Logins Only—Audits all failed login attempts
  • Both Failed and Successful Logins—Audits all login attempts

The default mode is: Failed Logins Only. Set the auditing mode to be both failed and successful logins if you store highly sensitive data.

Configuring SQL Server Security Logs for Auditing

  • In SQL Server Management Studio, right-click on the desired SQL Server and then click Properties.
  • On the Security page under Login Auditing, select the desired auditing criteria option button, such as Both Failed and Successful Logins, and click OK.
  • Restart the SQL Server Database Engine and SQL Server Agent to make the auditing changes effective.
  • If it’s required, and only if it is required, enable C2 auditing or Common Criteria compliance by selecting the appropriate checkbox.

Configuring C2 Audits

  • Activate (in Query Analyzer or osql.exe) and run the following (Command example):
  • EXEC sp_configure ‘show advanced option’, ‘1’
  • go
  • RECONFIGURE
  • go
  • EXEC sp_configure ‘c2 audit mode’,’1′
  • go
  • RECONFIGURE

Permissions

A recent article in CSO Online summarizing a survey at BlackHat said that five items thwart hackers in their tracks. #1 was limiting access to systems and #4 was limiting unknown applications. Linking your existing Active Directory with your database permissions is one quick fix. You should implement an identity management solution that includes provisioning and de-provisioning of accounts, authentication mechanisms and RBAC.

Best Practices

Our friends at Microsoft are behind on creating their recommendations for best practices for SQL Server 2016 security, but here’s the link to the one from 2012.

Last But Not Least

Most critical to all of your security requirements is to know what you have, where it is, and who’s accessing it, where, and when. Without good old fashioned data discovery, no matter what you think you are doing, it won’t be enough. May the security be with you.