SHARE
TWEET

Avoiding "Trusted Assemblies" - Demo

SqlQuantumLeap Aug 25th, 2017 (edited) 1,701 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  --------- TEST 3 FOR SQLCLR on SQL Server 2017 (Windows and Linux) ---------
  3.  
  4. By: Solomon Rutzky
  5. On: 2017-08-18
  6. Location: https://pastebin.com/mwi5BidL
  7. Related blog post: https://SqlQuantumLeap.com/2017/08/28/sqlclr-vs-sql-server-2017-part-4-trusted-assemblies-the-disappointment/
  8.  
  9. Description: This script shows a technique for PROPERLY handling SQLCLR Assemblies, marked
  10.              marked as SAFE and not signed, that reside in databases that are upgraded to
  11.              SQL Server 2017 or restored into SQL Server 2017 from a previous version,
  12.              while both keeping "clr strict security" enabled and "TRUSTWORTHY" disabled.
  13.              Prior to SQL Server 2017 it was not necessary to have SAFE Assemblies signed
  14.              and have a corresponding signature-based Login with the UNSAFE ASSEMBLY
  15.              permission. As of SQL Server 2017 it is necessary, but the new "Trusted
  16.              Assemblies" feature is NOT necessary and should not be used. Ever.
  17.  
  18.              The trick is to simply use module signing, which isn't really a trick at all.
  19.  
  20.              For a detailed, step-by-step explanation, please see the relatd blog post
  21.              noted above.
  22. */
  23.  
  24.  
  25.  
  26. USE [master];
  27. SET NOCOUNT ON;
  28. GO
  29.  
  30. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NULL)
  31. BEGIN
  32.     PRINT 'Creating Database...';
  33.     CREATE DATABASE [SQLCLRvsClrStrictSecurity]
  34.         COLLATE Latin1_General_100_CI_AS;
  35. END;
  36.  
  37. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NOT NULL)
  38. BEGIN
  39.     PRINT 'Altering Database (make sure RECOVERY=SIMPLE and TRUSTWORTHY=OFF)...';
  40.     ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  41.         SET RECOVERY SIMPLE,
  42.         TRUSTWORTHY OFF;
  43. END;
  44.  
  45.  
  46. PRINT 'Making sure that SQLCLR is enabled...';
  47. IF (EXISTS(
  48.             SELECT *
  49.             FROM   sys.configurations sc
  50.             WHERE  sc.[configuration_id] = 1562 -- "clr enabled"
  51.             AND    sc.[value_in_use] = 0
  52.     ))
  53. BEGIN
  54.     PRINT 'Enabling SQLCLR...';
  55.     EXEC sp_configure N'clr enabled', 1; RECONFIGURE;
  56. END;
  57.  
  58. PRINT 'Making sure that SQLCLR "strict security" is enabled...';
  59. IF (EXISTS(
  60.             SELECT *
  61.             FROM   sys.configurations sc
  62.             WHERE  sc.[configuration_id] = 518 -- "show advanced options"
  63.             AND    sc.[value_in_use] = 0
  64.     ))
  65. BEGIN
  66.     PRINT ' Showing advanced options...';
  67.     EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
  68. END;
  69.  
  70.  
  71. IF (EXISTS(
  72.             SELECT *
  73.             FROM   sys.configurations sc
  74.             WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
  75.             AND    sc.[value_in_use] = 0
  76.     ))
  77. BEGIN
  78.     PRINT ' Enabling CLR strict security...';
  79.     EXEC sp_configure N'clr strict security', 1; RECONFIGURE;
  80. END;
  81.  
  82.  
  83.  
  84. -- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
  85. PRINT CHAR(13) + CHAR(10) + 'Verifying settings...';
  86. SELECT *
  87. FROM   sys.configurations sc
  88. WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
  89.  
  90. SELECT [name], [is_trustworthy_on], [collation_name]
  91. FROM sys.databases WHERE [database_id] = DB_ID(N'SQLCLRvsClrStrictSecurity');
  92.  
  93.  
  94. GO
  95. USE [SQLCLRvsClrStrictSecurity];
  96.  
  97.  
  98. IF (OBJECT_ID(N'dbo.Date_Format') IS NULL)
  99. BEGIN
  100.     PRINT CHAR(13) + CHAR(10) + 'SQLCLR UDF [Date_Format] does not exist:';
  101.  
  102.     PRINT ' TEMPORARILY Altering Database to set TRUSTWORTHY=ON...';
  103.     ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  104.         SET TRUSTWORTHY ON;
  105.  
  106.  
  107.     IF (ASSEMBLYPROPERTY(N'SQL2017_TrustedAssemblies', N'MvID') IS NULL)
  108.     BEGIN
  109.         PRINT ' Creating [SQL2017_TrustedAssemblies] Assembly...';
  110.         CREATE ASSEMBLY [SQL2017_TrustedAssemblies]
  111.             AUTHORIZATION [dbo]
  112.             FROM 0x\
  113. 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
  114. 0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
  115. 69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
  116. 2400000000000000504500004C010300A4AD9F590000000000000000E00002210B010B00000A0000\
  117. 0008000000000000DE28000000200000004000000000001000200000000200000400000000000000\
  118. 06000000000000000080000000020000000000000300608500001000001000000000100000100000\
  119. 000000001000000000000000000000008C2800004F00000000400000080500000000000000000000\
  120. 0000000000000000006000000C000000542700001C00000000000000000000000000000000000000\
  121. 00000000000000000000000000000000000000000000000000200000080000000000000000000000\
  122. 082000004800000000000000000000002E74657874000000E408000000200000000A000000020000\
  123. 000000000000000000000000200000602E72737263000000080500000040000000060000000C0000\
  124. 000000000000000000000000400000402E72656C6F6300000C000000006000000002000000120000\
  125. 0000000000000000000000004000004200000000000000000000000000000000C028000000000000\
  126. 480000000200050080200000D4060000010000000000000000000000000000000000000000000000\
  127. 0000000000000000000000000000000000000000000000000000000000000000133002001C000000\
  128. 010000110F00280F00000A0A12000F01281000000A281100000A731200000A2A1E02281300000A2A\
  129. 42534A4201000100000000000C00000076342E302E33303331390000000005006C000000FC010000\
  130. 237E000068020000C802000023537472696E67730000000030050000080000002355530038050000\
  131. 100000002347554944000000480500008C01000023426C6F62000000000000000200000147150200\
  132. 0900000000FA2533001600000100000013000000020000000200000002000000130000000C000000\
  133. 01000000010000000200000000000A000100000000000600480041000A0070005B000A007A005B00\
  134. 0600C400B2000600DB00B2000600F800B20006001701B20006003001B20006004901B20006006401\
  135. B20006007F01B20006009801B2000600B101B2000600E101CE013B00F50100000600240204020600\
  136. 440204020A0097027C020600AC024100000000000100000000000100010001001000280000000500\
  137. 01000100502000000000960086000A00010078200000000086189200130003000000010098000000\
  138. 0200A500210092001700290092001700310092001700390092001700410092001700490092001700\
  139. 510092001700590092001700610092001700690092001700710092001C0081009200220089009200\
  140. 13009100920013001900B5023F001100B50244009900BF0248001100920017000900920013002000\
  141. 730027002E002B00DD002E00130071002E001B00C1002E002300C7002E000B0052002E0033000901\
  142. 2E003B00C1002E00530045012E005B0058012E00630061012E006B006A014D0004800000E1070800\
  143. 14000100000000000000620200000400000000000000000000000100380000000000040000000000\
  144. 00000000000001004F00000000000000003C4D6F64756C653E0053514C323031375F547275737465\
  145. 64417373656D626C6965732E646C6C0053514C32303137767353514C434C52006D73636F726C6962\
  146. 0053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53\
  147. 716C54797065730053716C537472696E670053716C4461746554696D6500446174655F466F726D61\
  148. 74002E63746F720044617465546F466F726D617400466F726D6174537472696E670053797374656D\
  149. 2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D\
  150. 626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E6669677572\
  151. 6174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500\
  152. 417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967\
  153. 687441747472696275746500417373656D626C7954726164656D61726B4174747269627574650041\
  154. 7373656D626C7943756C7475726541747472696275746500417373656D626C7956657273696F6E41\
  155. 747472696275746500417373656D626C7946696C6556657273696F6E417474726962757465005379\
  156. 7374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562\
  157. 756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C65725365727669\
  158. 63657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74\
  159. 696D65436F6D7061746962696C6974794174747269627574650053514C323031375F547275737465\
  160. 64417373656D626C696573004D6963726F736F66742E53716C5365727665722E5365727665720053\
  161. 716C46756E6374696F6E417474726962757465004461746554696D65006765745F56616C75650054\
  162. 6F537472696E670000032000000000007A9C698E8B40FA4D93155AD83B1E444E0008B77A5C561934\
  163. E0890800021109110D110903200001042001010E05200101113D0420010108170100010054020F49\
  164. 7344657465726D696E697374696301042000114D0320000E0420010E0E040701114D1E0100195351\
  165. 4C323031375F54727573746564417373656D626C69657300004F01004A68747470733A2F2F53716C\
  166. 5175616E74756D4C6561702E636F6D2F63617465676F72792F70726F6772616D6D696E672F73716C\
  167. 636C722D76732D73716C2D7365727665722D323031372F00000501000000001501001053716C2051\
  168. 75616E74756D204C65617000002B01002653514C20536572766572203230313720767320434C5220\
  169. 73747269637420736563757269747900003B010036436F7079726967687420286329203230313720\
  170. 536F6C6F6D6F6E2052757A6B792E20416C6C207269676874732072657365727665642E0000120100\
  171. 0D323031372E382E32302E30303200000801000200000000000801000800000000001E0100010054\
  172. 0216577261704E6F6E457863657074696F6E5468726F77730100000000000000A4AD9F5900000000\
  173. 020000001C01000070270000700900005253445313CF1DF386A59840A3A3BFB3702BDE7401000000\
  174. 633A5C53514C5175616E74756D4C6561705C50726F6A656374735C426C6F67546F706963735C3231\
  175. 33305C53514C323031375F54727573746564417373656D626C6965735C53514C323031375F547275\
  176. 73746564417373656D626C6965735C6F626A5C52656C656173655C53514C323031375F5472757374\
  177. 6564417373656D626C6965732E706462000000000000000000000000000000000000000000000000\
  178. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  179. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  180. 0000000000000000000000000000000000000000B42800000000000000000000CE28000000200000\
  181. 0000000000000000000000000000000000000000C0280000000000000000000000005F436F72446C\
  182. 6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000\
  183. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  184. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  185. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  186. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  187. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  188. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  189. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  190. 00000000000001001000000018000080000000000000000000000000000001000100000030000080\
  191. 00000000000000000000000000000100000000004800000058400000AC0400000000000000000000\
  192. AC0434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000\
  193. BD04EFFE000001000800E107020014000800E107020014003F000000000000000400000002000000\
  194. 000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00\
  195. 000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004\
  196. 0C040000010053007400720069006E006700460069006C00650049006E0066006F000000E8030000\
  197. 0100300030003000300030003400620030000000B0004B00010043006F006D006D0065006E007400\
  198. 73000000680074007400700073003A002F002F00530071006C005100750061006E00740075006D00\
  199. 4C006500610070002E0063006F006D002F00630061007400650067006F00720079002F0070007200\
  200. 6F006700720061006D006D0069006E0067002F00730071006C0063006C0072002D00760073002D00\
  201. 730071006C002D007300650072007600650072002D0032003000310037002F000000000044001100\
  202. 010043006F006D00700061006E0079004E0061006D00650000000000530071006C00200051007500\
  203. 61006E00740075006D0020004C00650061007000000000005C001A000100460069006C0065004400\
  204. 650073006300720069007000740069006F006E0000000000530051004C0032003000310037005F00\
  205. 540072007500730074006500640041007300730065006D0062006C0069006500730000003C000E00\
  206. 0100460069006C006500560065007200730069006F006E000000000032003000310037002E003800\
  207. 2E00320030002E0030003000320000005C001E00010049006E007400650072006E0061006C004E00\
  208. 61006D0065000000530051004C0032003000310037005F0054007200750073007400650064004100\
  209. 7300730065006D0062006C006900650073002E0064006C006C0000009400370001004C0065006700\
  210. 61006C0043006F007000790072006900670068007400000043006F00700079007200690067006800\
  211. 7400200028006300290020003200300031003700200053006F006C006F006D006F006E0020005200\
  212. 75007A006B0079002E00200041006C006C0020007200690067006800740073002000720065007300\
  213. 650072007600650064002E000000000064001E0001004F0072006900670069006E0061006C004600\
  214. 69006C0065006E0061006D0065000000530051004C0032003000310037005F005400720075007300\
  215. 74006500640041007300730065006D0062006C006900650073002E0064006C006C00000070002700\
  216. 0100500072006F0064007500630074004E0061006D00650000000000530051004C00200053006500\
  217. 720076006500720020003200300031003700200076007300200043004C0052002000730074007200\
  218. 6900630074002000730065006300750072006900740079000000000040000E000100500072006F00\
  219. 6400750063007400560065007200730069006F006E00000032003000310037002E0038002E003200\
  220. 30002E00300030003200000040000C00010041007300730065006D0062006C007900200056006500\
  221. 7200730069006F006E00000032003000310037002E0038002E00320030002E003100000000000000\
  222. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  223. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  224. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  225. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  226. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  227. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  228. 0000000000000000002000000C000000E03800000000000000000000000000000000000000000000\
  229. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  230. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  231. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  232. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  233. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  234. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  235. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  236. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  237. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  238. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  239. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  240. 00000000000000000000000000000000000000000000000000000000000000000000000000000000
  241.             WITH PERMISSION_SET = SAFE;
  242.     END;
  243.  
  244.     PRINT ' Altering Database to set TRUSTWORTHY back to OFF...';
  245.     ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  246.         SET TRUSTWORTHY OFF;
  247.  
  248.     PRINT N'    Creating [dbo].[Date_Format]...';
  249.     EXEC(N'
  250. CREATE FUNCTION [dbo].[Date_Format]
  251. (
  252.     @DateToFormat DATETIME,
  253.     @FormatString NVARCHAR(500)
  254. )
  255. RETURNS NVARCHAR(MAX)
  256. WITH    EXECUTE AS CALLER,
  257.         RETURNS NULL ON NULL INPUT
  258. AS EXTERNAL NAME [SQL2017_TrustedAssemblies].[SQL2017vsSQLCLR].[Date_Format];
  259. ');
  260.  
  261.     --PRINT '   Altering Database to set TRUSTWORTHY back to OFF...';
  262.     --ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  263.     --  SET TRUSTWORTHY OFF;
  264. END;
  265. GO
  266.  
  267. -- TEST A
  268. -- Should error due to:
  269. --   1) "clr strict security" is enabled
  270. --   2) TRUSTWORTHY is disabled
  271. --   3) Assembly is not signed (hence no signature-based Login)
  272. --   4) This is not a "Trusted" Assembly
  273.  
  274. PRINT CHAR(13) + CHAR(10) + 'Running test A...';
  275. GO
  276. SELECT dbo.Date_Format('2017-08-12 14:15:16.789', N'~~ yyyy ~~ d ~~ MMM ~~ mm ~~') AS [DateFormatOutput];
  277. -- error:
  278. /*
  279. Msg 10314, Level 16, State 11, Server osboxes, Line 1
  280. An error occurred in the Microsoft .NET Framework while trying to load assembly id 65546. The server may be running out of
  281.    resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the
  282.    assembly trust issues. For more information about this error:
  283. System.IO.FileLoadException: Could not load file or assembly 'sql2017_trustedassemblies, Version=0.0.0.0, Culture=neutral,
  284.    PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
  285. System.IO.FileLoadException:
  286.    at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly
  287.       locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection,
  288.       Boolean suppressSecurityChecks)
  289.    at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence asse
  290. */
  291.  
  292. GO
  293.  
  294. -- SETUP FOR TEST B (B1 and B2)
  295. -- 1) Create Certificate
  296. -- 2) Sign Assembly
  297. -- 3) Copy Certificate to [master] (Public Key only!)
  298. -- 4) Create Login from Certificate
  299.  
  300.  
  301. -- DROP SIGNATURE FROM Assembly::[SQL2017_TrustedAssemblies] BY CERTIFICATE [SQL2017-TrustedAssemblies];
  302. -- DROP CERTIFICATE [SQL2017-TrustedAssemblies-Cert];
  303.  
  304.  
  305. IF (SUSER_ID(N'SQL2017-TrustedAssemblies-Login') IS NULL)
  306. BEGIN
  307.     PRINT CHAR(13) + CHAR(10) + 'Permission Login does NOT exist:';
  308.  
  309.     IF (CERT_ID(N'SQL2017-TrustedAssemblies-Cert') IS NULL)
  310.     BEGIN
  311.         PRINT ' Creating Certificate in [SQLCLRvsClrStrictSecurity]...';
  312.         CREATE CERTIFICATE [SQL2017-TrustedAssemblies-Cert]
  313.             ENCRYPTION BY PASSWORD = 'YaddaYaddaYadda!'
  314.             WITH SUBJECT = 'Sql Quantum Leap',
  315.             EXPIRY_DATE = '2099-12-31';
  316.     END;
  317.  
  318.     IF (NOT EXISTS(
  319.                     SELECT *
  320.                     FROM  sys.crypt_properties cp
  321.                     INNER JOIN sys.assemblies sa
  322.                             ON sa.[assembly_id] = cp.[major_id]
  323.                     WHERE sa.[name] = N'SQL2017_TrustedAssemblies'
  324.                 ))
  325.     BEGIN
  326.         PRINT ' Signing the Assembly...';
  327.         ADD SIGNATURE
  328.             TO Assembly::[SQL2017_TrustedAssemblies]
  329.             BY CERTIFICATE [SQL2017-TrustedAssemblies-Cert]
  330.             WITH PASSWORD = 'YaddaYaddaYadda!';
  331.  
  332.     END;       
  333.  
  334.     IF (NOT EXISTS(
  335.                     SELECT *
  336.                     FROM   [master].[sys].[certificates] crt
  337.                     WHERE  crt.[name] = N'SQL2017-TrustedAssemblies-Cert'
  338.                 ))
  339.     BEGIN
  340.         PRINT ' Copying the Certificate to [master]...';
  341.         DECLARE @PublicKey VARBINARY(MAX),
  342.                 @SQL NVARCHAR(MAX);
  343.  
  344.         SET @PublicKey = CERTENCODED(CERT_ID(N'SQL2017-TrustedAssemblies-Cert'));
  345.  
  346.         SET @SQL = N'
  347. CREATE CERTIFICATE [SQL2017-TrustedAssemblies-Cert]
  348.     FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
  349.         --SELECT @PublicKey AS [@PublicKey]; -- DEBUG
  350.         --PRINT @SQL; -- DEBUG
  351.  
  352.         EXEC [master].[sys].[sp_executesql] @SQL;
  353.     END;
  354.  
  355.  
  356.     PRINT ' Creating permissions Login...';
  357.     EXEC [master].[sys].[sp_executesql] N'
  358. CREATE LOGIN [SQL2017-TrustedAssemblies-Login]
  359.     FROM CERTIFICATE [SQL2017-TrustedAssemblies-Cert];
  360. ';
  361. END;
  362. GO
  363.  
  364.  
  365. -- TEST B1
  366. -- Should error due to:
  367. --   1) "clr strict security" is enabled
  368. --   2) TRUSTWORTHY is disabled
  369. --   3) Signature-based Login does not have "UNSAFE ASSEMBLY" permission
  370. --   4) This is not a "Trusted" Assembly
  371.  
  372. PRINT CHAR(13) + CHAR(10) + 'Running test B1...';
  373. GO
  374. SELECT dbo.Date_Format('2017-08-12 14:15:16.789', N'~~ yyyy ~~ d ~~ MMM ~~ mm ~~') AS [DateFormatOutput];
  375. -- error
  376. /*
  377.     { same as shown above for Test A }
  378. */
  379.  
  380. GO
  381.  
  382. -- SETUP FOR TEST B2
  383. -- 5) Grant Login UNSAFE ASSEMBLY permission
  384.  
  385. PRINT CHAR(13) + CHAR(10) + 'Granting UNSAFE ASSEMBLY permission...';
  386. -- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-TrustedAssemblies-Login];
  387. EXEC [master].[sys].[sp_executesql] N'
  388. GRANT UNSAFE ASSEMBLY TO [SQL2017-TrustedAssemblies-Login]; -- REQUIRED!!!!
  389. ';
  390.  
  391.  
  392. PRINT CHAR(13) + CHAR(10) + 'Running test B2...';
  393. GO
  394. SELECT dbo.Date_Format('2017-08-12 14:15:16.789', N'~~ yyyy ~~ d ~~ MMM ~~ mm ~~') AS [DateFormatOutput];
  395.  
  396. /* RETURNS:
  397.  
  398. ~~ 2017 ~~ 12 ~~ Aug ~~ 15 ~~
  399.  
  400. */
  401.  
  402.  
  403. GO
  404.  
  405. PRINT '';
  406. SELECT ASSEMBLYPROPERTY(N'SQL2017_TrustedAssemblies', 'CLRName') AS [CLRName];
  407. -- sql2017_trustedassemblies, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
  408.  
  409. -----------------------------------------------------------
  410. -- Clean up:
  411.  
  412. PRINT CHAR(13) + CHAR(10) + 'Cleaning up objects (to be re-runnable)...';
  413.  
  414. USE [SQLCLRvsClrStrictSecurity];
  415. DROP FUNCTION [dbo].[Date_Format];
  416. DROP ASSEMBLY [SQL2017_TrustedAssemblies];
  417. DROP CERTIFICATE [SQL2017-TrustedAssemblies-Cert];
  418.  
  419. USE [master];
  420. DROP LOGIN [SQL2017-TrustedAssemblies-Login];
  421. DROP CERTIFICATE [SQL2017-TrustedAssemblies-Cert];
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top