March 1, 2016 at 9:19 pm
Comments posted to this topic are about the item Stairway to SQLCLR Level 7: Development and Security
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 1, 2016 at 9:20 pm
Many thanks to David Poole for his time and help in peer-reviewing this article (i.e. Levels 6, 7, and 8) :-D.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 10, 2016 at 4:25 pm
Thanks for another good article.
June 28, 2016 at 11:29 am
I am totally excited to find this great series, I need to get up to speed quickly on CLR and the security issues involved and your tutorial is exactly what I was looking for. I really appreciate the detailed, step-by-step instructions with explanations of ‘why’ things are done, as well.
To learn by doing, I’ve gone through the steps in Part 6 and 7 – but I’m running into an issue however – it looks like the SQL query for AssemblySecuritySetup-Part1of2.sql might be truncated? When I paste it into either MSVS editor, or SSMS, it is getting flagged by Intellisense with the error ‘Unclosed quotation mark after the character N’ (line 55) and ‘Incorrect syntax near end of file’ at the very end.
Here is what I have copied/pasted from AssemblySecuritySetup-Part1of2.sql. It looks OK in the article and also here, but not in VS or in SSMS.
/**********************************************
* Script: AssemblySecuritySetup.sql
* Date: 2016-01-20
* By: Solomon Rutzky
* Of: Sql Quantum Leap ( http://SqlQuantumLeap.com )
*
* Stairway to SQLCLR - Level 6: Development Tools
*
* Stairway to SQLCLR series:
* http://www.sqlservercentral.com/stairway/105855/
*
**********************************************/
USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
--------------------------------------------------------------------------------
DECLARE @ErrorMessage NVARCHAR(4000);
-- We first need to create the Assembly containing just
-- the Key info so that we can get the "thumbprint" /
-- "publickeytoken" value from it. That value is used to
-- determine if the Asymmetric Key and Login already exist.
--
-- We only need this Assembly temporarily, so create within
-- a transaction to guarantee cleanup if something fails.
DECLARE@AssemblyName sysname, -- keep lower-case for servers with case-sensitive / binary collations
@AsymmetricKeyName sysname, -- keep lower-case for servers with case-sensitive / binary collations
@LoginName sysname, -- keep lower-case for servers with case-sensitive / binary collations
@PublicKeyToken VARBINARY(32),
@SQL NVARCHAR(MAX);
SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';
SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';
BEGIN TRY
BEGIN TRAN;
IF (NOT EXISTS(
SELECT*
FROM[sys].[assemblies] sa
WHERE[sa].[name] = @AssemblyName
)
)
BEGIN
SET @SQL = N'
CREATE ASSEMBLY [' + @AssemblyName + N']
AUTHORIZATION [dbo]
-- Insert the result of the following command, found in _TempAssembly.sql, here:
-- FINDSTR /I /C:"FROM 0x" KeyInfo_Create.sql > _TempAssembly.sql
Can you offer any help on resolving this? Am I supposed to insert something in the last line? It looks like there's an End missing, to me. But I don't completely understand what this script is doing, yet.
Or is there something else I have wrong that is causing this problem? I've been through the step a couple of times to make sure I haven't skipped anything with the same results. Thanks for any help you can give me -
June 28, 2016 at 11:47 am
Iwas Bornready (3/10/2016)
Thanks for another good article.
I just realized that I never replied to this, and I apologize for that.
Thank you very much for those kind words. I am glad to hear that you are liking this Stairway series :-).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
June 28, 2016 at 11:58 am
shibbard 51721 (6/28/2016)
I am totally excited to find this great series, I need to get up to speed quickly on CLR and the security issues involved and your tutorial is exactly what I was looking for. I really appreciate the detailed, step-by-step instructions with explanations of ‘why’ things are done, as well.
Hi there. Thank you very much for that compliment. I very much appreciate hearing that you appreciate the detail that I put into these articles. It takes quite a bit of time and energy (hence why I am not done with the series yet) but I think it's all worth it if it increases the general level of understanding of SQLCLR among the community, such that people are more successful using it and use it more appropriately.
To learn by doing, I’ve gone through the steps in Part 6 and 7 – but I’m running into an issue however – it looks like the SQL query for AssemblySecuritySetup-Part1of2.sql might be truncated? When I paste it into either MSVS editor, or SSMS, it is getting flagged by Intellisense with the error ‘Unclosed quotation mark after the character N’ (line 55) and ‘Incorrect syntax near end of file’ at the very end.
That script is the first half (hence named "Part1of2") of what will get concatenated into a single SQL script, along with "Part2of2" and a fragment of a file that gets produced when you compile the Assembly (so it doesn't exist yet). So yes, those Intellisense errors are to be expected. At this point, all you need to do is save the script so that it exists to be concatenated in the PostBuild Event.
Please let me know if that does not work, or if you have additional questions.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
June 28, 2016 at 12:52 pm
Thanks very much for your quick response! And your explanation makes sense and with looking closer I can see how that works. However, my deploy failed 🙁
Here is the text of the output from the deploy:
------ Build started: Project: KeyInfo, Configuration: Debug Any CPU ------
CD /D "C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\bin\Debug\"
IF EXIST _TempAssembly.sql DEL /F _TempAssembly.sql
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva- /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorlib.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /debug+ /debug:full /keycontainer:VS_KEY_C929429D53C1D268 /optimize- /out:obj\Debug\KeyInfo.dll /target:library /warnaserror- /utf8output OnlyNeededToGenerateCreateAssembly.cs Properties\AssemblyInfo.cs "C:\Users\shibbard\AppData\Local\Temp\2\.NETFramework,Version=v2.0.SqlClrAttributes.cs"
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
Writing model to C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\obj\Debug\Model.xml...
Writing create script to KeyInfo_Create.sql...
KeyInfo -> C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\bin\Debug\KeyInfo.dll
KeyInfo -> C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\bin\Debug\KeyInfo.dacpac
CD /D "C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\bin\Debug\"
FINDSTR /I /C:"FROM 0x" KeyInfo_Create.sql > _TempAssembly.sql
COPY /V /Y /B "C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\AssemblySecuritySetup-Part1of2.sql" + _TempAssembly.sql + "C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\AssemblySecuritySetup-Part2of2.sql" "C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\AssemblySecuritySetup.sql"
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\AssemblySecuritySetup-Part1of2.sql
_TempAssembly.sql
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\KeyInfo\AssemblySecuritySetup-Part2of2.sql
1 file(s) copied.
------ Build started: Project: StairwayToSQLCLR-06_ConnectionTypeTest, Configuration: Debug Any CPU ------
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva- /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorlib.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /debug+ /debug:full /keycontainer:VS_KEY_C929429D53C1D268 /optimize- /out:obj\Debug\StairwayToSQLCLR-06_ConnectionTypeTest1.dll /target:library /warnaserror- /utf8output ConnectionTypeTest.cs Properties\AssemblyInfo.cs "C:\Users\shibbard\AppData\Local\Temp\2\.NETFramework,Version=v2.0.SqlClrAttributes.cs"
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
Writing model to C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\obj\Debug\Model.xml...
StairwayToSQLCLR-06_ConnectionTypeTest -> C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest1.dll
StairwayToSQLCLR-06_ConnectionTypeTest -> C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.dacpac
------ Skipped Deploy: Project: KeyInfo, Configuration: Debug Any CPU ------
Project not selected to build for this solution configuration
------ Deploy started: Project: StairwayToSQLCLR-06_ConnectionTypeTest, Configuration: Debug Any CPU ------
Deployment script generated to:
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.sql
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.sql(114,1): Error: SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 1, Line 72 Incorrect syntax near 'dbo'.
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.sql(43,0): Error: SQL72045: Script execution error. The executed script:
IF (EXISTS (SELECT sc.*
FROM sys.configurations AS sc
WHERE sc.[name] = N'clr enabled'
AND sc.[value_in_use] = 0))
BEGIN
EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE;
END
GO
USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
DECLARE @ErrorMessage AS NVARCHAR (4000);
DECLARE @AssemblyName AS sysname, @AsymmetricKeyName AS sysname, @LoginName AS sysname, @PublicKeyToken AS VARBINARY (32), @SQL AS NVARCHAR (MAX);
SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';
SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';
BEGIN TRY
BEGIN TRANSACTION;
IF (NOT EXISTS (SELECT *
FROM [sys].[assemblies] AS sa
WHERE [sa].[name] = @AssemblyName))
BEGIN
SET @SQL = N'
CREATE ASSEMBLY [' + @AssemblyName + N'] AUTHORIZATION [dbo]
-
An error occurred while the batch was being executed.
Done building project "StairwayToSQLCLR-06_ConnectionTypeTest.sqlproj" -- FAILED.
Build FAILED.
========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 1 skipped ==========
and here is StairwayToSQLCLR-06_ConnectionTypeTest2.sql:
/*
Deployment script for StairwayToSQLCLR-06_ConnectionTypeTest_2
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "StairwayToSQLCLR-06_ConnectionTypeTest_2"
:setvar DefaultFilePrefix "StairwayToSQLCLR-06_ConnectionTypeTest_2"
:setvar DefaultDataPath "C:\Users\shibbard\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\"
:setvar DefaultLogPath "C:\Users\shibbard\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
USE [$(DatabaseName)];
GO
-- Make sure "CLR Integration" feature is enabled.
IF (EXISTS(
SELECT sc.*
FROM sys.configurations sc
WHERE sc.[name] = N'clr enabled'
AND sc.[value_in_use] = 0
)
)
BEGIN
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
END;
GO
/**********************************************
* Script: AssemblySecuritySetup.sql
* Date: 2016-01-20
* By: Solomon Rutzky
* Of: Sql Quantum Leap ( http://SqlQuantumLeap.com )
*
* Stairway to SQLCLR - Level 6: Development Tools
*
* Stairway to SQLCLR series:
* http://www.sqlservercentral.com/stairway/105855/
*
**********************************************/
USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
--------------------------------------------------------------------------------
DECLARE @ErrorMessage NVARCHAR(4000);
-- We first need to create the Assembly containing just
-- the Key info so that we can get the "thumbprint" /
-- "publickeytoken" value from it. That value is used to
-- determine if the Asymmetric Key and Login already exist.
--
-- We only need this Assembly temporarily, so create within
-- a transaction to guarantee cleanup if something fails.
DECLARE@AssemblyName sysname, -- keep lower-case for servers with case-sensitive / binary collations
@AsymmetricKeyName sysname, -- keep lower-case for servers with case-sensitive / binary collations
@LoginName sysname, -- keep lower-case for servers with case-sensitive / binary collations
@PublicKeyToken VARBINARY(32),
@SQL NVARCHAR(MAX);
SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';
SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';
BEGIN TRY
BEGIN TRAN;
IF (NOT EXISTS(
SELECT*
FROM[sys].[assemblies] sa
WHERE[sa].[name] = @AssemblyName
)
)
BEGIN
SET @SQL = N'
CREATE ASSEMBLY [' + @AssemblyName + N'] AUTHORIZATION [dbo]
-- Insert the result of the following command, found in _TempAssembly.sql, here:
-- FINDSTR /I /C:"FROM 0x" KeyInfo_Create.sql > _TempAssembly.sql FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008EB272570000000000000000E00002210B010B00000A00000006000000000000DE2800000020000000400000000000100020000000020000040000000000000004000000000000000080000000020000818000000300408500001000001000000000100000100000000000001000000000000000000000008828000053000000004000005803000000000000000000000000000000000000006000000C000000502700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E408000000200000000A000000020000000000000000000000000000200000602E72737263000000580300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000C0280000000000004800000002000500F42000005C06000009000000000000000000000000000000502000008000000000000000000000000000000000000000000000000000000000000000000000000F972D25374EA2C96BE99E8087811B77DE82AFC5B268910BD8BFAA1C2418A02AF7ACAFB5CC36A8465DE89113F2E9D8FA573D97B04CD4A1FA9053DC95F76D3CDE81649A324C493FA30AA8A3E82E500B2AED606D16E0F675BE195B021F64CC540D6491CC7CB93233EB8CD9C5E47CCADFEE7B10658E914B4305BA0E2B8A4D96C0D3133001001000000001000011007E0F00000A731000000A0A2B00062A1E02281100000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000DC010000237E0000480200008C02000023537472696E677300000000D40400000800000023555300DC040000100000002347554944000000EC0400007001000023426C6F620000000000000002000001471402000900000000FA25330016000001000000120000000200000002000000110000000C00000001000000010000000200000000000A0001000000000006003B0034000A0063004E000600A80096000600BF0096000600DC0096000600FB00960006001401960006002D01960006004801960006006301960006007C0196000600950196000600C501B2013700D901000006000802E80106002802E8010A0069024E0206007E02340000000000010000000000010001000100100016000000050001000100D0200000000096006D000A000100EC2000000000861890000F000100190090001300210090001300290090001300310090001300390090001300410090001300490090001300510090001300590090001300610090001300690090001800790090001E00810090000F00890090000F0091008502CA00110090001300090090000F0020007300C5002E002B0004012E001300F1002E001B00FE002E002300FE002E000B00D2002E00330019012E003B00FE002E00530031012E005B003E012E00630047012E006B005001CD000480000001000200030004000100000023004602000002000000000000000000000001002B0000000000020000000000000000000000010042000000000000000000003C4D6F64756C653E004B6579496E666F2E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67004F6E6C794E6565646564546F47656E6572617465437265617465417373656D626C79002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004B6579496E666F004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500537472696E6700456D707479000000032000000000008666951431463043ABD05840E025D6DC0008B77A5C561934E089040000110903200001042001010E052001011139042001010880A000240000048000009400000006020000002400005253413100040000010001004BBC8CE79E7F308FCD0D5C8520E2DEA1CFE6C3A416B5D52780AC11CD0D9349F00C6DE91C46CE233B05DDE9A10CBE355593E279FA39A859C668B2C1FD95FB641E7FFF8C69425A6A1E0A31209C17CDC0750C07E99E94BC7DBBC10ADA9DADF78C07D7BE32BA26552D90ED6552C0DA0671D181F71B4B06EDFA5293F3FCFC7F0C38D8040100000002060E04070111091E0100194C6576656C203620446576656C6F706D656E7420546F6F6C7300000C0100074B6579496E666F00000501000000001401000F537461697277617920746F20434C52000017010012436F7079726967687420C2A920203230313600000C010007352E362E372E3800000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000008EB2725700000000020000001C0100006C2700006C0900005253445306F1DEC1FBC94F41897E8A50C39C23E101000000633A5C54656D705C5374616972776179546F53514C434C525C4C6576656C2D30365C5374616972776179546F53514C434C522D30365F436F6E6E656374696F6E54797065546573745C4B6579496E666F5C6F626A5C44656275675C4B6579496E666F2E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B02800000000000000000000CE280000002000000000000000000000000000000000000000000000C028000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000FC0200000000000000000000FC0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100060005000800070006000500080007003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0045C020000010053007400720069006E006700460069006C00650049006E0066006F00000038020000010030003000300030003000340062003000000028000800010043006F006D006D0065006E007400730000004B006500790049006E0066006F0000005C001A000100460069006C0065004400650073006300720069007000740069006F006E00000000004C006500760065006C0020003600200044006500760065006C006F0070006D0065006E007400200054006F006F006C0073000000300008000100460069006C006500560065007200730069006F006E000000000035002E0036002E0037002E003800000038000C00010049006E007400650072006E0061006C004E0061006D00650000004B006500790049006E0066006F002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003600000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004B006500790049006E0066006F002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D0065000000000053007400610069007200770061007900200074006F00200043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000035002E0036002E0037002E003800000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0032002E0033002E003400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
';
EXEC (@SQL);
END;
SET @PublicKeyToken = CONVERT(VARBINARY(32), ASSEMBLYPROPERTY(@AssemblyName, 'PublicKey'));
IF (NOT EXISTS(
SELECT*
FROM[sys].[asymmetric_keys] sak
WHEREsak.[thumbprint] = @PublicKeyToken
)
)
BEGIN
SET @SQL = N'
CREATE ASYMMETRIC KEY [' + @AsymmetricKeyName + N']
AUTHORIZATION [dbo]
FROM ASSEMBLY [' + @AssemblyName + N'];';
EXEC (@SQL);
END;
SET @SQL = N'DROP ASSEMBLY [' + @AssemblyName + N'];';
EXEC (@SQL);
COMMIT TRAN;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN;
END;
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
RETURN; -- exit the script
END CATCH;
-- If the Asymmetric Key exists but the Login does not exist, we need to:
-- 1) Create the Login
-- 2) Grant the appropriate permission
IF (EXISTS(
SELECT*
FROM[sys].[asymmetric_keys] sak
WHEREsak.[thumbprint] = @PublicKeyToken
)
) AND
(NOT EXISTS(
SELECT*
FROM[sys].[server_principals] sp
INNER JOIN[sys].[asymmetric_keys] sak
ONsak.[sid] = sp.[sid]
WHEREsak.[thumbprint] = @PublicKeyToken
)
)
BEGIN
BEGIN TRY
BEGIN TRAN;
SET @SQL = N'
CREATE LOGIN [' + @LoginName + N']
FROM ASYMMETRIC KEY [' + @AsymmetricKeyName + N'];';
EXEC (@SQL);
SET @SQL = N'
GRANT EXTERNAL ACCESS ASSEMBLY TO [' + @LoginName + N'];
-- OR, comment out the GRANT statement above, and uncomment the following:
-- GRANT UNSAFE ASSEMBLY TO [' + @LoginName + N'];
';
EXEC (@SQL);
COMMIT TRAN;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN;
END;
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
RETURN; -- exit the script
END CATCH;
END;
--------------------------------------------------------------------------------
GO
USE [$(DatabaseName)];
GO
GO
PRINT N'Creating [StairwayToSQLCLR-06_ConnectionTypeTest1]...';
GO
CREATE ASSEMBLY [StairwayToSQLCLR-06_ConnectionTypeTest1]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008FB272570000000000000000E00002210B010B00000E000000060000000000004E2D00000020000000400000000000100020000000020000040000000000000004000000000000000080000000020000D9330000030040850000100000100000000010000010000000000000100000000000000000000000002D00004B00000000400000E003000000000000000000000000000000000000006000000C000000C82B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000540D000000200000000E000000020000000000000000000000000000200000602E72737263000000E0030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000302D0000000000004800000002000500CC210000FC090000090000000000000000000000000000005020000080000000000000000000000000000000000000000000000000000000000000000000000095784E06088FA35EA8B5CC6A13401F822394C5C692DBA701A8D1F84AA43FB1B7B4C5F8AA7E67DA53F914C4AE8692584D325E325FC1AE04EC4DDBFB1DAAB86EA210C6971E5DAA50897E158DECD776980DAA9ABC76F8D3A19ADFF11DA03D5E389F322259E6E369CCBEAB59337DF1A1C47EC9D5F90B643DC9FCF246AC1A14C569A41B300200BF00000001000011007E0F00000A0A140B007201000070731000000A0C00086F1100000A0D00090F00281200000A6F1300000A000F01281400000A16FE01130511052D0D00281500000A6F1600000A0B00086F1700000A000714FE01130511052D0900076F1800000A0000096F1900000A0A00DE120914FE01130511052D07096F1A00000A00DC0000DE120814FE01130511052D07086F1A00000A00DC0000DE1D000714FE01130511052D1000076F1800000A00076F1B00000A000000DC000613042B0011042A000128000002001D00506D001200000000020015006E83001200000000020009009099001D000000001E02281C00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000064020000237E0000D00200000004000023537472696E677300000000D006000038000000235553000807000010000000234755494400000018070000E402000023426C6F620000000000000002000001471502000900000000FA253300160000010000001B0000000200000002000000020000001C0000000C00000001000000010000000200000000000A0001000000000006004E0047000A00760061000A00800061000600D400C2000600EB00C20006000801C20006002701C20006004001C20006005901C20006007401C20006008F01C2000600A801C2000600C101C2000600F101DE013B00050200000600340214020600540214020A00B5029A020600CA0247000A00ED02D7020A00FB02D7020A0031031E030A0056039A0206007B03610306009F0361030A00C7031E030600EC03470000000000010000000000010001000100100036000000050001000100D0200000000096008B000A000100C4210000000086189E001200030000000100A40000000200B10021009E00160029009E00160031009E00160039009E00160041009E00160049009E00160051009E00160059009E00160061009E00160069009E00160071009E001B0081009E00210089009E00120091009E0012009900D1021702A1009E001600A10006031B02110014032002B1003B03160019004B032402B9008B032802C100BB032D02D100D4031200C900D9031200B100DE033202D900F8031200C900F803120009009E00120020007300C8002E002B0079022E00130061022E001B0073022E00230073022E000B0042022E0033008E022E003B0073022E005300A6022E005B00B3022E006300BC022E006B00C50236020480000001000200030004000100000026007202000002000000000000000000000001003E0000000000020000000000000000000000010055000000000000000000003C4D6F64756C653E005374616972776179546F53514C434C522D30365F436F6E6E656374696F6E5479706554657374312E646C6C0054657374696E67006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C426F6F6C65616E00436F6E6E656374696F6E5479706554657374002E63746F720053716C546F4578656375746500557365496D706572736F6E6174696F6E0053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005374616972776179546F53514C434C522D30365F436F6E6E656374696F6E547970655465737431004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650044424E756C6C0056616C75650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053716C436F6D6D616E6400437265617465436F6D6D616E64006765745F56616C75650053797374656D2E446174612E436F6D6D6F6E004462436F6D6D616E64007365745F436F6D6D616E6454657874006765745F4973547275650053716C436F6E746578740053797374656D2E53656375726974792E5072696E636970616C0057696E646F77734964656E74697479006765745F57696E646F77734964656E746974790057696E646F7773496D706572736F6E6174696F6E436F6E7465787400496D706572736F6E617465004462436F6E6E656374696F6E004F70656E00556E646F00457865637574655363616C61720049446973706F7361626C6500446973706F736500003543006F006E007400650078007400200043006F006E006E0065006300740069006F006E0020003D00200074007200750065003B0000006A7C15C45584EF45A1BA46A5009BF8780008B77A5C561934E0890700021C1109110D03200001042001010E05200101113D042001010880A000240000048000009400000006020000002400005253413100040000010001004BBC8CE79E7F308FCD0D5C8520E2DEA1CFE6C3A416B5D52780AC11CD0D9349F00C6DE91C46CE233B05DDE9A10CBE355593E279FA39A859C668B2C1FD95FB641E7FFF8C69425A6A1E0A31209C17CDC0750C07E99E94BC7DBBC10ADA9DADF78C07D7BE32BA26552D90ED6552C0DA0671D181F71B4B06EDFA5293F3FCFC7F0C38D8814D01000300540E044E616D651F5374616972776179546F53514C434C525F436F6E6E656374696F6E546573745455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D44617461416363657373010000000306124D04200012550320000E03200002040000126104200012650320001C0B07061C1265125112551C021E0100194C6576656C203620446576656C6F706D656E7420546F6F6C7300001101000C4578616D706C6520436F646500000501000000001401000F537461697277617920746F20434C52000017010012436F7079726967687420C2A920203230313600000C010007352E362E372E3800000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000008FB2725700000000020000001C010000E42B0000E40D000052534453722BE63959D4CB49A32C38D6494F8DE402000000633A5C54656D705C5374616972776179546F53514C434C525C4C6576656C2D30365C5374616972776179546F53514C434C522D30365F436F6E6E656374696F6E54797065546573745C5374616972776179546F53514C434C522D30365F436F6E6E656374696F6E54797065546573745C6F626A5C44656275675C5374616972776179546F53514C434C522D30365F436F6E6E656374696F6E5479706554657374312E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000282D000000000000000000003E2D0000002000000000000000000000000000000000000000000000302D00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000880300000000000000000000880334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100060005000800070006000500080007003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004E8020000010053007400720069006E006700460069006C00650049006E0066006F000000C4020000010030003000300030003000340062003000000034000D00010043006F006D006D0065006E007400730000004500780061006D0070006C006500200043006F0064006500000000005C001A000100460069006C0065004400650073006300720069007000740069006F006E00000000004C006500760065006C0020003600200044006500760065006C006F0070006D0065006E007400200054006F006F006C0073000000300008000100460069006C006500560065007200730069006F006E000000000035002E0036002E0037002E003800000078002C00010049006E007400650072006E0061006C004E0061006D00650000005300740061006900720077006100790054006F00530051004C0043004C0052002D00300036005F0043006F006E006E0065006300740069006F006E005400790070006500540065007300740031002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003600000080002C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300740061006900720077006100790054006F00530051004C0043004C0052002D00300036005F0043006F006E006E0065006300740069006F006E005400790070006500540065007300740031002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D0065000000000053007400610069007200770061007900200074006F00200043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000035002E0036002E0037002E003800000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0032002E0033002E00340000000000000000000000000000000000000000000000000000000000000000000000002000000C000000503D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
PRINT N'Creating [dbo].[StairwayToSQLCLR_ConnectionTest]...';
GO
CREATE FUNCTION [dbo].[StairwayToSQLCLR_ConnectionTest]
(@SqlToExecute NVARCHAR (MAX), @UseImpersonation BIT)
RETURNS SQL_VARIANT
AS
EXTERNAL NAME [StairwayToSQLCLR-06_ConnectionTypeTest1].[Testing].[ConnectionTypeTest]
GO
PRINT N'Update complete.';
GO
Does this give you any clue as to what's wrong here? Again, thanks for your aid.
Susan
June 28, 2016 at 1:18 pm
shibbard 51721 (6/28/2016)
Thanks very much for your quick response! And your explanation makes sense and with looking closer I can see how that works. However, my deploy failed 🙁Here is the text of the output from the deploy:
SET @SQL = N'
CREATE ASSEMBLY [' + @AssemblyName + N'] AUTHORIZATION [dbo]
-- Insert the result of the following command, found in _TempAssembly.sql, here:
-- FINDSTR /I /C:"FROM 0x" KeyInfo_Create.sql > _TempAssembly.sql FROM 0x4D5A90000300000004000000FFE6F7
Does this give you any clue as to what's wrong here? Again, thanks for your aid.
Susan
Hi again. This should be easy to fix. You are missing a return on that last comment line of the Part1of2 script. The lack of return caused the FROM 0x4D5A... to be tacked onto the end of that inline comment. Just edit the Part1of2 script and add a return to the end so that the last real line is an empty line. Then re-run the deploy.
Please let me know if that works or not.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
June 28, 2016 at 3:39 pm
That worked like a charm. Thanks for your help!
Cheers,
Susan
June 28, 2016 at 7:05 pm
shibbard 51721 (6/28/2016)
That worked like a charm. Thanks for your help!Cheers,
Susan
You are quite welcome. Glad to hear that it is all working. I will try to add a note to the article about making sure that there is a carriage return at the end of that final inline comment line.
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 26, 2016 at 8:45 am
I had the exact same issue. Thanks for the clarification!
June 21, 2017 at 12:51 pm
Solomon,
I have successfully deployed some SQL CLR for EXTERNAL_ACCESS to allow Web service calls from SQL Server. Since I am a DBA and not a .Net programming expert, I asked for a code review from someone on my team who is. She pointed out that they had done a .Net application deployment some years ago, and they had signed the application with a .pfx file. They had a problem with the application about a year later because the certificate in the .pfx file had expired.
For my development and deployment process, I created a simple assembly that had one class with no members, and I had Visual Studio create a new .pfx file for it. For my real assemblies that I use for SQL CLR, I import that .pfx file into the project for signing. That way, once I have created the asymmetric key and login, all other assemblies based on that .pfx file will install and run.
I checked my .pfx file with "C:\Windows\System32\certutil.exe", and it has an expiration date about 9 months from now.
Question 1: Are my SQL CLR procedures and functions going to stop working 9 months from now?
Question 2: Will I be able to sign future assemblies after 9 months using that .pfx file?
Question 3: Is there a more permanent solution?
Sincerely,
Daniel
June 21, 2017 at 3:10 pm
JediSQL - Wednesday, June 21, 2017 12:51 PMSolomon,
I have successfully deployed some SQL CLR for EXTERNAL_ACCESS to allow Web service calls from SQL Server. Since I am a DBA and not a .Net programming expert, I asked for a code review from someone on my team who is. She pointed out that they had done a .Net application deployment some years ago, and they had signed the application with a .pfx file. They had a problem with the application about a year later because the certificate in the .pfx file had expired.For my development and deployment process, I created a simple assembly that had one class with no members, and I had Visual Studio create a new .pfx file for it. For my real assemblies that I use for SQL CLR, I import that .pfx file into the project for signing. That way, once I have created the asymmetric key and login, all other assemblies based on that .pfx file will install and run.
I checked my .pfx file with "C:\Windows\System32\certutil.exe", and it has an expiration date about 9 months from now.
Question 1: Are my SQL CLR procedures and functions going to stop working 9 months from now?
Question 2: Will I be able to sign future assemblies after 9 months using that .pfx file?
Question 3: Is there a more permanent solution?
Hi there, Daniel. Good question, and actually this might be better suited to being asked in the SQLCLR area of the SQL Server Central forums, but for the moment I can say that while I don't have time to test this to give a definitive answer, I believe:
1) I doubt anything will stop working as the public key should be usable forever
2) It is quite likely that you will not be able to sign future assemblies past the expiration date
3) The solution is to specify an end-date when creating the Certificate. The default end-date is 1 year from the creation date, but any means of generating a certificate will have an option for specifying the end date. Just pick something towards the upper-end, such as in 2099.
All of that being said, are you sure you created a certificate? If you created it via Visual Studio, in Project Properties, under "signing", then that should just be a strong name key file ("snk", which turns into "pfx" when protected with a password), and which does not have an expiration date.
------------------------------------
UPDATE:
This question was cross-posted on social.msdn.com, but that version contained some additional info. In that other version of the question it was stated that the .pfx file was created by Visual Studio and that it was an Asymmetric Key that was created in SQL Server. In this case, I can say that there is no issue here; there is no expiration date. Visual Studio does not deal with Certificates, and Asymmetric Keys in SQL Server do not have an expiration date.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 10, 2019 at 5:26 am
Hi Solomon
Thank you for sharing this series.
I receive an error when deploying. Seems to happen when the assembly should be created.
Do you have any clues?
Error copied from the output window:
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.sql(111,1): Error: SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 1, Line 69 CREATE or ALTER ASSEMBLY for assembly 'KeyInfo' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.sql(43,0): Error: SQL72045: Script execution error. The executed script:
IF (EXISTS (SELECT sc.*
FROM sys.configurations AS sc
WHERE sc.[name] = N'clr enabled'
AND sc.[value_in_use] = 0))
BEGIN
EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE;
END
GO
USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
DECLARE @ErrorMessage AS NVARCHAR (4000);
DECLARE @AssemblyName AS sysname, @AsymmetricKeyName AS sysname, @LoginName AS sysname, @PublicKeyToken AS VARBINARY (32), @SQL AS NVARCHAR (MAX);
SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';
SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';
BEGIN TRY
BEGIN TRANSACTION;
IF (NOT EXISTS (SELECT *
FROM [sys].[assemblies] AS sa
WHERE [sa].[name] = @AssemblyName))
BEGIN
SET @SQL = N'
CREATE ASSEMBLY [' + @AssemblyName + N']
AUTHORIZATION [dbo
An error occurred while the batch was being executed.
Done building project "StairwayToSQLCLR-06_ConnectionTypeTest.sqlproj" -- FAILED.
Build FAILED.
========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 1 skipped ==========
Cheers
Marko
September 10, 2019 at 5:50 am
Hi Solomon
Thank you for sharing this series.
You are quite welcome 🙂
I receive an error when deploying. Seems to happen when the assembly should be created.
Do you have any clues?
Error copied from the output window:
C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug\StairwayToSQLCLR-06_ConnectionTypeTest2.sql(111,1): Error: SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 1, Line 69 CREATE or ALTER ASSEMBLY for assembly 'KeyInfo' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
Ah, that is a SQL Server 2017 and newer problem. Please see the following post of mine for an updated version that accounts for the new security "feature":
SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply