Home > Technical > Accessing Web services using SQL CLR

Accessing Web services using SQL CLR

Problem : wanted to trigger event from SQL server to windows service, rather windows service is looking for a data changed as we always do.

Solution : After doing R&D, proposed following solution, which is still having one problem.  This i am writing because, i couldn’t find any complete guide to do this on the internet. just part by part explaining articles were there. Here i am going to combine all those and create complete guide.

Step 1 : Create windows service with REST based web service. (Not going to explain REST services here and hope you are familiar with REST)

Step 2 : You should have running REST service in your machine. As a example i will get the service URL as http://localhost:8090/Sync/NotifyNow/ 

Now just entering this URL on the browser, you should be able to invoke service’s method.

Step 3 : Create  following class, This will handle call from SQL server and pass to windows service. simply call the URL of REST service

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;

public partial class NotificationClr
{
[SqlProcedure]
public static void InvokeNotificationService(SqlString webUrl, out SqlString outPut)
{
string commandResult = string.Empty;

try
{
HttpWebRequest wr = WebRequest.Create(new Uri(webUrl.ToString())) as HttpWebRequest;
wr.Method = “GET”;
WebResponse response = wr.GetResponse();

commandResult = ((HttpWebResponse)(response)).StatusCode.ToString();
}
catch (Exception ex)
{
commandResult = ex.Message;
}

outPut = commandResult;
}
};

STEP 4 : Now we need to generate library from this and register on SQL server.

csc /t:library NotificationClr.cs

If you consider only SAFE permission for the assembly, then above created DLL will be ok. but if you need External_Access or UNSAFE access, then you need to SIGN the dll (Which is explained here http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx)

So once you have decided that your assembly needs to be enabled for “External_Access” or “Unsafe”, you have two options. Either set Trustworthy Property of a database to ON or to sign your assembly with a certificate or Asymmetric key . The easiest way is to set the Trustworthy Property of a database to ON, but it’s not good from security prospective. The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. But once you set it to ON , it might open up doors for malicious program. So the best way is to sign your assembly with a certificate or asymmetric key.

Then place the created DLL and  Key file together when you are doing below step.

STEP 5: Register this library on SQL server. here if we use SAFE permission(refer http://msdn.microsoft.com/en-us/library/ms345101.aspx), we don’t have to do much. but in our case we should have web access, which is not coming under SAFE permission.

So all the things we need to do for this i have created as one SQl query, which will create all the necessary things as well as it will handle already existing objects too.

–Important —– Change the DB name and DLL path before execute————–

–enable clr
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO

sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

PRINT (‘————–End enable clr————–‘)

PRINT (‘————–start deleting old notification clr if any————–‘)

USE DbName

GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’NotificationClrLogin’)
DROP USER [NotificationClrLogin]
GO

GO
IF object_id(‘InvokeNotification’) IS NOT NULL
DROP PROCEDURE [InvokeNotification]
GO

GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = ‘NotificationClrAssembly’)
DROP ASSEMBLY NotificationClrAssembly;
GO

GO
IF object_id(‘InvokeNotification’) IS NOT NULL
DROP PROCEDURE [InvokeNotification]
GO

———————————————————————————

USE [master]

GO
If Exists (SELECT loginname FROM master.dbo.syslogins WHERE name = N’NotificationClrLogin’ AND dbname = ‘master’)
DROP LOGIN [NotificationClrLogin]
GO

GO
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = ‘NotificationDllKey’)
DROP ASYMMETRIC KEY NotificationDllKey
GO

PRINT (‘————–end deleting old notification clr if any————–‘)

———————————————————————————————-

USE [master]

GO
CREATE ASYMMETRIC KEY NotificationDllKey
FROM EXECUTABLE FILE = ‘D:\SQL\CLR\NotificationClr.dll’
GO

PRINT (‘—————NotificationDllKey done————–‘)

GO
CREATE LOGIN NotificationClrLogin FROM ASYMMETRIC KEY NotificationDllKey
GO

PRINT (‘—————NotificationClrLogin done————–‘)

GO
GRANT EXTERNAL ACCESS ASSEMBLY TO NotificationClrLogin
–GRANT UNSAFE ASSEMBLY TO NotificationClrLogin
GO

PRINT (‘—————GRANT EXTERNAL ACCESS done————–‘)

———————————————————————————————-
USE DbName

GO
CREATE USER NotificationClrLogin FOR LOGIN NotificationClrLogin
GO

PRINT (‘—————CREATE USER NotificationClrLogin done————–‘)

GO
CREATE ASSEMBLY NotificationClrAssembly
FROM ‘D:\SQL\CLR\NotificationClr.dll’
–WITH PERMISSION_SET = UNSAFE
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

PRINT (‘—————NotificationClrAssembly done————–‘)

GO
CREATE PROCEDURE [dbo].[InvokeNotification]
@weburl [nvarchar](4000),
@returnval [nvarchar](2000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [NotificationClrAssembly].[NotificationClr].[InvokeNotificationService]
GO

PRINT (‘—————CREATE PROCEDURE done————–‘)

PRINT (‘—————-End of all commands—————‘)

—-Call WS
–GO
–Declare @Response NVARCHAR(2000)
–EXECUTE InvokeNotification ‘http://localhost:8090/Sync/NotifyNow’,
— @Response OUT
–SELECT
— @Response
–GO

Now you can call this SP in any place inside SQL server. Then it will call method on the DLL, that method will call REST service URL (here i am passing URL from SQL, so i don’t need to change the dll time to time)

Problem i am still having is, if web service(REST service) and SQL server are on same machine, then it works fine (so URL is localhost). if i tried to call remote web service (or website), then it works 2 times, JUST 2 TIMES. from third call, it starts to time out.

It starts to work again after i restarted sql server. (just 2 calls only). But in the same machine, this works perfectly.

I used following resources to complete this task

1. http://blogs.msdn.com/b/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

2. http://msdn.microsoft.com/en-us/library/ms345101.aspx

3. http://msdn.microsoft.com/en-us/library/ms189524.aspx

4. http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx

If you have any questions about REST service, which i didn’t explained here, please ask in the comments, will explain in another post.

Advertisements
  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