Yet another question on Application security....

Question

Ok, i have read a few articles regarding application security and it sems to me the Microsoft just doesn't get it.

As others have posted, Vendor packages are not normally controlled by the IT staff... they do what they do.  When an application is used for mission critical data, it becomes important to lock down that data as best as it can be under the limits of that application.  Yet what is often lacking in a vendor package is thought about what opening up the database means.

We are using a Vendor package that does not allow for application security to be set.  In other words, it uses windows authentication to grant Database access.  As many others here have noted, this opens the database to manipulation via any other tool the user has at their disposal.

It seems that the general reply here is that we should get the vendor to change their package to allow for Application level security.  Good Luck! 

I am not a DBA (must be obvious at this point) and i do not know all the what's up and coming, but generally as a system support person, i must deal within the environment i am given.  Here's what I see as the environment I am working under, the problem and the preferred solution.

Application only allows windows authentication. 

The users must be allowed full access to the database in order for the application to perform updates as needed. 

Security to specific data by company is controlled within the application to determine what a specific user can change.

Granting security to a user ID allows them to access and change data at will through an alternate product (E.G. Enterprise manager, MS Access etc.) without regards to the security built in to the application.

SQL Server knows what ID is requesting data and knows what application is generating the request (I have seen screen prints of the list of who is logged into the database and right alongside the UserID is the Apllication Name).

If SQL server knows these two bits of info, why is it so difficult to allow SQL server to append the Application name onto the user ID and allow access to the DB only via this combination?  The permissions can be set up with some combination of UserID/Application name and no special security, no messing with special application passwords/aliases, no pleading with Vendors to make changes  and best of all.... satisfying SEC rules for locking down DBs.

Am i missing something?  Does anyone know how i can accomplish this simple task without stressing out the DBAs?

Thanks for all your help and creative ideas!

Rob

Answer 1

  We are aware of requests such as this one. This is deceptively simple problem, but the true nature of it makes it extremely complex and unfortunately we don’t have a true solution for it.

 

   I have seem similar solutions to the one you are proposing, and while it is relatively simple to implemented such solutions in SLQ Server, they provide little to no real security. The client application name you mention is under the control of the client, and the server does not attempt to validate it in any way as it is used only for information; purposes and it is trivial to modify the TDS packet that contains this information.

 

   The main pitfall for the problem is determining how the system can verify that the connection was established by the application it expected.

 

  Determining the user’s context is relatively simple as every different user has a different identity, and there are established mechanisms that allow the system to verify that the calling user is himself (login/password, SSAPI, the presence of some token, etc.), while each one of these mechanisms have its own set of rules and limitations, they work to differentiate one principal from another based on the principle that a user will probably not give away his identity to anyone who asks for it (phishing and social engineering attacks come in place here, but that is outside the scope of this conversation).

 

  On the other hand, how can an application do the same? Remember that the application itself may be (I would say should be) running on a remote machine, most likely untrusted machine, therefore the attacker has all the time and resources necessary to analyze the behavior of your application.

 

  Using a password or a token purely generated by the application itself is useless as the application itself is already in the hands of the potential attacker and you should consider that a determined attacker has the time, resources and patience to reverse engineer whatever “secret” the application provides.

 

  One possibility would be to have some external trusted entity to validate the client application and validate the service (SQL Server) and establish that both parties are truly talking to each other besides validating the user’s credentials. As far as I am aware (and from talking with some people here), the facilities we would require for this are not available on Windows or the AD at the time I am writing this. Even in the case we had such facilities available, it would most likely require to rewrite the application in order to take advantage of it.

 

  Another problem is the attacker’s ability to subvert the execution of the application, even without modifying the binary itself a knowledgeable attacker can modify the execution by using a debugger/kernel debugger, making it difficult to guarantee that the application will always behave as expected, even in the case I can validate the connection was originally established by the correct binary.

 

  A third problem would be the attacker can subvert the OS in his own box to “hijack” the TCP connections from the application, injecting arbitrary TDS packets.

 

  We have a new mechanism in SQL Server (purely SQL Server) that, while it is not a silver bullet in any way, it may help you to minimize the permissions you need to grant to individuals and start moving some of those permissions directly to the TSQL base applications: digital signatures.

 

  The main idea is to digitally sign the modules (SPs, functions, etc.), create a user mapped to the signing certificate and then grant the permissions on the resouces needed for the application (i.e. tables, access to other DBs, etc.) to the cert-mapped principal instead to each person who has access to execute the application. For example:

 

 CREATE TABLE [dbo].[MyApp_Data]( data char(10) )

go

 

CREATE PROC [dbo].[MyApp_MainFunction]

AS

  TRUNCATE TABLE [dbo].[MyApp_Data]

go

 

CREATE USER [dummyUser] WITHOUT LOGIN

go

 

-- EXECUTE on the app main function

-- is the only permission we want to grant

GRANT EXECUTE ON [dbo].[MyApp_MainFunction] TO [dummyUser]

go

 

-- Will fail as dummyUser cannot use [dbo].[MyApp_Data]

EXECUTE ('EXEC [dbo].[MyApp_MainFunction]') AS USER  = 'dummyUser'

 

-- As we don't want to grant permissions directly to

-- the app resources to the app user, we will use sigantures

CREATE CERTIFICATE [myApp_cert] ENCRYPTION BY PASSWORD = '50m3 p@zzw0rD!'

   WITH SUBJECT = 'myApp signing certificate'

go

 

-- Create the user based on the cert and grant permissions

CREATE USER [myApp_cert] FOR CERTIFICATE [myApp_cert]

go

GRANT ALTER ON [dbo].[MyApp_Data] TO [myApp_cert]

go

 

-- Sign the module

ADD SIGNATURE TO [dbo].[MyApp_MainFunction] BY CERTIFICATE [myApp_cert] WITH PASSWORD = '50m3 p@zzw0rD!'

go

 

-- Try again to run the app,

EXECUTE AS USER  = 'dummyUser'

go

-- it will succeed

EXEC [dbo].[MyApp_MainFunction]

go

-- But accessing the table directly will not

TRUNCATE TABLE [dbo].[MyApp_Data]

go

REVERT

go

 

  While the feature was designed to allow new ISVs to develop applications this way, a DBA can use the same mechanism to sign any existing module.

 

  Besides the BOL documentation, Laurentiu Cristofor has some really good articles regarding this feature in his blog (http://blogs.msdn.com/lcris/), and I have at least one relevant article as well in my own blog (http://blogs.msdn.com/raulga/).

 

  I hope this information is useful. We will appreciate any further questions and feedback.

 

  -Raul Garcia

   SDE/T

  SQL Server Engine

Answer 2

Raul, thank you for this detailed response.  I am not sure i can do you justice but your reply does leave me with some thoughts to comment on.

You made the statement, "I have seem similar solutions to the one you are proposing, and while it is relatively simple to implemented such solutions in SLQ Server, they provide little to no real security. "

I am a bit surprised at that because you also go into some depth explaining that there is quite a sophisticated mechanism in place for validating the user ID.  The point you missed is that the proposed solution does not in anyway remove that User ID security level from the current process, but simply appends the application name to the user in order to validate that the user is coming from an acceptable path.  Whether someone can fake the application name is less of a concern seeing that the user level security remains in place. 

I think the rest of your email seems to focus on application level security which i think we'd like to avoid, and would be able to, if a secondary check were possible via permissions defined as user/application Name.

I think that most businesses feel that the access to their applications and network is secure enough that we are not trying to stop an external hacker from getting at the data as much we are in trying to keep our own employees where they belong.  If an internal user is sophisticated enough and determined enough to hack into the data via emulating the application, we probably have larger concerns than permissions is going to handle.  We just need to keep Joe Trader in London from seeing data from Tony Trader in the USA via standard tools (MS Access/ Enterprise manager) installed on the standard corporate desktop.  They are not able to install their own packages as they are not administrators on their own PCs.

So in summary, we're not trying to stop the world, just want to keep our worker's noses where they belong.

Rob

 

Answer 3

  You are absolutely correct regarding my comment on the existing solutions, I apologize. I should have said “no additional security”, as the regular user authentication and authorization still takes place.

 

  From your description, I think that what you are really looking for is row level security (RLS). This means I can only see a subset of the table rows based on my current context and a predicate (i.e. Joe Trader can see his customers, but not see Tony Trader’s customers).

 

  This is a common request and we are well aware of it and we are actually working on this area. I cannot guarantee this feature will be available in any future release of SQL Server, but I want to point out that we are aware of this particular case.

 

  For now, I would recommend exploring the digital signature solution whenever it is possible, it is more restrictive than RLS in the sense that outside the application the user cannot see any information at all with ad-hoc queries, but it may be useful none the less. Let us know if you have any questions on how to use this new feature.

 

  Thanks a lot for the feedback and comments. We are always glad to hear from our customers and improve our products based on your feedback.

 

  -Raul Garcia

  SDE/T

  SQL Server Engine

Answer 4

In addition to what Raul said, relying on the application name parameter in the connection string is just security through obscurity.

If all you hope to achieve is to prevent Joe or Tony from accidently manipulating the database directly, what you suggest does satisfy that.

But if you want to prevent Joe or Tony from intentionally manipulating the database directly, relying on this proposed feature will not suffice. Scripts can be written that allow the user to specify the connection string and some applications that may already be installed on their machine may allow connection string manipulation as well.

I've worked with an application that checked for the application name in their stored proc before proceeding, effectively doing what you are suggesting, and it was not a secure method. It may prevent unintentional mistakes, but it will not stop someone intentionally trying to get access. You might as well just ask Joe and Tony to not use Enterprise Manager or MS Access, because if they really want to, what you are suggesting will not stop them.

That said, if an application wanted to do this today, SQL Server already exposes everything they need to implement what you are suggesting. They could deny SELECT/UPDATE to all users, GRANT EXECUTE to a set of stored procedures and check the application name in the stored procedures before doing anything.

So then the question becomes: is there enough value in baking this feature directly into the product? Since it doesn't prevent anyone with malicious intent, I'm skeptical that it mets the bar of belonging in SQL Server.

Jack Richins
SDE Sql Server

Answer 5

All good points.  I totally agree that using the application name is not securing the database.  It is simply obscuring it with an easily beatable hack.  That solution would embarass Microsoft in the developer community...

However I do want to back "robxyz" up on the priority...  I totally agree with his assessment.  If you are in a client/server environment the brunt of responsibility for keeping hackers out is on the network.  Power users are a real risk to the database.  Not even from a malicious intent (i.e. maybe the app goes down and they know enough to connect via MS Access and run a stored proc outside of the context of the app which may not achieve the desired result and may make matters worse).  I know MANY users that would do these kinds of things.  Having experience with apps in a banking environment, there is no way they would allow the use of NT Authentication for this situation.  They have hundreds of users that wouldn't have the foggiest idea about how to hack into the database, but many might be proficient with MS Access. 

Keep in mind that the User ID component would theoretically keep the hackers out and the Application part is only intended to prevent the use of non authorized tools by authorized users.  Therefore it wouldn't have to be as robust.  Maybe implement some secure key concept that the application can pass to the database along with the user ID.  Or register some identifier in the Application (GUID or something) that is also registered in the database.  Maybe it would only be supported for .NET 2.0 apps?

Anyhow, I cast a vote to move this issue up in priority.

Thanks so much for your feedback!

Answer 6

 I want to reassure you that here in Microsoft we are really taking our customers’ feedback in consideration. We really appreciate all your feedback and we are actively trying to find solutions for our customers’ problems and concerns.

 

  As you mentioned, you would accept a solution that is not “as robust” as other authorization/authentication mechanisms we already provide, but because SQL Server is a platform where our customers (including other Microsoft products) rely on, we cannot make the same decision on behalf of our customers.

 

  As I already mentioned in one of my previous posts, finding a general and robust solution for this problem is quite challenging, but that doesn’t mean we are giving up. As a step forward we introduced the module signature feature in SQL Server 2005. This feature allows a controlled escalation of privileges by allowing access to the resources only via the application instead of granting direct access to the resources to the application users. These signatures can be applied to both T-SQL based modules and CLR based modules, but the limitation we have with this solution is that the signed modules must reside in the database.

 

  If the available tools are not enough to solve your problem in a satisfactory way, I would like to encourage you to provide us with more details on your particular scenario. We will do our best to help you find a solution based on the current features, but your feedback may also help us to find a general and robust solution that would be appropriate for a subset of similar scenarios.

 

  I hope the information I have posted here will be useful. As always we appreciate your feedback.

 

-Raul Garcia

  SDE/T

  SQL Server Engine

Answer 7

This may be answered in another forum... apologies in advance if it is.  Here is an application security problem that I see at every dot-net company.

 

Situation:  Web-based application, RDBMS backend, many (100k’s) of application users.

 

Need:  Application-based user logins to the website, limited (a few per application) common logins to the SQL Server, yet have the ability to track application-user (AU) CRUD activities.

 

In our current environment, all user interaction is via stored procedures.  We strictly control the AU security and functionality from the web applications.  While only a handful of the users are “internal” with NT accounts, the applications may each have 10k’s of legitimate logins.

 

If we had a “database cookie” (or some other container of user context) that we could access within T-SQL, we could implement framework-controlled auditing of the CRUD operations.  This is different from requiring all developers to explicitly pass in the AU’s context on every SQL call (a development headache), and it eliminates having to make every AU a SQL user (a security nightmare).  This “database cookie” would be associated with the database connection, and allow the AU’s login (and any other context information stuffed into the “database cookie”) to be available to the stored procedures / triggers.

 

Even sweeter would be if .Net had an option to automatically sync the "database cookie" with the browser session variables, so the developers would only need to reference the “database cookie” upon creation / destruction.  This may also solve the issue of connection pooling.

 

When I first saw that SQL 2005 had cookies (for sp_setapprole), this was the functionality I assumed was finally being implemented.  However, it appears MS still thinks in terms of every user being an NT user....

 

 

Answer 8

You can use the SET CONTEXT_INFO and CONTEXT_INFO() statements to maintain 128 bytes of information at the connection level.  It is persisted between batches, as long as the connection remains open.  This would get you the cookie you're looking for.  If you can fit all your data into 128 bytes, that's great, but you'll need to handle the encode/decode yourself.  A better solution would probably be to have a table with a GUID key and columns for any info that you wanted to store.  Then generate a GUID per session and store it in the context info as a key.  You would  need to clean up stale sessions (so many minutes/hours/days/etc. old) at some point as well.  If you're opening and closing connections between accesses to the database, then this won't work.  Unfortunately, since that is how most apps are written, this may be of little use to you. 

Answer 9

robxyz,

 

You may want to try a free firewall that allows you to block SQL Server connections by app name, location and user name. Although not a silver bullet per the descriptions provided in this thread, it does prevent malicious users to get to the database using common tools such as Query Analyzer and MS Access for instance.

 

Again the tool is free to use. It's called Enzo Firewall (the standard edition). We created specifically for this purpose and are looking for feedback on how to improve it.

 

I hope this helps

 

 

Answer 10

I am curious why DDL Triggers were never mentioned here?

 

http://jmkehayias.blogspot.com/2008/06/securing-production-servers-from.html

 

Now I completely agree that you can't keep a dishonest man out.  But by the same token I also believe that locks keep honest men honest.  If you remove the temptation, that is generally enough, at least in my own personal experience.  I have found that people often do what they do, because it was easy for them to, like connecting with SSMS.  The average person isn't going to know how you stopped them from logging in, they just know that they couldn't do it.  It won't stop a DBA or a Developer who knows their stuff, or anyone who reads this thread now.

Answer 11

  Using logon triggers is OK as long as you understand their limitations and that the scenario that you are trying to protect against falls between such limitations. Remember that the application name is set by the client and there is no way to verify the authenticity of such claim (i.e. the client application can set its own name arbitrarily).

 

  If your only motivation is trying is to keep honest people from connecting directly to the DB by mistake, then this is probably good enough, but only as long as you don't fall for having a false sense of security. A user with the privileges to connect can easily workaround logon triggers that only check for application name in a matter of minutes using client libraries (i.e. .Net, ODBC, etc.) directly and making sure they set the application name set by the code meets the expected rules.

 

    -Raul Garcia

    SDE/T

    SQL Server Engine

 

Answer 12

Raul,

 

Jens shows how to spoof the app name in her blog which I link to in my entry, so I definately know that where there is a will, there is a way.  My own issue was not that people had access to the server, but that they had access to a higher level app service account, and would login to the app server as the service account, then connect from the app server as the app service account to SQL and have elevated permissions.  The logon trigger solved my particular problem, so I figured I would bring it up.

Answer 13

  You are correct, I know that you understand I missed the link in your blog. I know that you understand that this trick is not bullet proof (you mentioned it on your previous post), and I apologize for not making myself clear.

 

  I just wanted to make sure that there was no misunderstanding from anyone else reading this thread: logon triggers are not a silver bullet that can securely prevent a connection based on the application name.

 

  As you also mentioned, this trick will help you to deal with honest people and help them follow policies, but it heavily relies on the adversary not  knowing the trick and/or not being sufficiently motivated to override your policy (this is why I am personally against relying on security through obscurity).

 

  Thanks a lot, 

    -Raul Garcia

    SDE/T

    SQL Server Engine

 

Answer 14

Raul - If I am not mistaken filtering by application name can still be useful (not a silver bullet, but bronze may be warranted). Please let me debate this and please let me know if I am off-track. 

 

Blocking by application name using an opt-in approach doesn't work well for the reasons you mentioned. i.e. if you attempt to allow ALL applications except a few, that's where we have an issue - a custom application (a VB script even) can change its name and gain connectivity (as described in the link, within the link ).

 

Blocking by application name using an out-out approach should work much better. i.e. if you attempt to block all except a few, there is much more value. For example blocking SQL Query Analyzer from anywhere except when the user is 'network\administrator' works well - because short of altering the network packets to change Query Analyzer's APP NAME the DDL Trigger will work.

 

So I would tend to think that using DDL Triggers as Jonathan suggests (I really like that by the way) makes sense when taking an opt-out approach.

 

With this in mind, the only real downsides I see of the DDL Triggers are related to larger enterprise environments, that deal with regulatory requirements, such as SOX, PCI... including:

 

> There is no segregation of duty - The person in charge of the database is in charge of the DDL Triggers... like the fox in charge of the hen house - this is not something that network engineering can control for example

 

> It's code on the database - As such it has to be deployed and maintained on dozens (or hundreds) of SQL instances (for larger companies or hosting companies)

 

> It is not a universal solution - for companies that run SQL Server 2000 or earlier Service Packs of SQL Server 2005, this is not available - which implies a lack of uniformity from an enterprise policy standpoint

 

Outside of larger companies however, I would tend to say that DDL Triggers do have their place when used properly.

 

 

Answer 15

Herve Roggero wrote:

With this in mind, the only real downsides I see of the DDL Triggers are related to larger enterprise environments, that deal with regulatory requirements, such as SOX, PCI... including:   > There is no segregation of duty - The person in charge of the database is in charge of the DDL Triggers... like the fox in charge of the hen house - this is not something that network engineering can control for example   > It's code on the database - As such it has to be deployed and maintained on dozens (or hundreds) of SQL instances (for larger companies or hosting companies)

 

The company I work for is under scope for SOX as well as being PCI Level 1 certified.  This trigger has nothing to do with the requirements of either of these.  SOX is generally controls through audits.  Everything I do in a SOX scoped database is logged by an active 24x7x365 trace on the server.  Even if I did disable the DDL Trigger, if I do something in the databse, it is logged.  If I stop the trace, that triggers email alerts to people.  Can I get around it all, certainly, if I really cared to.  I am the DBA, there is always a loop hole I could exploit if I really wanted to, that is why our CIO has to sign off on certain risks.

 

As for the multiple server issue, a simple script can be done to add it, even in a hosting company.  If I had to build multiple instances consistently, I wouldn't be doing it from the GUI.  Even now I use a command line batch file to kick off the installation of new servers, and I don't do the installs that regularly.  I use the batch file to ensure that I do the same thing every single time on every single server.  I like having a cookie cutter base install.  To add the trigger would be another like making a sqlcmd call after the install completes.  Not to difficult overall.

 

Just my .02.

Answer 16

  Thanks a lot for the insight of SOX/PCI compliance; BTW,  A little bit off-topic, but we would really appreciate hearing your feedback on the new auditing infrastructure in SQL Server 2008 (we can start a new thread for this discussion).

 

  Going back to the discussion on logon triggers. I agree with Herve & Jonathan that the logon triggers are good tools (OK, let's give them the title of  bronze bullets ) and that they can help to make sure most people will respect the policy with some limitations.

 

   I also agree that an opt-out policy (all denied except for app x, y & z) is better and easier to manage than an opt-in policy, but the limitations of security through obscurity still exists (i.e. the system can prevent connections only as long as our adversary doesn't know the rule); the main reason for this limitation in application restrictions is that the policy is based on data completely under the control of the adversary.

 

  Policies such as "users can only establish a new connection between 7:00AM and 5:00PM, Mon-Fri" or "Only user A can connect 24x7" can be better enforced by the server because all the data that will be used to evaluate the policy can be verified by the server (i.e. it is not under the control of the user).

 

  I think it is OK to have logon triggers that prevent non-authorized applications to connect and assert to the user that such action goes against the policies (they probably know that already, but being assertive will most likely discourage them from keep trying, think of this as a "a small slap in their hand" to discourage such activities), but I would strongly recommend using such triggers along with auditing (as Jonathan has suggested). That way we give the auditor/DBA an opportunity to detect and try to prevent unauthorized attempts to connect as well as a mechanism to detect irregular activities inside the server.

 

  Thanks a lot,

    -Raul Garcia

    SDE/T

    SQL Server Engine

 

Answer 17

Jonathan Kehayias wrote:

The company I work for is under scope for SOX as well as being PCI Level 1 certified.  This trigger has nothing to do with the requirements of either of these.  SOX is generally controls through audits...

 

Right! I was replying in the context of the first post (from robxyz) since one of the items reads "satisfying SEC rules for locking down DBs"... and he was looking for ideas. So although I am not familiar with the SEC rules, I was only pointing out that indeed DDL Triggers are not meant for compliance purposes. So it looks like we are saying the same thing.

 

Jonathan Kehayias wrote:

As for the multiple server issue, a simple script can be done to add it, even in a hosting company.

 

Well... my point was that scripting can be prone to failure, and the larger the environment, the harder it gets. But I can't really disagree with you on this! May the Command Prompt prevail!

Answer 18

This doesn't really help the OP since he can't modify the app, but here's a solution that I dont think was mentioned yet. I remember recommending the following when the app was on sql server 2000 (probably would've gone with the certificates if it was in 05). The existing setup was a single 'dbo-like' permission app login/password that was hardcoded into the app and app driven user logins (username/pass/access_rights in table in db). The users couldn't hit the db directly unless they knew the app login pass that was hard coded into the .exe (I think this changed with every quarterly release)

I think the powers that be wanted single sign on but that was very insecure since it would allow users to hit the database with anything. This was at a gov't health dept & each hospital had its own db (the exe was the same for all, command line parameters for server\db - info available from help|about) & everything was on the same domain. So i could see a user being able to hit up the patient data of hospitals that they had no business looking up, esp if it's a celebrity/politician etc and there was some especially sensitive data in the drug & mental health dbs

the proposed solution was something like this
Create generic 'test_access' sp
add ad\user to db and grant execute on the test_access sp

have a 2 stage login within the app
stage 1 - trusted connection
exec test_access (limited access explicity to certain dbs, didn't exist with prev setup since user could change server/db command line params and 'guess' a valid user\pass)
successful execution meant that the user could access the app
close trusted connection
open 'app login' connection exactly the same as before but now we didn't need to verify the username\password of the user, just crosscheck the ad\username against that in the username table in the db. This meant that the fundamental app driven permission code didn't need to be modified, just the login part. This gave the impression of a single sign on while preventing ad-hoc access to the back end

I dont know how well this ended up working since I left soon after but i thought it always looked ok (in theory!)