Home > Uncategorized > Grumbles about web service endpoints in SQL 2005

Grumbles about web service endpoints in SQL 2005

I was interested in trying out the new feature in SQL 2005, whereby it can host web services directly within the database engine, rather than via IIS.

So following a few online guides I created a simple stored procedure

create procedure FindPub
 @city varchar(100)
as
select * from pubs where city=@city

Then mapped this to an endpoint with the following code:

CREATE ENDPOINT SQLEP_FindPub
    STATE = STARTED
AS HTTP
(
    PATH = ‘/FinfPub’,
    AUTHENTICATION = (BASIC),
    PORTS = (CLEAR),
    SITE = ‘listofpubs’
)
FOR SOAP
(
    WEBMETHOD ‘FindPub’
        (NAME=’dinfo.dbo.FindPub’),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = ‘dinfo’,
    NAMESPACE = ‘http://www.listofPubs.info/FindPub’
)

On running this code – I got an error saying the "CREATE ENDPOINT’ statment is not supported in this edition of SQL server. (note the typo in the error message! :)). Fair enough, it’s a free product. But what really struck me was that it forced me to enter an Authentication option. To me this is counter intuitive for a web service. – I have to add the caveat here that I’m no expert in SQL server webserices, so there may be a way around this.

Forcing the user to provide authentication seems logical, as in, the web service needs to run under some credentials so that it can authenticate itself against the database, but this should be coded into the webservice, not provided by the caller. After all, in order to supply Windows authentication (NTLM) over the internet, We’d need a VPN or similar. Then if we were to use Basic authentication, this is too insecure, it would need to be provided over SSL.

Therefore in order to supply a web service directly from SQL server to the general public over the Internet, you’d need (a) to get a SSL certificate and install it (b) create a new windows login, since, you don’t want mr Joe public logging in under Administrator rights (c) map the new windows login to a sql login, (d) Include the login details in a public place so people can find it easily. – This seems much more difficult then hammering out an equivalent stored procedure in Visual Studio, with hard-coded credentials.

To me, the ethos behind web services, was that they were designed to be publicly viewable and useable resources, not coveted entry points to fulll control of a company’s data.

Anyway, enough grumbling. I just realized this morning that my website www.openmerchantaccount.info just got a PR5 in google. Unfortunately it hasn’t yet re-positioned itself in google yet, I guess I have to wait for the next index shuffle. It’s got over 1,000 backlinks from my own personal ring of data-heavy websites, of which I just launched two more, www.listofhauliers.info and www.listofcardealers.info – I’m quite an expert at SEO !.

 

 

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: