UDF based on UNSAFE CLR Assembly with Native C++ Freeze on MSSQL 2014

• murat 59727

  SSC Rookie

  Points: 36

  More actions

  November 30, 2015 at 2:13 am

  #309837

  To make faster our client database solution we need to use the our c++ native dll-library from UDF. I read several articles about registering the UNSAFE Assemblies on MSDN and other sources (i skip references to avoid the spam links) - all instructions works fine on MS SQLSRV 2008 (x64) and NOT WORKS on MS SQLSRV 2014 (x64)! - on this version my UDF freeze if dll-imported function is included

  1) To simplify example I include in our native C++ dll project the small test function "test_func":

  test_func.cpp:

  #include "stdafx.h"

  double __stdcall test_func()

  {

  return 1.0;

  }

  in .def file also have added "test_func"

  After compiling - the CoreLIB.dll is signed with our company digital key.

  I have tested accessing to this function from .NET in c# Application - and it worked.

  2) I create new Visual C# -> Class Libary project and in Class1.cs file and put this code:

  using System;

  using System.Collections.Generic;

  using System.Linq;

  using System.Text;

  //using System.Threading.Tasks;

  using System.Data;

  using System.Data.SqlClient;

  using System.Data.SqlTypes;

  using Microsoft.SqlServer.Server;

  using System.Collections;

  using System.Runtime.InteropServices;

  public static class CLRTest

  {

  #if DEBUG

  [DllImport("D:\\Project\\Core_LIB\\x64\\Debug\\CoreLIB.dll")]

  #else

  [DllImport("C:\\SQLSRV_CLRFUNCS\\Core_LIB\\x64\\CoreLIB.dll")]

  #endif

  public static extern double test_func();

  [SqlFunction()]

  public static SqlInt32 TestFnc()

  {

  return (SqlInt32)test_func();

  }

  }

  The project successfully compiled into CLRTest.dll and signed with company digital key. I copy it into C:\SQLSRV_CLRFUNCS\CLRTest.dll.

  3) I create the TestDB database

  4) I enable CLR:

  sp_configure 'show advanced options', 1

  RECONFIGURE

  GO

  sp_configure 'clr enabled', 1

  RECONFIGURE

  GO

  sp_configure 'show advanced options', 0

  RECONFIGURE

  GO

  5) I prepare the TestDB database to use UNSAFE Assemblies:

  use TestDB

  go

  alter database TestDB set trustworthy on;

  go

  6) I register assembly

  CREATE ASSEMBLY CLRTest

  AUTHORIZATION dbo

  FROM 'C:\SQLSRV_CLRFUNCS\CLRTest.dll'

  WITH PERMISSION_SET = UNSAFE

  GO

  7) Register th UDF:

  CREATE FUNCTION TestFnc()

  RETURNS int

  AS EXTERNAL NAME CLRClaytonBeta.CLRTest.TestFnc

  GO

  All registration scripts finished successfully.

  8) Test my UDF, I run this query:

  select dbo.TestFnc();

  this command freeze without any errors!!

  if i replace the "return (SqlInt32)test_func();" string with "return 1;" - all works.

  Please, help!

• murat 59727

  SSC Rookie

  Points: 36

  More actions

  November 30, 2015 at 5:46 am

  #1842845

  please, close this topic.. I found problem: in my native dll I have hard initialization code in DLLMain. after comment it - all works fine.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  December 4, 2015 at 10:33 pm

  #1844191

  There is no way to close a topic on this Forum. Thanks for posting back that you found the issue.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato