MSSQL 어셈블리 만들기와 연동(Implementing CLR Object)

MSSQL 환경에서 SP 개발시 지원하지 않는 데이터 암복호화 관련 기능을 추가하고자 할때 유용한 기능으로 직접 작성한 DLL Library를 연동할 때 이용된다. 실제 프로젝트 진행시 보안 이슈로 인해 Client로부터 전달 받은 데이터의 복호화를 WAS에서 진행하지 않고 그대로 DB에 전달, SP내에서 복호화 하도록 하였습니다. 이후 자주 사용할 것으로 판된되어 정리해 둔다.

 

개발환경

-VisualStudio 2017

-MSSQL EXP 2017

-DotNetFramework 4.6.1

 

CLR DLL Library 생성

STEP1

-VisualStudio에서 SQL Server -> SQL Server 데이터베이스 프로젝트 생성

 

STEP2

-솔루션 탐색기에서 추가 -> 새항목 -> SQL Server -> SQL CLR C# ->SQL CLR C#에서 사용자 정의 함수 추가

 (해당 화면에서 저장프로시저 또는 사용자 정의 형식끼리 용도에 따라 추가할 수 있음)

-SQLGetKeys.cs를 추가

 

STEP3

-예제는 간단하게 두문자열을 합치는 기능으로 적용 하였다.

 

빌드하면 아래 경로에 적용한 SQLBaseFunctions.dll 파일이 생성된다. 해당 파일을 원하는 경로에 복사 후 MSSQL Assembly에 

등록해 사용할 수 있다.

 

 

*빌드시 유의사항

생성할 CLR DLL Library는 안전여부를 떠나 Win32 등을 이용할 것이므로 UNSAFE 권한 수준으로 적용할 것이며, 프로젝트 -> SQLBaseFunctions 속성 -> SQLCLR 탭에서 권한수준을 'UNSAFE'로 선택하여 빌드한다. 또는 외부접근을 허용할 경우 EXTERNAL_ACCESS 옵션을 적용하여 외부접근을 허용할 수 있도록 한다.
(*나의 경우엔 EXTERNAL_ACCESS 적용)

 

*CREATE ASSEMBLY에 대한 자세한 설명은 아래 경로에서 확인 가능

https://docs.microsoft.com/ko-kr/sql/relational-databases/clr-integration/assemblies/creating-an-assembly?view=sql-server-2017

 

 

MSSQL ASSEMBLY 등록

작성된 CLR DLL Library를 원하는 DB에서 script를 통해 등록할 수 있다. 

 

USE [master]

 

--CLR 활성작업

SET NOCOUNT ON;

USE master;

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE;

GO

 

--TRUSTWORTHY 기본 속성이 OFF이므로 높은 권한적용을 위해 ON 시킴

ALTER DATABASE  TESTDB

SET TRUSTWORTHY ON

 

USE [TESTDB]

GO

 

--ASSEMBLY 생성

CREATE ASSEMBLY [SQLBaseFunctions]

FROM 'D:\SQLBaseFunctions.dll'  --파일 지정 경로

WITH PERMISSION_SET = UNSAFE --또는 EXTERNAL_ACCESS

GO

 

 

CREATE FUNCTION [dbo].[UF_GET_ENCKEY](@po_strInputValue1 [nvarchar](8), @po_strInputValue2 [nvarchar](8))

RETURNS [nvarchar](max) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [SQLBaseFunctions].[SQLBaseFunctions.SQLGetKeys].[GetEncKeyValue]

GO

 

->정상 적용시 스칼라 반환 함수 항목에 아래와 같이 적용된다.

 

사용법

DECLARE @pi_intValue1 VARCHAR(8)

DECLARE @pi_intValue2 VARCHAR(8)

SET @pi_intValue1 = '11111111'

SET @pi_intValue2 = '22222222'

SELECT dbo.UF_GET_ENCKEY(@pi_intValue1, @pi_intValue2)

 

 

CREATE ASSEMBLY 생성시 오류 발생시 해결책

메시지 10327, 수준 14, 상태 1, 줄 8

어셈블리 'SQLBaseFunctions'이(가) 트러스트되어 있지 않으므로 어셈블리 'SQLBaseFunctions'에 대한 CREATE ASSEMBLY가 실패했습니다.

어셈블리는 UNSAFE ASSEMBLY 권한과 함께 해당하는 로그인이 있는 인증서 또는 비대칭 키로 서명되어 있는 경우나 sp_add_trusted_assembly를 사용하여 트러스트되어 있는 경우에 트러스터됩니다.

 

->해당 오류시 반드시 TRUSTWORTHY를 ON시켜야 함

https://m.blog.naver.com/PostView.nhn?blogId=dotnethelper&logNo=60178827166&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2Fㄹ