Best practice? (SQLCommand and Web Services)
-
Hi all, I have a database that is in a DMZ. To be able to access the DB easily I was planning to create a web service that does all the DB access so that it can be used via a WinForm app over the internet. My intention was to pass a SQLCommand to the WebService but apparently this is not Serializable so it doesn't work. I wanted to pass the SQLCommand to avoid having to put all the business knowledge in the webservice and to make it as dynamic as possible. What is the best practice for such a problem? All help is welcome ! Greetz, Gette
-
Hi all, I have a database that is in a DMZ. To be able to access the DB easily I was planning to create a web service that does all the DB access so that it can be used via a WinForm app over the internet. My intention was to pass a SQLCommand to the WebService but apparently this is not Serializable so it doesn't work. I wanted to pass the SQLCommand to avoid having to put all the business knowledge in the webservice and to make it as dynamic as possible. What is the best practice for such a problem? All help is welcome ! Greetz, Gette
Geert Verhoeven wrote:
What is the best practice for such a problem?
Certainly not passing, essentially, raw SQL Queries. You might as well just expose your SQL Server on the internet if you are going to do that - Not wize in my opinion. In my database there are stored procedures, these procedures are the only way into the database - I have shut off access to the tables directly to all but sysadmins and db_owners. The only way is via the stored procedure. The stored procedures know the business rules so they can veto anything daft that comes in that would potentially damage the database (either accidentally, or as part of a malicious attack). I would suggest that the datbase should have some business inteligence in order that it can protect itself.
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Geert Verhoeven wrote:
What is the best practice for such a problem?
Certainly not passing, essentially, raw SQL Queries. You might as well just expose your SQL Server on the internet if you are going to do that - Not wize in my opinion. In my database there are stored procedures, these procedures are the only way into the database - I have shut off access to the tables directly to all but sysadmins and db_owners. The only way is via the stored procedure. The stored procedures know the business rules so they can veto anything daft that comes in that would potentially damage the database (either accidentally, or as part of a malicious attack). I would suggest that the datbase should have some business inteligence in order that it can protect itself.
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
I agree partially with Colin. Allowing raw sql from the internet is effectively granting hackers full control to your database. But accessing data for your application through a narrow set of specially secured stored procedures isn't any better. I consider this a false sense of security. Enforcing security in your application by applying additional security requirements does not make your application secure. If anything, it's worse because the surface of your application is wider. Any mistake in security requirements and the hackers are in. There are stored-procedures that can execute command-line scripts, register linked databases with external IP addresses -- how do you convince your boss / client that you've hardened everything? Your application should be secure by design. The best practice is to expose your data as a service or "end-point". While you could use stored-procedures to access your data, but it means that the client application has to know these values. This might be a valid approach if you only have to worry about a single client such as web-application, but if you have a win-form application, third party, console script or any other service-oriented application then this isn't very portable. Ideally, your client-application shouldn't have any details about where the data comes from. This level of transparency is especially true for winforms applications. If you suddenly need to version or replace parts of your database you run the risk of compatibility problems with your client-applications, forcing you to redeploy a new version. If you don't control who installs your application, this means you have to version this complexity indefiniately! Ideally, if the client has no knowledge of the back-end implementation, you should be able to swap out or replace your back-end systems (ie, move from a database to a xml file) without breaking that dependency on the client side. Fundamentally, from a best-practice perspective, I see there are two approaches to exposing this data. 1) A "command" strategy. You create a single service that acts as a gateway into your database. You don't pass stored-procedures, you pass an object which represents a command. This still requires that your clients know the names or types of commands to execute, but it creates that transparency of the back-end that you need. 2) Data Access Layer as a Service. Create wrappers around all your stored-procedures to represent their line of business data functions. For example, a ProductGateway class woul
-
I agree partially with Colin. Allowing raw sql from the internet is effectively granting hackers full control to your database. But accessing data for your application through a narrow set of specially secured stored procedures isn't any better. I consider this a false sense of security. Enforcing security in your application by applying additional security requirements does not make your application secure. If anything, it's worse because the surface of your application is wider. Any mistake in security requirements and the hackers are in. There are stored-procedures that can execute command-line scripts, register linked databases with external IP addresses -- how do you convince your boss / client that you've hardened everything? Your application should be secure by design. The best practice is to expose your data as a service or "end-point". While you could use stored-procedures to access your data, but it means that the client application has to know these values. This might be a valid approach if you only have to worry about a single client such as web-application, but if you have a win-form application, third party, console script or any other service-oriented application then this isn't very portable. Ideally, your client-application shouldn't have any details about where the data comes from. This level of transparency is especially true for winforms applications. If you suddenly need to version or replace parts of your database you run the risk of compatibility problems with your client-applications, forcing you to redeploy a new version. If you don't control who installs your application, this means you have to version this complexity indefiniately! Ideally, if the client has no knowledge of the back-end implementation, you should be able to swap out or replace your back-end systems (ie, move from a database to a xml file) without breaking that dependency on the client side. Fundamentally, from a best-practice perspective, I see there are two approaches to exposing this data. 1) A "command" strategy. You create a single service that acts as a gateway into your database. You don't pass stored-procedures, you pass an object which represents a command. This still requires that your clients know the names or types of commands to execute, but it creates that transparency of the back-end that you need. 2) Data Access Layer as a Service. Create wrappers around all your stored-procedures to represent their line of business data functions. For example, a ProductGateway class woul
stupiddumbguy wrote:
I agree partially with Colin. Allowing raw sql from the internet is effectively granting hackers full control to your database. But accessing data for your application through a narrow set of specially secured stored procedures isn't any better. I consider this a false sense of security.
I never once said this was the be-all-and-end-all of SQL Security. It is only one part of defence against attack, and it was was a single part that was in context with the original question asked. I, perhaps, did not explain that the access to the stored procedures were through the web service. The web serive, as you explained can "act as a gateway into your database.".
stupiddumbguy wrote:
Enforcing security in your application by applying additional security requirements does not make your application secure. If anything, it's worse because the surface of your application is wider.
Either I am missing something or I am misinterpreting what you are saying here. How does locking off access to tables, and views, widen the attack surface of the application?
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
stupiddumbguy wrote:
I agree partially with Colin. Allowing raw sql from the internet is effectively granting hackers full control to your database. But accessing data for your application through a narrow set of specially secured stored procedures isn't any better. I consider this a false sense of security.
I never once said this was the be-all-and-end-all of SQL Security. It is only one part of defence against attack, and it was was a single part that was in context with the original question asked. I, perhaps, did not explain that the access to the stored procedures were through the web service. The web serive, as you explained can "act as a gateway into your database.".
stupiddumbguy wrote:
Enforcing security in your application by applying additional security requirements does not make your application secure. If anything, it's worse because the surface of your application is wider.
Either I am missing something or I am misinterpreting what you are saying here. How does locking off access to tables, and views, widen the attack surface of the application?
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Colin, you're absolutely right. This is only one of many different steps Geert should take to ensure he is able to expose his data as a web service without giving away the keys to the castle. I think what Geert was asking was to find the shortest route to expose the data, and your method is the most direct. Perhaps I misunderstood your comment, but to clarify, I'm going to assume the worst so that I can illustrate two different approaches and their impact on security. The first (what I assumed you meant), was to create a very generic web-service that allows Geert to call any stored-procedure of his willing. While there is some benefit that this is generic code that could be used in many different ways, this is also the biggest downfall of this approach. By allowing any stored procedure to be executed you can't predict which stored procedures will be executed. That's a pretty big hole which requires you to lock down every object in your database. My point was, that if you need to do this in order to make your application secure, then by design, this is not a secure design. We as modern application designers should strive to write secure code and not be dependant on the infrastructure configuration to save us. The second approach (my recommendation) was to abstract the details of the stored procedure away from the client application. Under this design, the web-service exposes some general input parameters but its implementation does the stored-procedure logic for you. This has a smaller security surface because you're not exposing the database directly to the caller.
-
Hi all, I have a database that is in a DMZ. To be able to access the DB easily I was planning to create a web service that does all the DB access so that it can be used via a WinForm app over the internet. My intention was to pass a SQLCommand to the WebService but apparently this is not Serializable so it doesn't work. I wanted to pass the SQLCommand to avoid having to put all the business knowledge in the webservice and to make it as dynamic as possible. What is the best practice for such a problem? All help is welcome ! Greetz, Gette
Why not use business objects at the webservices like DeKlarit (although you need to buy it), but some sort of ORM at the webservice, this will de-couple the database from the webservices to start with, then transfer datasets to and from the webservice. when you want something from the server call a function on the dataadapter interface (interfaces only required on client) let the web service fill the dataset then bind it at the client. viola g00fy