Shop Talk: 2021-08-30 – Shop Talk with TriPASS

The Recording

The Panelists

Kevin Feasel
Mala Mahadevan
Mike Chrestensen

Notes: Questions and Topics

Securing Application Accounts in SQL Server

@rednelo7625 had a follow-up from a prior e-mail and introduced a rather interesting question (or set of questions!):

Last week I mentioned in chat that our server was hacked and we were re-examining a lot of things. It appears that the hacker was only interested in defacing a site and spreading malware. But, it raised questions regarding what if the hacker was able to view confidential records in a db. While we’ve done all we know to prevent SQL injection, we have to ask if they were to launch a successful Sql injection attack, what could they see and how would we know it?
We had Sql auditing enabled but most of the results were not very helpful. At the time I set it up to pull everything without really thinking through what I needed to see. I watched the video with Mark Gordon which was very helpful. So, now we’re defining the audits to answer specific questions and automating email alerts if certain lookups occur on certain tables.
The user in the connection string for the application (AKA “WebConfig_user”) needs to connect select data from these tables. In the audit records, we see that user and what the application is selecting. But, it’s hard to know what is a legit query and what is not because of massive volume of data.
So, to help I created a SQL audit that looks to see if specific tables were accessed by anyone who is not the application’s connection string user. So, if a developer is working in SSMS, and views a table, we would catch that as well as any actor who might have gained access to one of the user SQL accounts. That part works fine. I can now determine if someone other than the WebConfig_user was poking around.
But, this still doesn’t completely solve the issue. Suppose a hacker was successfully able to gain access through the application’s connection string user? If I can’t determine through SQL auditing what that person as “WebConfig_user” was looking at, I’m still in the dark. Is there an answer with Sql Auditing I’m unaware of? I’m wondering if I should I just try to stop them another way –perhaps by taking more efforts at blocking their ability to find the tables to look at in the first place.”
This causes us some concern. I read somewhere that I could deny select on certain objects or schemas which would prevent this user from accessing this information and knowing which tables there are to look at.
First, I’m wondering if denying select on these is a good practice or if it would even work?
Second, if it is a good idea and would prevent a bad actor from viewing the table structures, would denying these break the application? We don’t specifically call these anywhere. But, I’m not sure if denying select on these would impact anything Sql server is may be running in the background. Does anybody know.
I guess it comes down to this. How do we best ensure that nobody can access anything beyond the intended use of the application? And what is the best way to determine if they did?

We had quite a bit of discussion about options here, but in brief:

Encrypt your connection strings. Here’s an example for encrypting connection strings in a web.config file.
Run DENY VIEW DEFINITION on a particular role or user who shouldn’t have rights to view system tables.
Use stored procedures as a way of limiting direct table access and sign those procedures using module signing. This is the best way of limiting access in SQL Server.

ScriptDom

Next up, we covered an article Mala wrote on ScriptDom. This is hopefully the first of many articles.

Thoughts on Exception Handling in T-SQL with Try/Catch

For the last 15 minutes or so, we talked about the implementation of try/catch style error handling in SQL Server. The short version is that it’s definitely not perfect, but does have value. I think there are enough places where error handling brings value, but fully agree with the sentiment that if you’re handling errors in your T-SQL statements and you do not fix the error in the script itself, make sure that you bubble up the original error so that your caller has a fighting chance of fixing the issue.