SQL Server WCF Web Service

Since Microsoft SQL Server 2012, SOAP endpoints are deprecated features and developers should not use this feature due to separation of concerns and also security purposes. I believe that feature was reducing a lot of development effort. In this blog post I would like to introduce SQL Server Web Service which is developed to generate and provide Windows Communication Foundation Service based on the given database name.

SQL Server Web Service is external software that enhances SQL Server abilities in order to provide proper web service with minimal development and administration effort. This software generates the web service codes dynamically and creates the assembly on memory for flexibility purposes. We are specialized in SQL Server database development and administration, and we are able to develop software to extend the SQL Server ability. By this software every SQL Server databases can expose their stored procedures as web method and let third party software consume the data. Be aware that web service security is managed by SQL Server itself.

Installation

[info]Trial version can hosts up to two (2) web services at same time. [/info]

You may download the trial version installation files, unzip the file once you download it. It contains few DLL files and one EXE file as the following:-

  • ServiceProcess.exe: This is the WCF service launcher.
  • SQLServerWebService.dll: This is the DLL file to generate WCF service classes and methods dynamically on the memory.

Copy the files in a folder path that is secure and SQL Server service account is able to access.

Open SSMS, and then execute the following TSQL statements to create the SQL Server Assembly. Therefore you can access to the SQLServerWebService methods through TSQL.

[codesyntax lang=”tsql”]

use master;
go
exec sp_configure 'show advanced options','1';reconfigure with override;
exec sp_configure 'clr enabled','1';reconfigure with override;
go
Alter Database Master set trustworthy  on;
go
Create Assembly WebService from ''
with permission_set = unsafe;
go
create Proc Prepare
as external name [WebService].[ServiceEnvironment].[Prepare];
go
Create Proc SetupWebService(@server nvarchar(128), 
				@database nvarchar(128), 
				@servicename nvarchar(128),
				@serviceUrl nvarchar(255),
				@user nvarchar(128) = null) 
as external name [WebService].[ServiceEnvironment].[SetupWebService];
go
Create Proc RunService(@servicename nvarchar(255)) 
As External name [WebService].[ServiceEnvironment].[RunService];
go
Create Proc StopService (@servicename nvarchar(255),@force int = 0)
As External name [WebService].[ServiceEnvironment].[StopService];
go
Create Proc RemoveService (@servicename nvarchar(255))
As External name [WebService].[ServiceEnvironment].[RemoveService];
go
Create Proc GetServices
As External Name [WebService].[ServiceEnvironment].[GetServices];
go
Create Proc About
As External Name [WebService].[ServiceEnvironment].[About];
go
Create Proc UpdateAuthentication(@username nvarchar(200),@password nvarchar(200))
As External Name [WebService].[ServiceEnvironment].[UpdateAuthentication];
go
Create Proc ExportService (@servicename nvarchar(200),@filename nvarchar(255),@overwrite bit = 0 )
As External Name [WebService].[ServiceEnvironment].[ExportService];
go
Create Proc AddFilteredSP (@spname nvarchar(128),@serviceId bigint)
As External Name [WebService].[ServiceEnvironment].[AddFilteredStoredProc];
go
Create Proc RemoveFilteredSP (@spname nvarchar(128),@serviceId bigint)
As External Name [WebService].[ServiceEnvironment].[RemoveFilteredStoredProc];
go
Create Proc RebuildService (@servicename nvarchar(200),@user nvarchar(128) = null)
As External Name [WebService].[ServiceEnvironment].[RebuildService];
go
Create Proc RestartService (@servicename nvarchar(200))
As External Name [WebService].[ServiceEnvironment].[RestartService];
go
exec dbo.Prepare;
go

[/codesyntax]

Once above TSQL statements are ran, the assembly creates four tables on the database as the following:-

  • ServiceAssemblies: Stores the WCF service metadata and binary content.
  • ServiceConfiguration: Stores the WCF service launcher configuration such as Authentication.
  • ServiceFilteredMethods; Stores the WCF service filtered web methods.
  • ServiceLog: Keeps the WCF service error logs.

The following stored procedures are created once the above TSQ statements are ran:-

  • Prepare: It prepares the environment to host the web services, this stored procedure is called automatically during installation process.
  • SetupWebService: It generates the web service. It accepts few parameters such as @Server (the target database server name), @Database (the target database name), @ServiceName (the web service name, it is unique) and @ServiceURL (the web service URL and it is unique as well).
  • RunService: It loads and runs the web service. It accepts a parameter as @ServiceName.
  • StopService: It stops and un-load the web service. It provides two parameters as @ServiceName and @Force. In some cases the web service is not running but it is hosted by SQL Server Web Service engine, then administrators can use @Force=1 parameter to un-host the web service.
  • RemoveService: It removes the specific web service meta data from the environment. It provides @ServiceName parameter.
  • UpdateAuthentication: It updates the web service host authentication.
  • GetServices: It returns all running web service name and process id.
  • About: Returns the copyright and company information.
  • ExportService: It exports the web service contract classes into a DLL file. It provides the @servicename, @filename and @overwrite parameters.
  • RestartService: It restarts the web service. It provides @servicename parameter.
  • RebuildService: It re-generate the web service class and web methods. It provides @servicename parameter. Note: the service must setup first.
  • AddFilterSP: It filters the specific stored procedure from being the web method. It provides @spname and @servicename parameters.
  • RemoveFilterSP: It removes the filtered stored procedure from not being the web method. it provides @spname and @servicename parameters.

How It Works

sqlserverwebservice architecture

SQL Server Web Service, analyzes the user database and generates the WCF service classes in memory and compile it as DLL, then store it into the ServiceAssemblies table. Once the service required to be hosted, the SQL Server Web Service runs the ServiceProcess.exe to launch the WCF service on the given URL and port number.

Run a WCF Service!

I would like to use AdvantureWorks database to generate WCF service. The following statement setups the WCF service:-

[codesyntax lang=”tsql”]

exec SetupWebService @servicename = ‘AdvWorks’,
@server=’Fard-Solutions’,
@database=’AdventureWorks2014′,
@ServiceURL=’net.tcp://localhost:9998/AdvWorks’;

[/codesyntax]

Once the service is generated, a similar message appears on the Message tab.

wcf1

To verify the WCF service creation, I would like to take a look at the ServiceAssemblies table.

Use RunWebService stored procedure to run and host the WCF service, the following statements does that:-

[codesyntax lang=”tsql”]

exec dbo.RunService @servicename = ‘AdvWorks’;

[/codesyntax]

To verify the WCF service, whether it is currently running, you may use GetServices stored procedure as following statement:-

[codesyntax lang=”tsql”]
exec dbo.GetServices;

[/codesyntax]

GetServices stored procedures returns the list of currently running WCF Services and the related ProcessID.

wcf3

Once the WCF service is launched, you may verify the WCF service launcher by retrieving ServiceLog table records. The following resultset shows that ‘AdvWorks’ WCF service is launched and opened.

wcf5

Now, it is time to try to connect to WCF service through Visual Studio, the following figure illustrates the Web Service Reference window in VS which is already connected to recent generated and hosted WCF service.

wcf6

As above figure shows, all the stored procedures in the AdventureWorks2014 database have been generated as web method. You may use AddFilterSP to filter stored procedures to be generated as web method.

Features provided by SQL Service Web Service:

  • Easy installation steps on SQL Server instances.
  • Easy administration steps for pure DBAs.
  • Transport channel and data is encrypted and signed in the generated web service.
  • The user authentication and authorization is managed by SQL Server.
  • It is able to generate web method for every stored procedure in the target database.
  • It is able to host multiple web services at same time.
  • It is able to export the web service assembly as DLL file.
  • It is able to filter specific stored procedures.
  • It is able to rebuild and restart every web service easily when the web service is published.
  • It is able to distribute all modified and new data in every specific table automatically.(New) (Only in Full Version)
  • It can generate web methods base on specific user privileges. (New) (Only in Full Version)
  • It uses Net.TCP protocol to increase the web service performance and security. (New)
  • All data transferred through network is compressed. (New) (Only in Full Version)

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan