Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Better security for SQL Server - how to protect your database Expand / Collapse
Author
Message
Posted Monday, August 3, 2009 12:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 207, Visits: 201
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?



Post #764295
Posted Tuesday, August 4, 2009 12:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 27, 2010 11:18 PM
Points: 43, Visits: 35
Hi
you can use impersonation at your application level connection string. and grant access on ALL DB objects for that windows user only. NO other windows authenticated user should have access to database.
Thanks
Post #764590
Posted Tuesday, August 4, 2009 7:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 207, Visits: 201
Rupashri, thanks for the reply. If I understand you correctly, this would yield a trusted sub-system model, which is not what we are after. We need a delegation model because at the end of the day, we need to be able to audit the databases that contain sensitive data. If domain\jdoe logs onto the network, accesses an application, which then executes a stored procedure, then we need to be able to follow domain\jdoe all of the way through. And the only way to do this effectively is to use a delegation model and permission domain\jdoe at the database level.


Post #764779
Posted Monday, August 31, 2009 3:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:35 PM
Points: 32, Visits: 58
I'm not sure I fully understand the question, but we do something similar (I think) to what you're asking.

The application knows the logon user (e.g. dmn\jdoe)
But then it connects to the database using a SQL account (e.g. MyDataWriter)
Then in the table(s), there is a field for UpdateUser, which the stored procedure fills with the actual user ID (dmn\jdoe)
Post #780309
Posted Monday, August 31, 2009 3:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 207, Visits: 201
Beverley (8/31/2009)
I'm not sure I fully understand the question, but we do something similar (I think) to what you're asking.

The application knows the logon user (e.g. dmn\jdoe)
But then it connects to the database using a SQL account (e.g. MyDataWriter)
Then in the table(s), there is a field for UpdateUser, which the stored procedure fills with the actual user ID (dmn\jdoe)


Beverley,
How does you application connect? Integrated or SQL?



Post #780314
Posted Monday, August 31, 2009 4:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:35 PM
Points: 32, Visits: 58
SQL. The username and password are stored within the application, so it logs on with that.

But in order for the user to get to the application in the first place, they have had to provide their own username and password, so the username is stored and passed to the various stored procedures.
Post #780331
Posted Tuesday, September 1, 2009 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 207, Visits: 201
Beverley,

So that is the problem right, the application is connecting to SQL server using a SQL account verses doing integrated security. Integrated security is more secure than a SQL account. And from an auditing standpoint, you want to see the user's true identity all of the way from point A (logging into the network) to point Z (selecting from a table). So, going back to my problem, if you do integrated security, then you open your DB up to users being able to connect to it outside of your application.



Post #780921
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse