SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQLCLR Level 7: Development and Security


Stairway to SQLCLR Level 7: Development and Security

Author
Message
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5979 Visits: 3084
Comments posted to this topic are about the item Stairway to SQLCLR Level 7: Development and Security

SQL# - https://SQLsharp.com/
Sql Quantum Lift - https://SqlQuantumLift.com/
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5979 Visits: 3084
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/
Sql Quantum Lift - https://SqlQuantumLift.com/
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34844 Visits: 886
Thanks for another good article.
shibbard 51721
shibbard 51721
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 19
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 -
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5979 Visits: 3084
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/
Sql Quantum Lift - https://SqlQuantumLift.com/
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5979 Visits: 3084
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/
Sql Quantum Lift - https://SqlQuantumLift.com/
shibbard 51721
shibbard 51721
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 19
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 Sad

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 /defineBigGrinEBUG;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 /defineBigGrinEBUG;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
WHERE sak.[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
WHERE sak.[thumbprint] = @PublicKeyToken
)
) AND
(NOT EXISTS(
SELECT *
FROM [sys].[server_principals] sp
INNER JOIN [sys].[asymmetric_keys] sak
ON sak.[sid] = sp.[sid]
WHERE sak.[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
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5979 Visits: 3084
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 Sad

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/
Sql Quantum Lift - https://SqlQuantumLift.com/
shibbard 51721
shibbard 51721
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 19
That worked like a charm. Thanks for your help!

Cheers,
Susan
Solomon Rutzky
Solomon Rutzky
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5979 Visits: 3084
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/
Sql Quantum Lift - https://SqlQuantumLift.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search