SQL Server Endpoints

Endpoints are essentially web services that expose database access over HTTP. Aside from architectural design decisions, these are useful is that your application development team does not have to have management studio access to SQL Server in order to begin development. The WSDL generated shows the parameters required for the endpoint.

There are a few security issues to keep in mind when exposing this functionality. First, if you are not connecting over a secure channel (SSL) then you need to use Integrated or NTLM authentication. Meaning your web application will have to impersonate a user or use the logged in user’s context in order to access the service. (Yes, that does mean enabling impersonation). If you are connecting over a secure channel, then you can use SQL Authentication (mixed mode). The second security consideration is to disable BATCHES. Batches basically allows pass-through SQL calls.

Before you can create an endpoint, you have to reserve an HTTP namespace (URL). This will ensure that you do not overwrite a URL already created in IIS or vice-versa. Create the namespace using the following command:
[cc lang=”sql”]
EXEC sp_reserve_http_namespace N’http://YourServerName:80/WebServices/’
[/cc]
Now you can create the endpoint.

[cc lang=”sql”]
IF EXISTS
(
SELECT name from sys.http_endpoints
WHERE name = ‘MyEndpoint’
)
BEGIN
DROP ENDPOINT MyEndpoint
END
GO

CREATE ENDPOINT MyEndpoint
AUTHORIZATION [domainyouruser] — with optional authorization for owner
STATE = STARTED — the state of the endpoint
— http or tcp
AS HTTP
(
path=’/WebServices/’, — the virtual path
AUTHENTICATION=(INTEGRATED), — type of authentication
PORTS=(CLEAR), — which ports (clear=all)
SITE =’YourServerName’ — site can be ‘*" or specific server
)
— type of protocol, in this case, SOAP for a webservice
FOR SOAP
(
— Create an address web method
WEBMETHOD ‘http://YourServerName/’.’GetAddress’
(
— define the webmethod(s) and fully qualified sproc
name=’YourDB.dbo.spGetAddress’,
SCHEMA = STANDARD
),
— Create an city web method
WEBMETHOD ‘http://YourServerName/’.’GetCity’
(
name=’YourDB.dbo.spGetCity’,
SCHEMA = STANDARD
),

WSDL = DEFAULT,
BATCHES=DISABLED, — disable this for security reasons
DATABASE=’YourDB’ — underlying database
)

use master
GRANT CONNECT ON ENDPOINT::MyEndpoint TO public
[/cc]

Returning the content can be a little tricky. You have to return the result as an object, then loop the object for the dataset:

[csharp]
private void button1_Click(object sender, EventArgs e)
{
YourServer.WebServices gc = new YourServer.WebServices();
gc.Credentials = System.Net.CredentialCache.DefaultCredentials;
DataSet ds = new DataSet();
object[] oCity = gc.GetCity(sZipCode);

foreach (object city in oCity)
{
if(city is DataSet) ds = (DataSet)city;
}

this.dataGridView1.DataSource = ds.Tables[0];
}
[/csharp]

3 comments
Bhaskar 04 May 2016 at 4:25 am

Hi Derek, I found very useful information here. But i have one question, if we don’t create the namespace, can’t we use the end point outside over network?

I have created an end point without registering the namespace, when hit the url from other network it is throwing “server DNS address could not be found” error. Could you please help in this.

Waiting for this since long time.

Thanks,
Bhaskar

Philippe moore 19 Aug 2010 at 7:55 am

Hi Derek, excellent article! I have a question: my sql server is on server_sql and my web server is on server_web in a dmz. What can I do to make the web service available to server_web sites. Thanks Philippe

Derek Dieter 30 Aug 2010 at 9:29 pm

Hi Phillippe,

Hmm. It’s tough to determine what obstacle you are running into. When you say make available, are you referring to trying to find a connection string? Or can your server’s not see each other? What are you trying to display?

Featured Articles

 Site Author

  • Thanks for visiting!
css.php