API tools faq
paste
Advertisement
SqlQuantumLeap

Avoiding "Trusted Assemblies" - Demo

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