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 […]