Better SQL Server Security - How to protect your database? RRS feed

  • Question

  • I have a database that was created for a custom application.  The application may be broken up into multiple tiers or not.  All interaction with the data is done using stored procedures.  My problem is that I want to constrain my end users to accessing and executing the stored procedures through the custom application and nothing else.  That means the user should not be able to go applications like Excel and make a connection to the database and execute one of the stored procedures.  And keep in mind my users must authenticate and be authorized in SQL Server using their Windows identity for security and auditing purposes.

    I have read posts on the Internet saying that Logon Triggers are a great way to prevent people from connecting to databases using “unapproved” software applications.  The application would specify a Application Name in its connection string (Application Name=MyCustomApplication).  A logon trigger can be written and the app_name can be evaluated and only allow the applications you ever wanted to connect to the SQL Server, thus eliminating Excel.  While this is all fine and good, this isn’t 100% foolproof.  Rouge EXEs could spoof an application name that is on the good list of EXEs and circumvent the logon trigger’s purpose.

    I have also read that using a loginless account is one way to prevent users from accessing a database directly using their Windows identity.  For example, let’s assume you have stored procedures written and you grant the loginless account rights to execute the procs.   As long as the EXECUTE AS ‘loginless account’ is executed prior to executing the procs, then this works pretty well.  The end users don’t have rights themselves to execute the procedures, but as the loginless  used for the impersonation does.  The problem I have with this approach is that you are requiring your application code to execute that EXECUTE AS statement before anything else is executed.  And because the EXECUTE AS is scoped for the session/module, each time the application were to execute a procedure, it would have to first call the EXECUTE AS and do so over the wire (problem if you aren’t using SSL).

    I also read about using signed procedures.  In this case a certificate user is granted access to the tables themselves.  And then the stored procedures are signed with the same certificate.  Then the end users are permissioned to the stored procedures.  But I don’t see how it is relevant because it seems this is addressing the issue of where ownership chaining is not possible.  The users would still have the ability to execute the procedures from any application they wanted to.

    So my problem is there is no good surefire way of allowing a Windows authenticated user to a database and only to the database if the user is using the application the database is intended for.  There just seems to be no good way to handle this.  There are of course things like Application Roles, but if you have spent any time with them, you know they are pretty much worthless in an enterprise class application.  It seems in SQL 2005, Microsoft started to make improvements towards handling this issue, but it is still appears to be half baked.

    Have you figured out something that I have yet to read about?  Are you the guy/girl that has the answer?  If you aren’t, have you thought about the possibility of users accessing your database other than using the application it was intended for?

    To me, it almost seems as though the ultimate solution to this problem is to sign the application code and then in SQL Server, per database, say what certificate is allowed into this database.  That could be the certificate of a user or a certificate of the application.  By doing the user and application would people like DBAs could still get to databases using any application and end users could access the databases only with the “approved” applications.  Thoughts?

    Monday, August 3, 2009 6:25 PM

All replies

  • There is one more solution you did not mention: terminal server. Put the application on a Terminal Server. The network is configured so that the Terminal Server box can reach the SQL Server machine, but the users' workstations cannot.

    With SQL 2008, it is possible to set up Terminal Server so that the users do not really experience that they log into a Terminal Server, but it seems that they log into the application. Don't ask me how, but I've seen a client who had done this. It was very slick!

    As for the idea of signing application code and store it SQL Server, I'm not sure that this can be done wholly safe, but I suggest that you put it on http://connect.microsoft.com/SqlServer/Feedback. But search around first! This a common problem, so someone is likely to have submitted this kind of idea before.

    SQL Server MVP
    Monday, August 3, 2009 8:10 PM
  • Thanks Erland for the reply.  You are right that TS is one way to go for desktop applications.  With server zoning you could put the DB servers into a zone and have an application zone where your terminal servers and application servers (middle tier, web, services, etc) live.  The DB zone would only allow servers from the application zone to access them.  The users would be in a zone too and they could only get to the application zone.

    But again, all of this seems to be using a sledgehammer to drive a pushpin into a corkboard.
    Monday, August 3, 2009 8:16 PM
  • I would suggest taking a look to existing threads in the forum where we have discussed similar problems. I am including two of links that I think will be relevant:



      Basically SQL Server does not provide a DRM solution, but you can limit access based on rules you define (i.e. via a LOGON TRIGGER), or via SP access (digitally signed modules). Be aware that some of these rules may be verified by SQL Server (i.e. sigantures or a logon trigger that allows connections only at certain times of the day) while other mechanisms provide only a speed bump and a deterrent for casual/accidental violators, but no protection against a determined adversary(i.e. a logon trigger that only allows connections where the application name is the expected one). 

      It is also important to relaize that if the SQL Server is under the control of your adversary (i.e. the adversary is a box administrator) all bets are off.

      I hope this information helps,
      -Raul Garcia
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 5, 2009 10:58 PM
  • Raul, thank you for your reply.  I actually did read one of the links you pointed out.  This seems to be an old age issue that people have.  And it’s funny that there is no decent way of solving it.  Let's face it, protecting data is no longer a topic of conversation just in the IT world.  It is now become a household topic too.  Therefore, IT professionals (developers and DBAs), software vendors, and the owners of the data have a duty to protect data.


    As far as your suggestions in your reply, they do not adequately solve the underlying issue.  Limiting access base on login triggers is not good enough.  All of the metadata available to you in a login trigger that is coming from the calling entity can be compromised one way or another.  Also, if you have a SQL instance that is supporting dozens of applications, that logon trigger or the multiple logon triggers becomes a kludged mess. 


    SPs that are signed and consequently mapped to a user is too late in the game.  That does not in any way prevent a user from connecting to a database and executing that stored procedure outside the confines of the “trusted” application.


    One thing that I don’t think a lot of people realize is that in today’s world, keeping people out of the data they have no business being in is not just the primary goal.  A just as important goal is auditing the privileged user’s access and use of the data.  And the only way to adequately do this is by using a single trusted authority that issues that individual user an identity.  And that identity is then used to grant rights to the resources it is allowed to access. 


    Now, in the SQL Server security we have today, this means that we have to permission a user’s Windows identity to a database and its objects either through direct permissioning or via group membership.  But the moment we do this, then the issue of a user accessing a database in ways other than the indented methods becomes prevalent.  And sure, there are ways to “limit” or to use obscurity for security, but there are better ways to prevent this, but the technology is going to have to change. 


    Developers are going to have to sign code.  The data providers (OLE DB, ODBC, .NET, etc) are going to have to change.  IT shops are going to have to issue and manage certificates.  SQL Server is going to have to change to handle recognizing certificates and verifying them with a certificate authority.  Once this is done, then a trust relationship can be created between the calling entity (person or application) and the data store that entity is attempting to access. 


    Until a solution like this or something better comes along, we are just going to have to understand the risks, accept them, and mitigate them as best as we can and hope for the best.

    Thursday, August 6, 2009 1:55 PM
  • I thought I would give everyone some information regarding this.  I submitted this "issue" to Microsoft Connect and this is the response I received.  I still argue that this doesn't meet the need I have.  If I want to have full auditing of a user's actions and I only want the user to use a certain application to access the database, there is no good way of doing it.  Each "solution" only gets so far and then falls flat.

    Thank you for your feedback. This is a problem that many customers have reported. The fundamental problem here is that there is no reliable way for SQL Server to guarantee that the application is actually the intended application. Ideally, we would want the OS security layer to be able to authenticate applications/processes as well as user principals but this is not currently possible. We will continue investigating this problem but it seems like using the loginless users may be your best approach for now. To comment on your statement that "because the EXECUTE AS is scoped for the session/module, each time the application were to execute a procedure, it would have to first call the EXECUTE AS", if you issue an EXECUTE AS "loginless user" as a separate statement after the connection, then the security context will switch to the "loginless user" for the remaining duration of the connection and there is no need to issue it each time you execute a procedure. So 1 strategy you can employ is:

    1 - create a login for each application user and
    2 - deny connect to each login but grant the necessary access to the database objects
    3 - create a login for the application
    4 - the application login would not have any permissions except to connect to the instance and impersonate all the application logins
    5 - the application connects to the instance using it's application login and then immediately EXECUTE AS the neessary login.

    I know this isn't a perfect solution but it has worked for some customers.
    Monday, August 10, 2009 2:32 PM
  • I'm sorry, but they are correct.  You can't enforce this.  Right now, there is only one company in the world who could enforce something like that and then only on a single platform.  That is Apple on their iPhone.  (Ignoring the fact that you can "jailbreak" an iPhone and bypass everything.)  I'm sure you wouldn't want software to suddenly follow the abysmal process that you have for iPhone apps.  Because the only way to do something like this is that you would have to have a single entity that every application is submitted to, that entity would be the only way of distributing an application, that entity would have to verify the identity of the developer submitting the application as well as approve the application itself (because you can create applications that will write other applications - thereby violating the digital identity principle).  Once approved that single entity would assign every application a unique code and that entity would be the only one allowed to sell software.

    The only way you can enforce what you are asking for is to outlaw every existing application and then have complete control of the hardware and software that anything new is running on.  An impossible task.

    Even if you digitally sign code using a certificate, you do know that you can crack a certificate.  Is it easy?  No.  But, EVERY encryption algorithm is breakable, it's actually one of the requirements of an encryption algorithm to prove how secure it really is.  Certificates CAN be duplicated and just because I digitally sign an application doesn't mean it is the application that I say it is.  I could tell you that my signed application paints pattens on your screen when in fact my application does something entirely different.  I can also sign dozens of applications using the same certificate.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, August 10, 2009 10:56 PM
  • Mike, thanks for your reply.  There is no perfect solution, I agree.  But I do think there are other ways to better this situation and I’m throwing out my thoughts and generate discussion about it. 
    Tuesday, August 11, 2009 1:31 PM
  • Is your application client or server based?  If it is server based, your best bet is probably to physically seperate the SQL Server from the end user's subnet and only allow trusted servers to share the network with the SQL server.  This would allow you to use user level SQL security without allowing access outside of the trusted servers, but this does not work if you are running a client app that needs to connect.  Also, if running on a trusted server, using a privileged service account and providing security in application may be cleaner.
    AJ Henderson
    Thursday, August 25, 2011 6:34 PM