Maintaining a secure environment is very hard. There are so many threats that can be exploited that it demands a specialized security team to continuously evaluate, monitor, and audit the many known and unknown threats. SQL Server is just another process that can be exploited and needs to be monitored. Still, since the database’s nature is to store information, including sensitive information, it is one of the main targets chosen by attackers.

In this article, I would like to show you a technique that can be used to reveal information that a user is not supposed to see and how to protect it. The technique is very simple, and it relies on SQL Server error messages to reveal information that could be stolen by adversaries. This technique has been used for several years in SQL Injection attacks, and many DBAs still overlook it.

The idea is simple; a non-privileged user can write a query referencing a SQL Server view that causes an exception, such as invalid conversion to be thrown during query processing if certain row values exist in the underlying tables. Depending on the query plan, these exceptions may bypass SQL Server permission validations and are thrown even if existing data cannot be retrieved through the view.

Before I move forward with recommendations, let’s understand the problem starting by looking at a simple example.

Note: I’m running all tests on Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) – 14.0.3294.2 (X64) . Other versions may have different results.

A simple view-based row-level security

Before SQL Server 2016 and row-level-security, it was very difficult to implement restrictions on data at the row level. One of the most common solutions for this requirement is to use a view with a predicate filter used to reveal only the information a user has access.

For instance, consider the following scenario:

Create two user accounts that will demonstrate different access capabilities.

Create a table to hold test data.

Populate the table with four rows of data.

A row-level security view controls which rows each user can see. For instance, the Manager user will be able to see all rows, while any other user will only see rows where HideSalary is equal to 0.

Grant read access on the view to users.

Now, if User1 tries to access the data, it will return the following:

User Manager will have access to all rows:

The problem with this approach is that any user with read access to the view can write a carefully crafted query that uses an expression executed in a specific order by the query optimizer. This causes an information leak through the use of the exception error message.

For instance, if a query that attempts to convert the Employee column to an Integer, it returns the following message:

Bob is a name that User1 could already access, so there is no “leaked information” here. But what about a query that ignores employees Bob and Jonh that User1 can already access?

User1 shouldn’t be able to see Mark’s row, right? How can User1 see Marks salary information? Easy, just change the query to convert the salary column.

As you can see, even though there is a security predicate in place to prevent a malicious user from directly querying other people’s salary, User1 was able to determine the data by running a query that returns a SQL Server error message to leak the data.

Note: To leak the correct information, you need to make sure that the query processor is evaluating the expression in the correct order. Otherwise, it would first try to convert the data in the row the user already has access to and not leak the desired data. The query optimizer will have to create a plan that is pushing the predicate down to the table access. Look at the execution plan of the query to confirm the expression evaluation order. If necessary, you may need to force a short-circuit using a CASE expression.

Row-level security in SQL Server 2016 was introduced to address this problem. Well, kind of. Considering the same scenario, you would need to do the following:

The first step is to create a function with the predicate that will be used in a security policy.

Create a security policy on table TabSalary.

Grant access to table TabSalary to users.

Just like when using the view, User1 only has access to employees “Bob” and “Jonh”.

The user Manager can see all rows.

Here is the main difference between the view-based solution and the row-level security feature. If User1 tries to run the implicit conversion query, the query will return the following error message:

As you can see, the data is masked. Because the query failed, the attacker can still infer that there are more rows in the underlying data. They can even write a query to identify the salary value, but it will be more difficult, and the data will never be leaked to the user screen. A query to infer the salary would be something like:

As you can see, a user could write a query in a loop to test a range of values and infer that a value exists. It is limited information, but in some scenarios, this could be a security problem.

Some recommendations to minimize the risk:

• Consider using the row-level security feature to minimize the information exposed to the attacker.
• View-based level security should not be used as an isolated measure to fully secure sensitive data from users running ad-hoc queries on the database. It is appropriate for preventing sensitive data exposure but will not protect against malicious users trying to infer or reveal the underlying data.
• Any SQL Server or associated application providing too much information in error messages on the screen or printout risks compromising the data and security of the system. The structure and content of error messages need to be carefully considered by the organization and development team.
  • Databases can inadvertently provide a wealth of information to an attacker through improperly handled error messages. In addition to sensitive business or personal information, database errors can provide hostnames, IP addresses, user names, and other system information not required for end-user troubleshooting but very useful to someone targeting the system.
• Detailed error messages must be visible only to those who are authorized to view them. General users must receive only generalized acknowledgments that errors have occurred. These generalized messages must appear only when relevant to the user’s task.
• Configure audit logging, tracing or custom code in the database or application to record detailed error messages generated by SQL Server for review by authorized personnel.
• Consider enabling trace flag 3625 to mask certain system-level error information returned to non-administrative users.
• Inspect application source code, which will require collaboration with the application developers. It is recognized that, in many cases, the database administrator (DBA) is organizationally separate from the application developers and may have limited, if any, access to source code. Nevertheless, protections of this type are so important to the secure operation of databases that they must not be ignored. At a minimum, the DBA must attempt to obtain assurances from the development organization that this issue has been addressed and must document what has been discovered.

Final thoughts

It is important to understand that this approach to secure data using row level views is not very safe. Any arbitrary T-SQL and access to all views (including system views) should be limited to only specific users.

One other thing I like to do in my environments is to get rid of any permissions granted to the “public” role. You know, by default, SQL Server system objects have permissions granted to public. That means any login will automatically have access to those tables. But do they really need it? I don’t think so. Of course, you’ll have to test it to confirm, but many applications never use system objects. You may want to remove public access to all system objects. Following is a link with a Microsoft article that will help you with that: https://techcommunity.microsoft.com/t5/sql-server/remove-public-and-guest-permissions/ba-p/383594

Sensitive environments require special attention to many things, including application users that may look harmless. Remember, most attacks come from inside; that means any user that already has access to the system should be limited to do only what is intended to do and nothing else. Deny everything and grant access as you go (developers will hate me for that 😊).

It would be nice if there were an option on SQL Server to limit the information returned in an error message. In my opinion, this is the primary security issue here which has been exploited for many years with SQL Injection. Something like TF3625 “Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages using ‘******’. This can help prevent disclosure of sensitive information.”.

Am I too cautious? What do you think?