Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Tuesday, November 4, 2014

SQL CLR Error: .NET Framework is disabled. Enable "clr enabled" configuration option.



Created new SQL Server project in Visual studio, to generate DLL file having SQL function, when we tried to use published function we ended with below error message:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

To fix the above issue enable CLR in sql server:


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

After applying the code we tried to execute the created function, but ended in below error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "<functionname>":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort

System.Security.HostProtectionException:
   at UserDefinedFunctions.<functionname>(SqlDateTime pstTime, String Timezone)


To overcome the above issue, run below commands:

ALTER ASSEMBLY <functionname> WITH PERMISSION_SET = Safe

ALTER DATABASE <DBNAME>
SET TRUSTWORTHY ON


ALTER ASSEMBLY Timezone WITH PERMISSION_SET = UnSafe


No comments:

Post a Comment