Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Web Service to talk to SQL server database using custom queries

Posted on 2014-02-14
7
Medium Priority
?
437 Views
Last Modified: 2014-03-05
Hi
I have client with SQL server database on their server. They don't want to open the SQL server database to "world" so I thought that I could talk to it I could have say a web service? (previously used VPN but that is no longer)
I don't want client to have all the queries I use (not for any "bad" reason, rather just that we've taken a long time to develop).
So, on client side a service with method say something like
function GetData(theQuery as string)
    do database stuff
    execute theQuery
    return result
end function
The problem with this might be SQL injection with variable theQuery ?  how to prevent?
I know could use stored procedures, but to do that again the client would know all the queries I use
Please advise
0
Comment
Question by:rwallacej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 800 total points
ID: 39858920
You could still use stored procedures but create them with the WITH ENCRYPTION option to encrypt/obfuscate the contents.
0
 

Author Comment

by:rwallacej
ID: 39859062
thanks Carl,
however, I don't think I'd be able to do this, the client is "owner" of database (although I have written queries to do the smart stuff), I don't think they'd let me add stored procedures to it
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39859081
In that case you would be safer to pass a parameterised query to your method, along with a collection of values, or name/value pairs.

So you could have a query like "select some_field(s) from some_table where some_field = ?", and then pass a value to your method as well.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 17

Accepted Solution

by:
Barry Cunney earned 1200 total points
ID: 39859194
Hi
In SQL Server 2005/2008 you can create a HTTP Endpoint which essentially exposes a Stored Procedure as a Web Service Method - the following is a partial sample


CREATE PROCEDURE Employees_Select
AS
SELECT EmployeeID, FirstName, LastName, BirthDate FROM Employees



CREATE ENDPOINT Employees_Select_EndPoint
STATE=STARTED
AS HTTP
(
   PATH = '/SQL/Employees_Select',
   AUTHENTICATION=(INTEGRATED),
   PORTS = (CLEAR)
)

FOR SOAP
(
   WEBMETHOD 'Employees_Select'
   (
      NAME='Northwind.dbo.Employees_Select',
      SCHEMA=STANDARD),
      WSDL=DEFAULT,
      DATABASE='Northwind'
   )


using localhost;
using System.Net;

private void Page_Load(object sender, EventArgs e)
{
   Employees_Select_EndPoint proxy =
      new Employees_Select_EndPoint();
   proxy.Credentials = CredentialCache.DefaultCredentials;
   object[] results=proxy.Employees_Select();
   DataSet ds=(DataSet)results[0];
   GridView1.DataSource = ds;
}

Open in new window



So the Webmethod can have a more general name and reduce how easy it would be for ahacker to guess the specific SQL to use for SQL injection
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 39859318
Further to my previous post, from a security point of view:
When setting up an HTTP endpoint, you will need to decide between Basic, Digest, Integrated (NTLM, Kerberos), and SQL Authentication

A connection firstly authenticates at the HTTP transport level. If successful, the user's SID is used to authenticate with SQL. The exception is SQL Auth. The SQL Auth credentials are sent as part of the SOAP packet using WsSecurity Username token headers. One can also restrict access to only specified IPs or ranges of IPs. Even if a stored procedure is mapped, it can only be executed if the user has CONNECT permissions on the endpoint as well as EXECUTE permissions on the stored procedure.

When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:

GRANT CONNECT ON HTTP ENDPOINT::Employees_Select_EndPoint TO [DOMAIN\USER]

To restrict access to an endpoint, the administrator can deny permission to the EVERYONE group, using the DENY CONNECT statement. Then, he can grant permission to specific individuals or roles, using the GRANT CONNECT statement.
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 39859575
Also HTTP endpoints can use SSL certificates to encrypt the communication going back and forth
0
 

Author Closing Comment

by:rwallacej
ID: 39906616
thanks for help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question