We just announced exciting new support for native SQL monitoring within One Identity Safeguard for Privileged Sessions. Getting to this point was quite a journey. I want to share some of the learning experiences I gained while traveling from a starting point that was a cloudy idea of SQL-connection monitoring to a solution with a defined scope.
When we start any complex feature development, we will do several user interviews before a developer hits any keys on their keyboard. We already knew that our customers wanted to monitor their SQL connections. As this issue can be solved in many ways, the interviews helped us to better understand our customers’ challenges and to define the minimum feature set that would be of value to them. Frequently during the interviews, the issues that customers brought up surprised me. I learned that my ideas about what their challenges would be wasn’t completely accurate. Below is a list of critical issues we identified from our customer input sessions:
- Shared accounts: The credentials with the highest privileges to the database (for instance, the 'sa' user) is used by multiple administrators. Even worse, in application-to-application use cases, such high-privileged accounts, these accounts are often used to access and modify databases.
- Shared passwords: The username and the password are shared. There is no individual accountability, and it is nearly impossible to determine who accessed the database. To do so under these circumstances requires an intense – and expensive - investigative effort.
- No multi-factor authentication: Once you know the password, you're in. No one-time-passwords, no references to a valid support ticket number implemented whatsoever.
- Lack of access control: High-privileged accounts can access anything in the SQL environment. The rationale behind these type of accounts is that if something goes wrong, you still have the account that can access and fix everything.
- Control is more important than audit: Applying central control on the access to prevent unauthorized or impersonated access to databases is more important than providing rich audit information.
It's 2020 and organizations still use the 'sa' user account with the password written on a post-it note that is pasted on a monitor, surprising, huh?
After these revelations, it was clear what we needed to do. We needed a SQL proxy that enforced users to perform the gateway authentication (for instance, with their Active Directory accounts) before they can get to the database server. The proxy would optionally perform the multi-factor authentication; check the group membership to determine if the user is in the correct group to grant access to the target server. Only after these prerequisites are fulfilled can they check-out the 'sa' password from the credential vault and inject it into the session.
Luckily, most clients enable the customization of the content of the login field like this and even thought the string in the login field: "gu=mike%ticket_id=BU234K4%sa%10.110.255.120" looks weird it contains everything we need:
- gu=mike: This is the username for the gateway authentication. Based on the configuration, this can be an AD/LDAP username. The group membership of the user is also determined during the logon phase.
- ticket_id=BU234K4: A wide repository of plugins are available on our github.com page to achieve vendor-supported integrations with ticketing systems or multi-factor authentication providers.
- sa: This specifies the username used on the SQL server. If the settings and the information provided by users allows it, the password of this user will be checked out from a credential vault and injected into the session. Again, you can find plugins for vendor-supported third-party password-vault integrations on our github.com page.
- 10.110.255.120: This is the target database server to which we want to connect.
- The password field contains the password of the username used during the gateway authentication.
After the successful authentication and authorization phase, the session recording starts and the executed SQL statements are stored as commands for the sake of searchability and analysis. Furthermore, SQL servers are protected against harmful SQL statements as the statements are identified against a predefined list in real time, and if harmful, the connection is terminated before commands can reach the server.
Watch this video to see how you can protect Mike from accessing sensitive data, even if he claimed high privileges by using the 'sa' account on the SQL server.
The first release our SQL proxy is compatible with MSSQL, Azure SQL servers and various client programs (including Toad). The user experience is transparent as the user can use the same client, only the content of the login field needs to be customized, which makes it easy to use and to integrate into an existing environment.
We are excited about the positive feedback we have received so far and are looking to decide where to take this feature next. We’re not done yet. In fact, we’ve only begun. So if you have any comments about difficulties you have to monitoring database access, don't hesitate to leave a comment below. Learn how One Identity Safeguard for Privileged Sessions can help protect your environment in this on-demand webinar: Eight Ways to Analyze Privileged Sessions to Identify Your Most Suspicious Activity.