SqlQuantumLeap

"clr strict security" - Solution 2

Aug 20th, 2017
385
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  --------- TEST 2 FOR SQLCLR on SQL Server 2017 (Windows and Linux) ---------
  3.  
  4. By: Solomon Rutzky
  5. On: 2017-08-10
  6. Location: https://pastebin.com/2nw9Kyfe
  7. Related blog post: https://SqlQuantumLeap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/
  8.  
  9. Description: This script shows a technique for deploying SQLCLR Assemblies to SQLCLR 2017
  10.              while both keeping "clr strict security" enabled and "TRUSTWORTHY" disabled,
  11.              AND while being fulled contained within a SQL script, AND working with SSDT.
  12.  
  13.              The trick is to override the default SSDT build process to add a step to sign
  14.              the DLL with a certificate before the DLL is pulled into a deployment script.
  15.  
  16.              For a detailed, step-by-step explanation, please see the relatd blog post
  17.              noted above.
  18. */
  19.  
  20.  
  21.  
  22. USE [master];
  23. SET NOCOUNT ON;
  24. GO
  25.  
  26. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NULL)
  27. BEGIN
  28.     PRINT 'Creating Database...';
  29.     CREATE DATABASE [SQLCLRvsClrStrictSecurity]
  30.         COLLATE Latin1_General_100_CI_AS;
  31. END;
  32.  
  33. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NOT NULL)
  34. BEGIN
  35.     PRINT 'Altering Database (make sure RECOVERY=SIMPLE and TRUSTWORTHY=OFF)...';
  36.     ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  37.         SET RECOVERY SIMPLE,
  38.         TRUSTWORTHY OFF;
  39. END;
  40.  
  41.  
  42. PRINT 'Making sure that SQLCLR is enabled...';
  43. IF (EXISTS(
  44.             SELECT *
  45.             FROM   sys.configurations sc
  46.             WHERE  sc.[configuration_id] = 1562 -- "clr enabled"
  47.             AND    sc.[value_in_use] = 0
  48.     ))
  49. BEGIN
  50.     PRINT 'Enabling SQLCLR...';
  51.     EXEC sp_configure N'clr enabled', 1; RECONFIGURE;
  52. END;
  53.  
  54. PRINT 'Making sure that SQLCLR "strict security" is enabled...';
  55. IF (EXISTS(
  56.             SELECT *
  57.             FROM   sys.configurations sc
  58.             WHERE  sc.[configuration_id] = 518 -- "show advanced options"
  59.             AND    sc.[value_in_use] = 0
  60.     ))
  61. BEGIN
  62.     PRINT ' Showing advanced options...';
  63.     EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
  64. END;
  65.  
  66.  
  67. IF (EXISTS(
  68.             SELECT *
  69.             FROM   sys.configurations sc
  70.             WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
  71.             AND    sc.[value_in_use] = 0
  72.     ))
  73. BEGIN
  74.     PRINT ' Enabling CLR strict security...';
  75.     EXEC sp_configure N'clr strict security', 1; RECONFIGURE;
  76. END;
  77.  
  78.  
  79.  
  80.  
  81.  
  82. IF (SUSER_ID(N'SQL2017-ClrStrictSecurity2-Login') IS NULL)
  83. BEGIN
  84.     PRINT 'Permission Login does NOT exist:';
  85.  
  86.     IF (CERT_ID(N'SQL2017-ClrStrictSecurity2-Cert') IS NULL)
  87.     BEGIN
  88.         PRINT ' Creating Certificate...';
  89.         CREATE CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert]
  90. FROM BINARY = 0x\
  91. 3082037930820265A00302010202109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D\
  92. 05003040310B3009060355040613025553311B3019060355040A131253716C5175616E74756D4C65\
  93. 61702E636F6D311430120603550403130B53514C434C5220436572743020170D3137303831383033\
  94. 313030345A180F32303939313233313034303030305A3040310B3009060355040613025553311B30\
  95. 19060355040A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C\
  96. 434C52204365727430820122300D06092A864886F70D01010105000382010F003082010A02820101\
  97. 00CCEA3F8D614CE7AB2B52E39301A9D9E2A7828473EC10D8748CF0BB32DDF6ED1423E56C0FC36DAA\
  98. 995D54BD7194BF3BC71B1931111706DF162ED5188017B0B570DDF9AFFD3E531E266BFADD02220253\
  99. 53277BAC965E89DB4645438A973256897E0D2644C24CB3C61AC0011FC7CDDFE06C1234C1C0A60158\
  100. 6952FF2033C58F8A519AD1D71B35A6975E1F846A83D1F90D1602A372CC8EB697240E9E38852BBB96\
  101. CB78FE296B57DE401AAFD958EDDF59894E8C0A42B95A77BE43C1B893CE312824B68C931F9893639A\
  102. 41BF99E68AD6F2AA2AEC58AA5CC067EF8FF0AD7BE9A1AA44B48F8338BBD324C2D162DA30356FD461\
  103. BF2FD388EDDFDD5990682379EF18A33C4D0203010001A375307330710603551D01046A30688010B7\
  104. C292D45B0F3FDDBF55241F2FD6F2CFA1423040310B3009060355040613025553311B301906035504\
  105. 0A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C522043\
  106. 65727482109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D050003820101006AF2D8\
  107. F382D55034DE7715B0F5536669914C6B256344B7343B0B099B32B4759E77B367CA19A3B3FBC7DBCF\
  108. 913FB022DD5D4F83EB6792D58B63AF015767F0BC059D24D3E019880A95DEA9E72BD462302806CF9B\
  109. FB1C9A3E3BAC4D612889ACF4839AC0FE7F60625FA33DABA6F0FCD6EBF88401712EF3DA505092CBB5\
  110. ACCDC510258F3003868DC15C0E5374B01E6BF555972E3F99C789F1E14B171CE508D895CDD71AB6FD\
  111. 8D9F61962A3E1D89E0E748E3C5505AF21E0DF6D95FEA86E87D5DED7D00A669FC8CAB472A93F993BB\
  112. B21636AEDE56F747E11FCF69F96D4C87732C5BE3780E01C772F3BD0938DD5A440BDCC84680839572\
  113. 8AF10C95C6F8E6714DDAFA19B2
  114.     END;
  115.  
  116.     IF (SUSER_ID(N'SQL2017-ClrStrictSecurity2-Login') IS NULL)
  117.     BEGIN
  118.         PRINT ' Creating permissions Login...';
  119.         CREATE LOGIN [SQL2017-ClrStrictSecurity2-Login]
  120.             FROM CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];
  121.     END;
  122. END;
  123.  
  124. -- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity2-Login];
  125. GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity2-Login]; -- REQUIRED!!!!
  126.  
  127.  
  128. --DROP LOGIN [SQL2017-ClrStrictSecurity2-Login];
  129. --DROP CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];
  130.  
  131.  
  132. -- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
  133. SELECT *
  134. FROM   sys.configurations sc
  135. WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
  136.  
  137. SELECT [name], [is_trustworthy_on], [collation_name]
  138. FROM sys.databases WHERE [database_id] = DB_ID(N'SQLCLRvsClrStrictSecurity');
  139.  
  140.  
  141. GO
  142. USE [SQLCLRvsClrStrictSecurity];
  143. -- DROP FUNCTION [dbo].[String_PadLeft];
  144. -- DROP ASSEMBLY [SQL2017_ClrStrictSecurity2];
  145.  
  146.  
  147. IF (ASSEMBLYPROPERTY(N'SQL2017_ClrStrictSecurity2', N'MvID') IS NULL)
  148. BEGIN
  149.     PRINT 'Creating [SQL2017_ClrStrictSecurity2] Assembly...';
  150.     CREATE ASSEMBLY [SQL2017_ClrStrictSecurity2]
  151.         AUTHORIZATION [dbo]
  152.         FROM 0x\
  153. 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
  154. 0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
  155. 69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
  156. 2400000000000000504500004C01030082F498590000000000000000E00002210B010B00000A0000\
  157. 0008000000000000CE28000000200000004000000000001000200000000200000400000000000000\
  158. 06000000000000000080000000020000247B00000300608500001000001000000000100000100000\
  159. 00000000100000000000000000000000802800004B00000000400000980400000000000000000000\
  160. 00140000D8050000006000000C000000482700001C00000000000000000000000000000000000000\
  161. 00000000000000000000000000000000000000000000000000200000080000000000000000000000\
  162. 082000004800000000000000000000002E74657874000000D408000000200000000A000000020000\
  163. 000000000000000000000000200000602E72737263000000980400000040000000060000000C0000\
  164. 000000000000000000000000400000402E72656C6F6300000C000000006000000002000000120000\
  165. 0000000000000000000000004000004200000000000000000000000000000000B028000000000000\
  166. 48000000020005007C200000CC060000010000000000000000000000000000000000000000000000\
  167. 0000000000000000000000000000000000000000000000000000000000000000860F00280F00000A\
  168. 0F02281000000A03166A6F1100000A6F1200000A731300000A2A1E02281400000A2A000042534A42\
  169. 01000100000000000C00000076342E302E33303331390000000005006C00000008020000237E0000\
  170. 74020000E402000023537472696E6773000000005805000008000000235553006005000010000000\
  171. 2347554944000000700500005C01000023426C6F6200000000000000020000014715000009000000\
  172. 00FA2533001600000100000014000000020000000200000003000000140000000C00000001000000\
  173. 0200000000000A0001000000000006004E0047000A00760061000A00800061000A00890061000600\
  174. D700C5000600EE00C50006000B01C50006002A01C50006004301C50006005C01C50006007701C500\
  175. 06009201C5000600AB01C5000600C401C5000600F401E1013F000802000006003702170206005702\
  176. 17020A00AB0290020600D30247000000000001000000000001000100010010002900000005000100\
  177. 0100502000000000960092000A0001007220000000008618A1001500040000000100A70000000200\
  178. B30000000300BB002900A10019003100A10019003900A10019004100A10019004900A10019005100\
  179. A10019005900A10019006100A10019006900A10019007100A10019007900A1001E008900A1002400\
  180. 9100A10015009900A10015001100C00241002100C00245001900CA024900A100DA024E001100A100\
  181. 19000900A10015002000730029002E002B00B5002E00130074002E001B0099002E0023009F002E00\
  182. 0B0054002E003300E1002E003B0099002E0053001D012E005B002A012E00630033012E006B003C01\
  183. 0480000002000600070000000000000000007502000004000000000000000000000001003E000000\
  184. 000004000000000000000000000001005500000000000000003C4D6F64756C653E0053514C323031\
  185. 375F436C725374726963745365637572697479322E646C6C0055736572446566696E656446756E63\
  186. 74696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E44617461\
  187. 0053797374656D2E446174612E53716C54797065730053716C537472696E670053716C4368617273\
  188. 0053716C496E74333200537472696E675F5061644C656674002E63746F7200496E70757453747269\
  189. 6E67005061644368617200506164416D6F756E740053797374656D2E5265666C656374696F6E0041\
  190. 7373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F\
  191. 6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574\
  192. 6500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475\
  193. 637441747472696275746500417373656D626C79436F707972696768744174747269627574650041\
  194. 7373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572\
  195. 6541747472696275746500417373656D626C7956657273696F6E4174747269627574650041737365\
  196. 6D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F7374\
  197. 6963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053\
  198. 797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C617469\
  199. 6F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C\
  200. 6974794174747269627574650053514C323031375F436C7253747269637453656375726974793200\
  201. 4D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174\
  202. 74726962757465006765745F56616C7565006765745F4974656D00537472696E67005061644C6566\
  203. 740000000003200000000000F41C3A78FF4C4A49B5F1F9B95DE8B0F70008B77A5C561934E0890A00\
  204. 0311091109120D111103200001042001010E0520010111410420010108170100010054020F497344\
  205. 657465726D696E6973746963010320000E03200008042001030A0520020E08031F01001A53514C32\
  206. 3031375F436C7253747269637453656375726974793200002401001F68747470733A2F2F53716C51\
  207. 75616E74756D4C6561702E636F6D2F3230313700000501000000001501001053716C205175616E74\
  208. 756D204C65617000002B01002653514C20536572766572203230313720767320434C522073747269\
  209. 637420736563757269747900003B010036436F7079726967687420286329203230313720536F6C6F\
  210. 6D6F6E2052757A6B792E20416C6C207269676874732072657365727665642E00000C010007332E38\
  211. 2E392E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E45\
  212. 7863657074696F6E5468726F777301000000000082F4985900000000020000001C01000064270000\
  213. 64090000525344535AC21935AE54774C9B9CF9BD052243AB08000000633A5C53514C5175616E7475\
  214. 6D4C6561705C50726F6A656374735C426C6F67546F706963735C313830335C53514C323031375F43\
  215. 6C725374726963745365637572697479325C53514C323031375F436C725374726963745365637572\
  216. 697479325C6F626A5C52656C656173655C53514C323031375F436C72537472696374536563757269\
  217. 7479322E706462000000000000000000000000000000000000000000000000000000000000000000\
  218. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  219. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  220. 0000000000000000A82800000000000000000000BE28000000200000000000000000000000000000\
  221. 0000000000000000B02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C\
  222. 6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000\
  223. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  224. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  225. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  226. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  227. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  228. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  229. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  230. 00000000000001001000000018000080000000000000000000000000000001000100000030000080\
  231. 00000000000000000000000000000100000000004800000058400000400400000000000000000000\
  232. 400434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000\
  233. BD04EFFE00000100080003000000090008000300000009003F000000000000000400000002000000\
  234. 000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00\
  235. 000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004\
  236. A0030000010053007400720069006E006700460069006C00650049006E0066006F0000007C030000\
  237. 010030003000300030003000340062003000000058002000010043006F006D006D0065006E007400\
  238. 73000000680074007400700073003A002F002F00530071006C005100750061006E00740075006D00\
  239. 4C006500610070002E0063006F006D002F003200300031003700000044001100010043006F006D00\
  240. 700061006E0079004E0061006D00650000000000530071006C0020005100750061006E0074007500\
  241. 6D0020004C006500610070000000000060001B000100460069006C00650044006500730063007200\
  242. 69007000740069006F006E0000000000530051004C0032003000310037005F0043006C0072005300\
  243. 74007200690063007400530065006300750072006900740079003200000000003000080001004600\
  244. 69006C006500560065007200730069006F006E000000000033002E0038002E0039002E0030000000\
  245. 60001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C003200\
  246. 3000310037005F0043006C0072005300740072006900630074005300650063007500720069007400\
  247. 790032002E0064006C006C00000000009400370001004C006500670061006C0043006F0070007900\
  248. 72006900670068007400000043006F00700079007200690067006800740020002800630029002000\
  249. 3200300031003700200053006F006C006F006D006F006E002000520075007A006B0079002E002000\
  250. 41006C006C0020007200690067006800740073002000720065007300650072007600650064002E00\
  251. 0000000068001F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00\
  252. 65000000530051004C0032003000310037005F0043006C0072005300740072006900630074005300\
  253. 650063007500720069007400790032002E0064006C006C0000000000700027000100500072006F00\
  254. 64007500630074004E0061006D00650000000000530051004C002000530065007200760065007200\
  255. 20003200300031003700200076007300200043004C00520020007300740072006900630074002000\
  256. 7300650063007500720069007400790000000000340008000100500072006F006400750063007400\
  257. 560065007200730069006F006E00000033002E0038002E0039002E00300000003800080001004100\
  258. 7300730065006D0062006C0079002000560065007200730069006F006E00000032002E0036002E00\
  259. 37002E00300000000000000000000000000000000000000000000000000000000000000000000000\
  260. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  261. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  262. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  263. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  264. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  265. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  266. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  267. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  268. 0000000000000000002000000C000000D03800000000000000000000000000000000000000000000\
  269. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  270. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  271. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  272. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  273. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  274. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  275. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  276. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  277. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  278. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  279. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  280. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  281. D805000000020200308205C506092A864886F70D010702A08205B6308205B2020101310B30090605\
  282. 2B0E03021A0500304C060A2B060104018237020104A03E303C3017060A2B06010401823702010F30\
  283. 09030100A004A20280003021300906052B0E03021A0500041443D232A77578792F67D9469EBEE29E\
  284. B6D88FF59AA082037D3082037930820265A00302010202109BCC3B23A3549F9645D0A425322CF7FD\
  285. 300906052B0E03021D05003040310B3009060355040613025553311B3019060355040A131253716C\
  286. 5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C522043657274302017\
  287. 0D3137303831383033313030345A180F32303939313233313034303030305A3040310B3009060355\
  288. 040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D3114301206\
  289. 03550403130B53514C434C52204365727430820122300D06092A864886F70D01010105000382010F\
  290. 003082010A0282010100CCEA3F8D614CE7AB2B52E39301A9D9E2A7828473EC10D8748CF0BB32DDF6\
  291. ED1423E56C0FC36DAA995D54BD7194BF3BC71B1931111706DF162ED5188017B0B570DDF9AFFD3E53\
  292. 1E266BFADD0222025353277BAC965E89DB4645438A973256897E0D2644C24CB3C61AC0011FC7CDDF\
  293. E06C1234C1C0A601586952FF2033C58F8A519AD1D71B35A6975E1F846A83D1F90D1602A372CC8EB6\
  294. 97240E9E38852BBB96CB78FE296B57DE401AAFD958EDDF59894E8C0A42B95A77BE43C1B893CE3128\
  295. 24B68C931F9893639A41BF99E68AD6F2AA2AEC58AA5CC067EF8FF0AD7BE9A1AA44B48F8338BBD324\
  296. C2D162DA30356FD461BF2FD388EDDFDD5990682379EF18A33C4D0203010001A37530733071060355\
  297. 1D01046A30688010B7C292D45B0F3FDDBF55241F2FD6F2CFA1423040310B30090603550406130255\
  298. 53311B3019060355040A131253716C5175616E74756D4C6561702E636F6D31143012060355040313\
  299. 0B53514C434C52204365727482109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D05\
  300. 0003820101006AF2D8F382D55034DE7715B0F5536669914C6B256344B7343B0B099B32B4759E77B3\
  301. 67CA19A3B3FBC7DBCF913FB022DD5D4F83EB6792D58B63AF015767F0BC059D24D3E019880A95DEA9\
  302. E72BD462302806CF9BFB1C9A3E3BAC4D612889ACF4839AC0FE7F60625FA33DABA6F0FCD6EBF88401\
  303. 712EF3DA505092CBB5ACCDC510258F3003868DC15C0E5374B01E6BF555972E3F99C789F1E14B171C\
  304. E508D895CDD71AB6FD8D9F61962A3E1D89E0E748E3C5505AF21E0DF6D95FEA86E87D5DED7D00A669\
  305. FC8CAB472A93F993BBB21636AEDE56F747E11FCF69F96D4C87732C5BE3780E01C772F3BD0938DD5A\
  306. 440BDCC846808395728AF10C95C6F8E6714DDAFA19B2318201CF308201CB02010130543040310B30\
  307. 09060355040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D31\
  308. 1430120603550403130B53514C434C52204365727402109BCC3B23A3549F9645D0A425322CF7FD30\
  309. 0906052B0E03021A0500A0523010060A2B06010401823702010C31023000301906092A864886F70D\
  310. 010903310C060A2B060104018237020104302306092A864886F70D0109043116041470E1D5141ADB\
  311. 71E29F00A5D80E2A58B2BCBA01D4300D06092A864886F70D010101050004820100AF02DE1136A6CE\
  312. 693A4C46F9DBB494FD7FE687F0C014A75B5A8DEB0D6B7A0CB967C42A09B743E0124891B3598FADAB\
  313. A6DF6812378F15A0B9C7B8AB076A90D320BAFC220F5438C4204E2A5A770DDD93AB2E4AD324198659\
  314. 9C991196FBC115ACFBB5B492B21F2FB6DCB89D4B61E2E19C716693C6A2F9DE12572E259CBFD48238\
  315. 003FF893D95D3EC5C025CD9323147A0D4BEAB0F2C430C9F1C0B035FA983FF62E7F840723B9B7DABC\
  316. D580FC3347C1173DFDF56A94EC5498FFB11D0F6816A3E0432E986327CC6A2CC9858CDD79C39E6C8F\
  317. 929CCD8DE79B7C0C6EC77556E7EFAF79E2A2B79CE11AE5F4A9EC459E1C290E7935CAB66AD1B957FE\
  318. 8D4829365E24549A9F00000000000000
  319.         WITH PERMISSION_SET = SAFE;
  320. END;
  321.  
  322.  
  323. IF (OBJECT_ID(N'dbo.String_PadLeft') IS NULL)
  324. BEGIN
  325.     PRINT N'Creating [dbo].[String_PadLeft]...';
  326.     EXEC(N'
  327. CREATE FUNCTION [dbo].[String_PadLeft]
  328. (
  329.  @InputString NVARCHAR(4000),
  330.  @PadChar NVARCHAR(1),
  331.  @PadAmount INT
  332. )
  333. RETURNS NVARCHAR(4000)
  334. WITH RETURNS NULL ON NULL INPUT
  335. AS EXTERNAL NAME [SQL2017_ClrStrictSecurity2].[UserDefinedFunctions].[String_PadLeft];
  336. ');
  337. END;
  338. GO
  339.  
  340.  
  341. PRINT 'Running three tests...';
  342. SELECT dbo.String_PadLeft(N'3456', N'~', 7); -- ~~~3456
  343. SELECT dbo.String_PadLeft(N'34567', N'+', 3); -- 34567
  344. SELECT dbo.String_PadLeft(NULL, N'f', 5); -- NULL
  345.  
  346.  
  347. PRINT 'Removing UNSAFE ASSEMBLY permission...';
  348. EXEC (N'
  349. USE [master];
  350.  
  351. REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity2-Login];
  352. ');
  353.  
  354.  
  355. PRINT 'Re-running first test (it should error in SQL Server 2017)...';
  356. SELECT dbo.String_PadLeft(N'3456', N'~', 7); -- ~~~3456
  357.  
  358. /* RETURNS:
  359.  
  360. ~~~3456
  361. 34567
  362. NULL
  363.  
  364. */
  365. GO
  366.  
  367. SELECT ASSEMBLYPROPERTY(N'sql2017_clrstrictsecurity2', 'CLRName') AS [CLRName];
  368. -- sql2017_clrstrictsecurity2, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
RAW Paste Data