SHARE
TWEET

"clr strict security" - Solution 1

SqlQuantumLeap Aug 15th, 2017 (edited) 418 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  --------- TEST FOR SQLCLR on SQL Server 2017 (Windows and Linux) ---------
  3.  
  4. By: Solomon Rutzky
  5. On: 2017-08-04
  6. Location: https://pastebin.com/vY9GgH8F
  7. Related blog post: https://SqlQuantumLeap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/
  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 first create a separate Project / Assembly that is empty but
  14.              is also signed. That will be used to create the Asymmetric Key in [master].
  15.  
  16.              The second part of the trick is to also sign that empty Assembly with a
  17.              Certificate. This will allow for the empty Assembly to be created.
  18.  
  19.              For a detailed, step-by-step explanation, please see the relatd blog post
  20.              noted above.
  21. */
  22.  
  23.  
  24.  
  25. USE [master];
  26. SET NOCOUNT ON;
  27. GO
  28.  
  29. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NULL)
  30. BEGIN
  31.     PRINT 'Creating Database...';
  32.     CREATE DATABASE [SQLCLRvsClrStrictSecurity]
  33.         COLLATE Latin1_General_100_CI_AS;
  34. END;
  35.  
  36. IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NOT NULL)
  37. BEGIN
  38.     PRINT 'Altering Database...';
  39.     ALTER DATABASE [SQLCLRvsClrStrictSecurity]
  40.         SET RECOVERY SIMPLE,
  41.         TRUSTWORTHY OFF;
  42. END;
  43.  
  44.  
  45. PRINT 'Making sure that SQLCLR is enabled...';
  46. EXEC sp_configure N'clr enabled', 1; RECONFIGURE;
  47.  
  48. PRINT 'Making sure that SQLCLR "strict security" is enabled...';
  49. EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
  50. EXEC sp_configure N'clr strict security', 1; RECONFIGURE;
  51.  
  52.  
  53.  
  54.  
  55. IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-Login') IS NULL)
  56. BEGIN
  57.     PRINT 'Permission Login does NOT exist:';
  58.  
  59.     IF (CERT_ID(N'SQL2017-ClrStrictSecurity-Cert') IS NULL)
  60.     BEGIN
  61.         PRINT 'Creating Certificate...';
  62.         CREATE CERTIFICATE [SQL2017-ClrStrictSecurity-Cert]
  63. FROM BINARY = 0x\
  64. 30820310308201FCA0030201020210170A6F5B690D95A0402670F83588A54F300906052B0E03021D\
  65. 0500301D311B30190603550403131253716C5175616E74756D4C6561702E636F6D3020170D313730\
  66. 3830353037313133335A180F32303939313233313034303030305A301D311B301906035504031312\
  67. 53716C5175616E74756D4C6561702E636F6D30820122300D06092A864886F70D0101010500038201\
  68. 0F003082010A0282010100C3F1C860C555A61F510AD500B185FA9308E3B3D5A64EBA56B2002329EE\
  69. 2F3D9B7A8FD803E21668285EC2C18D4018F211329145C201B827782FB086A6688B08EFCD80D915F3\
  70. A8BEA4550206F1232A0086D154632BC8FDE09F5B24EBA25598F2F5DA255093D1C03E8D67F2F305FE\
  71. AFF09EEFC7F1FAD2D6A06D65EF2AA9168802A254B77E62EFD117EF466C3293E1DB6FB8E51BE618AA\
  72. 6D7B1C58D05712962C17A123DD554EFF6496E9A1FB8C24C706B6AEC7BAFE606B46E31DD5CDDE23A0\
  73. 71DF30D181BEC9416F1B52AF5FF9AEF15A93FF4D79427AF76EF1BC85B3581A7C55FC87446173F2A0\
  74. 6DBD96BD59BBD74BF7C8997F8CF0AC8A8FE44AA477E2450681AD670203010001A3523050304E0603\
  75. 551D01044730458010A817C71654DDEDACE2F9E5DDE3786D5FA11F301D311B301906035504031312\
  76. 53716C5175616E74756D4C6561702E636F6D8210170A6F5B690D95A0402670F83588A54F30090605\
  77. 2B0E03021D050003820101001734E296A9978F17108A96354A40D664E46FD32E76227B9378AEE1BF\
  78. 6189FB4ED1F638A96775EE2613D3FFCBDECAFD8B265BBDDD3841C541F4DC842C8807A0A81469990C\
  79. EBEA093ABCD5A1FD0ABDE391B3F068B8D42F7A1F1E467B39A610D4C60CDFFEE138CA6610935FD7F5\
  80. 26CF3195ED2940FF79FA9D97103C9859526C749471046C9C325CE00100371E9EABF520EC70ADDD1C\
  81. D298DCC8E2F2C6851B2627FBFAB60B868EC57C3E702C0E58F5741E3EFEA997345D395D70E2249E2D\
  82. 324698853B3BAD0DE28E81B74C386C6A16F41F1F2B2E078D22A90D56669C7F3BD532D363C1BA54FD\
  83. DF30825A25A27ED010FF5353D10586EF4622CA67E736E4C05162D80C
  84.     END;
  85.  
  86.     IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-TempLogin') IS NULL)
  87.     BEGIN
  88.         PRINT 'Temporary Login does NOT exist:';
  89.         CREATE LOGIN [SQL2017-ClrStrictSecurity-TempLogin]
  90.             FROM CERTIFICATE [SQL2017-ClrStrictSecurity-Cert];
  91.     END;
  92.  
  93.     -- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity-TempLogin];
  94.     GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity-TempLogin]; -- REQUIRED!!!!
  95.  
  96.     IF (ASSEMBLYPROPERTY(N'SQL2017-ClrStrictSecurity-KeyAsm', N'MvID') IS NULL)
  97.     BEGIN
  98.         PRINT ' Creating Assembly that holds the Asymmetric Key...';
  99.         CREATE ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm]
  100.             FROM 0x\
  101. 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
  102. 0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
  103. 69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
  104. 2400000000000000504500004C010300505085590000000000000000E00002210B010B0000080000\
  105. 0008000000000000CE27000000200000004000000000001000200000000200000400000000000000\
  106. 06000000000000000080000000020000E09600000300608500001000001000000000100000100000\
  107. 00000000100000000000000000000000782700005300000000400000200400000000000000000000\
  108. 0012000048050000006000000C000000402600001C00000000000000000000000000000000000000\
  109. 00000000000000000000000000000000000000000000000000200000080000000000000000000000\
  110. 082000004800000000000000000000002E74657874000000D4070000002000000008000000020000\
  111. 000000000000000000000000200000602E72737263000000200400000040000000060000000A0000\
  112. 000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000\
  113. 0000000000000000000000004000004200000000000000000000000000000000B027000000000000\
  114. 4800000002000500D020000070050000090000000000000000000000000000005020000080000000\
  115. 0000000000000000000000000000000000000000000000000000000000000000176AC7AEEFDF17CB\
  116. AA56763437384545BB93D9435026E4FA74E1F58627354F933210279E295E86B3AD6611A60FF51FAF\
  117. 65B432F08C086A3EC0B77A61DEBB67A2A41B362B38A678FA9F69C4A94C8FEDB42C707F2D175B11C9\
  118. 0FD4FD85DF816F1B6C5D3358D3457FD4795CE95513D6DB5DCA51A110E2A640258C27CE6AC161D451\
  119. 42534A4201000100000000000C00000076342E302E33303331390000000005006C0000005C010000\
  120. 237E0000C8010000EC01000023537472696E677300000000B40300000800000023555300BC030000\
  121. 100000002347554944000000CC030000A401000023426C6F62000000000000000200000107140000\
  122. 0900000000FA253300160000010000000E000000010000000D0000000B0000000100000001000000\
  123. 00000A000100000000000600380026000600550026000600720026000600910026000600AA002600\
  124. 0600C30026000600DE0026000600F900260006001201260006002B01260006005B0148012F006F01\
  125. 000006009E017E010600BE017E01000000000100000000000100010009004F000A0011004F000A00\
  126. 19004F000A0021004F000A0029004F000A0031004F000A0039004F000A0041004F000A0049004F00\
  127. 0A0051004F000A0059004F000F0069004F00150071004F001A002E000B00C0002E001300DB002E00\
  128. 1B00F9002E002300FF002E002B0015012E00330029012E003B00F9002E00530066012E005B007301\
  129. 2E0063007C012E006B008501048000000100000000000000010000001E00DC010000040000000000\
  130. 00000000000001001D00000000000000003C4D6F64756C653E0053514C323031375F4B657941736D\
  131. 2E646C6C006D73636F726C69620053797374656D2E5265666C656374696F6E00417373656D626C79\
  132. 5469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E\
  133. 41747472696275746500417373656D626C79436F6E66696775726174696F6E417474726962757465\
  134. 00417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F647563\
  135. 7441747472696275746500417373656D626C79436F70797269676874417474726962757465004173\
  136. 73656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C74757265\
  137. 41747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D\
  138. 626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F737469\
  139. 63730044656275676761626C6541747472696275746500446562756767696E674D6F646573005379\
  140. 7374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F\
  141. 6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69\
  142. 74794174747269627574650053514C323031375F4B657941736D0000000320000000000050D5633F\
  143. 80DD834B8AEEF46CEAC66A850008B77A5C561934E089042001010E05200101113104200101080320\
  144. 000180A00024000004800000940000000602000000240000525341310004000001000100DFB9D86A\
  145. F437605A8736C7A36E5EF9DE45A64F56C3673CC0A37DACEACD64F830A74C06147998309BF902665A\
  146. 51C7C81843D4C95466B4D59B9E790245A0C05B498753CD075F2C6B1EB40E0E478E73DC3F12929C8B\
  147. 529B9805D10AE58A8945B99AB5BBE885AAB2732892D5F063AE6B7B3F4E0EF71E9FC484E87FD062DA\
  148. 56DFE1D01A01001553514C2032303137204B657920417373656D626C7900001D0100184A75737420\
  149. 746865207374726F6E67206E616D65206B657900000501000000001501001053716C205175616E74\
  150. 756D204C65617000001301000E53514C323031375F4B657941736D00003C010037436F7079726967\
  151. 687420286329203230313720536F6C6F6D6F6E205275747A6B792E20416C6C207269676874732072\
  152. 657365727665642E00000C010007312E302E302E3000000801000200000000000801000800000000\
  153. 001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000050508559\
  154. 00000000020000001C0100005C2600005C080000525344532C6CED9E5E15FF43B030186868E50A40\
  155. 01000000633A5C53514C5175616E74756D4C6561705C50726F6A656374735C426C6F67546F706963\
  156. 735C3731375C53514C323031375F436C7253747269637453656375726974795C53514C323031375F\
  157. 4B657941736D5C6F626A5C52656C656173655C53514C323031375F4B657941736D2E706462000000\
  158. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  159. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  160. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  161. 000000000000000000000000000000000000000000000000A02700000000000000000000BE270000\
  162. 002000000000000000000000000000000000000000000000B0270000000000000000000000000000\
  163. 00005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000\
  164. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  165. 00000000000000000000000000000100100000001800008000000000000000000000000000000100\
  166. 010000003000008000000000000000000000000000000100000000004800000058400000C8030000\
  167. 0000000000000000C80334000000560053005F00560045005200530049004F004E005F0049004E00\
  168. 46004F0000000000BD04EFFE00000100000001000000000000000100000000003F00000000000000\
  169. 0400000002000000000000000000000000000000440000000100560061007200460069006C006500\
  170. 49006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00\
  171. 000000000000B00428030000010053007400720069006E006700460069006C00650049006E006600\
  172. 6F0000000403000001003000300030003000300034006200300000004C001900010043006F006D00\
  173. 6D0065006E007400730000004A00750073007400200074006800650020007300740072006F006E00\
  174. 670020006E0061006D00650020006B00650079000000000044001100010043006F006D0070006100\
  175. 6E0079004E0061006D00650000000000530071006C0020005100750061006E00740075006D002000\
  176. 4C0065006100700000000000540016000100460069006C0065004400650073006300720069007000\
  177. 740069006F006E0000000000530051004C002000320030003100370020004B006500790020004100\
  178. 7300730065006D0062006C0079000000300008000100460069006C00650056006500720073006900\
  179. 6F006E000000000031002E0030002E0030002E003000000048001300010049006E00740065007200\
  180. 6E0061006C004E0061006D0065000000530051004C0032003000310037005F004B00650079004100\
  181. 73006D002E0064006C006C00000000009400380001004C006500670061006C0043006F0070007900\
  182. 72006900670068007400000043006F00700079007200690067006800740020002800630029002000\
  183. 3200300031003700200053006F006C006F006D006F006E0020005200750074007A006B0079002E00\
  184. 200041006C006C002000720069006700680074007300200072006500730065007200760065006400\
  185. 2E0000005000130001004F0072006900670069006E0061006C00460069006C0065006E0061006D00\
  186. 65000000530051004C0032003000310037005F004B0065007900410073006D002E0064006C006C00\
  187. 0000000040000F000100500072006F0064007500630074004E0061006D0065000000000053005100\
  188. 4C0032003000310037005F004B0065007900410073006D0000000000340008000100500072006F00\
  189. 6400750063007400560065007200730069006F006E00000031002E0030002E0030002E0030000000\
  190. 38000800010041007300730065006D0062006C0079002000560065007200730069006F006E000000\
  191. 31002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000\
  192. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  193. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  194. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  195. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  196. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  197. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  198. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  199. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  200. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  201. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  202. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  203. 00000000000000000000000000000000002000000C000000D0370000000000000000000000000000\
  204. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  205. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  206. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  207. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  208. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  209. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  210. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  211. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  212. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  213. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  214. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  215. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  216. 000000000000000048050000000202003082053906092A864886F70D010702A082052A3082052602\
  217. 0101310B300906052B0E03021A0500304C060A2B060104018237020104A03E303C3017060A2B0601\
  218. 0401823702010F3009030100A004A20280003021300906052B0E03021A0500041401DA5AEF4DBC69\
  219. 8EE215F956129DD2D3F3A631D8A082031430820310308201FCA0030201020210170A6F5B690D95A0\
  220. 402670F83588A54F300906052B0E03021D0500301D311B30190603550403131253716C5175616E74\
  221. 756D4C6561702E636F6D3020170D3137303830353037313133335A180F3230393931323331303430\
  222. 3030305A301D311B30190603550403131253716C5175616E74756D4C6561702E636F6D3082012230\
  223. 0D06092A864886F70D01010105000382010F003082010A0282010100C3F1C860C555A61F510AD500\
  224. B185FA9308E3B3D5A64EBA56B2002329EE2F3D9B7A8FD803E21668285EC2C18D4018F211329145C2\
  225. 01B827782FB086A6688B08EFCD80D915F3A8BEA4550206F1232A0086D154632BC8FDE09F5B24EBA2\
  226. 5598F2F5DA255093D1C03E8D67F2F305FEAFF09EEFC7F1FAD2D6A06D65EF2AA9168802A254B77E62\
  227. EFD117EF466C3293E1DB6FB8E51BE618AA6D7B1C58D05712962C17A123DD554EFF6496E9A1FB8C24\
  228. C706B6AEC7BAFE606B46E31DD5CDDE23A071DF30D181BEC9416F1B52AF5FF9AEF15A93FF4D79427A\
  229. F76EF1BC85B3581A7C55FC87446173F2A06DBD96BD59BBD74BF7C8997F8CF0AC8A8FE44AA477E245\
  230. 0681AD670203010001A3523050304E0603551D01044730458010A817C71654DDEDACE2F9E5DDE378\
  231. 6D5FA11F301D311B30190603550403131253716C5175616E74756D4C6561702E636F6D8210170A6F\
  232. 5B690D95A0402670F83588A54F300906052B0E03021D050003820101001734E296A9978F17108A96\
  233. 354A40D664E46FD32E76227B9378AEE1BF6189FB4ED1F638A96775EE2613D3FFCBDECAFD8B265BBD\
  234. DD3841C541F4DC842C8807A0A81469990CEBEA093ABCD5A1FD0ABDE391B3F068B8D42F7A1F1E467B\
  235. 39A610D4C60CDFFEE138CA6610935FD7F526CF3195ED2940FF79FA9D97103C9859526C749471046C\
  236. 9C325CE00100371E9EABF520EC70ADDD1CD298DCC8E2F2C6851B2627FBFAB60B868EC57C3E702C0E\
  237. 58F5741E3EFEA997345D395D70E2249E2D324698853B3BAD0DE28E81B74C386C6A16F41F1F2B2E07\
  238. 8D22A90D56669C7F3BD532D363C1BA54FDDF30825A25A27ED010FF5353D10586EF4622CA67E736E4\
  239. C05162D80C318201AC308201A80201013031301D311B30190603550403131253716C5175616E7475\
  240. 6D4C6561702E636F6D0210170A6F5B690D95A0402670F83588A54F300906052B0E03021A0500A052\
  241. 3010060A2B06010401823702010C31023000301906092A864886F70D010903310C060A2B06010401\
  242. 8237020104302306092A864886F70D01090431160414F8CDBD63BEF919FD6752DC3A30F6D7CFEAF6\
  243. E8D1300D06092A864886F70D0101010500048201002471AB9C11EDE18DDB9CD1E4E8A0A036556B0B\
  244. D3CF60F09E84D94495AE7DA3672B18AE022C8ECA44D5BE17B4D3520F3B3F9AB7B42867B0C3FBF63B\
  245. 92E9B1D6BDD02F284CE43FC9FAC277D947E42E8F8A63CC99FA76698F9F378A6DA7F39D6A68526C76\
  246. E6FF0115DAB5910D8858A5FF6186D9D58055AED9D3A7F571D8D313784EC269477F47B030E5988A7C\
  247. D9967D81168BB8E77A1E76AF32C0112E123BFB317213D58DB5956454D7EF9FF4CD6EE8E865AC6847\
  248. EED8E034791FE9E2640ABBB4FFE221DBC273BEFF39DD80AB91BDECC129082F7E866E3868D534DFB4\
  249. 3C4F78112818353BED62B893822F15DA2454200B53EBFA10AFD28B58F714E220B73F30DE35000000
  250. WITH PERMISSION_SET = SAFE;
  251.     END;
  252.  
  253.     IF (ASYMKEY_ID(N'SQL2017-ClrStrictSecurity-Key') IS NULL)
  254.     BEGIN
  255.         PRINT ' Creating Asymmetric Key...';
  256.         CREATE ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key]
  257.             FROM ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm];
  258.     END;
  259.  
  260.     IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-Login') IS NULL)
  261.     BEGIN
  262.         PRINT ' Creating permissions Login...';
  263.         CREATE LOGIN [SQL2017-ClrStrictSecurity-Login]
  264.             FROM ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key];
  265.     END;
  266.  
  267.     GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity-Login]; -- REQUIRED!!!!
  268.  
  269.  
  270.     IF (ASSEMBLYPROPERTY(N'SQL2017-ClrStrictSecurity-KeyAsm', N'MvID') IS NOT NULL)
  271.     BEGIN
  272.         PRINT ' Dropping Assembly that holds the Asymmetric Key...';
  273.         DROP ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm];
  274.     END;
  275.  
  276.     IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-TempLogin') IS NOT NULL)
  277.     BEGIN
  278.         PRINT ' Dropping temporary Login...';
  279.         DROP LOGIN [SQL2017-ClrStrictSecurity-TempLogin];
  280.     END;
  281.  
  282.     IF (CERT_ID(N'SQL2017-ClrStrictSecurity-Cert') IS NOT NULL)
  283.     BEGIN
  284.         PRINT ' Dropping Certificate...';
  285.         DROP CERTIFICATE [SQL2017-ClrStrictSecurity-Cert];
  286.     END;
  287. END;
  288.  
  289. --DROP LOGIN [SQL2017-ClrStrictSecurity-Login];
  290. --DROP ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key];
  291.  
  292.  
  293. -- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
  294. EXEC sp_configure 'clr strict security';
  295.  
  296. SELECT [name], [is_trustworthy_on], [collation_name]
  297. FROM sys.databases WHERE [database_id] = DB_ID(N'SQLCLRvsClrStrictSecurity');
  298.  
  299.  
  300. GO
  301. USE [SQLCLRvsClrStrictSecurity];
  302. -- DROP PROCEDURE [dbo].[RegEx_IsMatch];
  303. -- DROP ASSEMBLY [SQL2017_ClrStrictSecurity];
  304.  
  305.  
  306. IF (ASSEMBLYPROPERTY(N'SQL2017_ClrStrictSecurity', N'MvID') IS NULL)
  307. BEGIN
  308.     PRINT 'Creating [SQL2017_ClrStrictSecurity] Assembly...';
  309.     CREATE ASSEMBLY [SQL2017_ClrStrictSecurity]
  310.         AUTHORIZATION [dbo]
  311.         FROM 0x\
  312. 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
  313. 0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
  314. 69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
  315. 2400000000000000504500004C01030041F485590000000000000000E00002210B010B00000C0000\
  316. 0008000000000000FE29000000200000004000000000001000200000000200000400000000000000\
  317. 06000000000000000080000000020000EDFC00000300608500001000001000000000100000100000\
  318. 00000000100000000000000000000000B02900004B00000000400000380400000000000000000000\
  319. 0000000000000000006000000C000000782800001C00000000000000000000000000000000000000\
  320. 00000000000000000000000000000000000000000000000000200000080000000000000000000000\
  321. 082000004800000000000000000000002E74657874000000040A000000200000000C000000020000\
  322. 000000000000000000000000200000602E72737263000000380400000040000000060000000E0000\
  323. 000000000000000000000000400000402E72656C6F6300000C000000006000000002000000140000\
  324. 0000000000000000000000004000004200000000000000000000000000000000E029000000000000\
  325. 4800000002000500FC2000007C070000090000000000000000000000000000005020000080000000\
  326. 000000000000000000000000000000000000000000000000000000000000000091780EA01A63A0D0\
  327. 0E190A0E39C02686F7BBFCB3D5CAD5378CC74A2FE6979E80312AB5CB936C9418A4CAA677BF10B8F0\
  328. 9E1ADDFC31CB6B51F24256AFCF714EF7C90812ACBCCEE166107189F3C06954C400900E973785ABCC\
  329. 34C60FC4412479F9EA535BE5F67BFD37822F8EBC082A5627EDD97C73152BF14B9BC963215B5F35BB\
  330. 8E0F00280F00000A0F01280F00000A1716161F1E731000000A281100000A281200000A2A1E022813\
  331. 00000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C000000\
  332. 18020000237E000084020000F402000023537472696E677300000000780500000800000023555300\
  333. 8005000010000000234755494400000090050000EC01000023426C6F620000000000000002000001\
  334. 471500000900000000FA253300160000010000001500000002000000020000000200000013000000\
  335. 0C000000010000000300000000000A0001000000000006003D0036000A00650050000A0070005000\
  336. 0600B0009E000600C7009E000600E4009E00060003019E0006001C019E00060035019E0006005001\
  337. 9E0006006B019E00060084019E0006009D019E000600CD01BA013B00E101000006001002F0010600\
  338. 3002F0010A00830268020600A20236000E00CA02AB020E00D002AB02000000000100000000000100\
  339. 01000100100028000000050001000100D0200000000096007A000A000100F4200000000086188800\
  340. 13000300000001008E00000002009300210088001700290088001700310088001700390088001700\
  341. 41008800170049008800170051008800170059008800170061008800170069008800170071008800\
  342. 1C0081008800220089008800130091008800130019009802EE0099008800F200A100DD02F9001100\
  343. E502030109008800130020007300C9002E002B0044012E00130028012E001B0028012E0023002E01\
  344. 2E000B0009012E00330070012E003B0028012E005300AC012E005B00B9012E006300C2012E006B00\
  345. CB010480000001000000000000000100000027004E02000004000000000000000000000001002D00\
  346. 00000000040000000000000000000000010044000000000004000000000000000000000001003600\
  347. 0000000000000000003C4D6F64756C653E0053514C323031375F436C725374726963745365637572\
  348. 6974792E646C6C0055444673006D73636F726C69620053797374656D004F626A6563740053797374\
  349. 656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E00\
  350. 53716C537472696E670052656745785F49734D61746368002E63746F720054657874004578707265\
  351. 7373696F6E0053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474\
  352. 72696275746500417373656D626C794465736372697074696F6E4174747269627574650041737365\
  353. 6D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D7061\
  354. 6E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373\
  355. 656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D6172\
  356. 6B41747472696275746500417373656D626C7943756C747572654174747269627574650041737365\
  357. 6D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E\
  358. 4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541\
  359. 747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E43\
  360. 6F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174\
  361. 747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005351\
  362. 4C323031375F436C725374726963745365637572697479004D6963726F736F66742E53716C536572\
  363. 7665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75\
  364. 650054696D655370616E0053797374656D2E546578742E526567756C617245787072657373696F6E\
  365. 730052656765780052656765784F7074696F6E730049734D61746368006F705F496D706C69636974\
  366. 000000000003200000000000D258441C60AF8441936C5A07D95675150008B77A5C561934E0890800\
  367. 021109110D110D03200001042001010E05200101113D042001010880A00024000004800000940000\
  368. 000602000000240000525341310004000001000100DFB9D86AF437605A8736C7A36E5EF9DE45A64F\
  369. 56C3673CC0A37DACEACD64F830A74C06147998309BF902665A51C7C81843D4C95466B4D59B9E7902\
  370. 45A0C05B498753CD075F2C6B1EB40E0E478E73DC3F12929C8B529B9805D10AE58A8945B99AB5BBE8\
  371. 85AAB2732892D5F063AE6B7B3F4E0EF71E9FC484E87FD062DA56DFE1D0240100020054020F497344\
  372. 657465726D696E697374696301540209497350726563697365010320000E06200301080808090004\
  373. 020E0E1155114D0500011109021E01001953514C323031375F436C72537472696374536563757269\
  374. 747900000501000000001501001053716C205175616E74756D204C65617000002B01002653514C20\
  375. 536572766572203230313720767320434C522073747269637420736563757269747900003B010036\
  376. 436F7079726967687420286329203230313720536F6C6F6D6F6E2052757A6B792E20416C6C207269\
  377. 676874732072657365727665642E00000C010007312E302E302E3000000801000200000000000801\
  378. 000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000\
  379. 0000000041F4855900000000020000001C01000094280000940A000052534453A19A1595E9DDED45\
  380. BEA5CF59FEDC604003000000633A5C53514C5175616E74756D4C6561705C50726F6A656374735C42\
  381. 6C6F67546F706963735C3731375C53514C323031375F436C7253747269637453656375726974795C\
  382. 53514C323031375F436C7253747269637453656375726974795C6F626A5C52656C656173655C5351\
  383. 4C323031375F436C7253747269637453656375726974792E70646200000000000000000000000000\
  384. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  385. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  386. 0000000000000000000000000000000000000000000000000000000000000000D829000000000000\
  387. 00000000EE290000002000000000000000000000000000000000000000000000E029000000000000\
  388. 00005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000\
  389. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  390. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  391. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  392. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  393. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  394. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  395. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  396. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  397. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  398. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  399. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  400. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  401. 00000000000000000000000000000000000000000000000000000000000000000000000000000100\
  402. 10000000180000800000000000000000000000000000010001000000300000800000000000000000\
  403. 0000000000000100000000004800000058400000DC0300000000000000000000DC03340000005600\
  404. 53005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100\
  405. 000001000000000000000100000000003F0000000000000004000000020000000000000000000000\
  406. 00000000440000000100560061007200460069006C00650049006E0066006F000000000024000400\
  407. 00005400720061006E0073006C006100740069006F006E00000000000000B0043C03000001005300\
  408. 7400720069006E006700460069006C00650049006E0066006F000000180300000100300030003000\
  409. 30003000340062003000000044001100010043006F006D00700061006E0079004E0061006D006500\
  410. 00000000530071006C0020005100750061006E00740075006D0020004C0065006100700000000000\
  411. 5C001A000100460069006C0065004400650073006300720069007000740069006F006E0000000000\
  412. 530051004C0032003000310037005F0043006C007200530074007200690063007400530065006300\
  413. 750072006900740079000000300008000100460069006C006500560065007200730069006F006E00\
  414. 0000000031002E0030002E0030002E00300000005C001E00010049006E007400650072006E006100\
  415. 6C004E0061006D0065000000530051004C0032003000310037005F0043006C007200530074007200\
  416. 690063007400530065006300750072006900740079002E0064006C006C0000009400370001004C00\
  417. 6500670061006C0043006F007000790072006900670068007400000043006F007000790072006900\
  418. 670068007400200028006300290020003200300031003700200053006F006C006F006D006F006E00\
  419. 2000520075007A006B0079002E00200041006C006C00200072006900670068007400730020007200\
  420. 65007300650072007600650064002E000000000064001E0001004F0072006900670069006E006100\
  421. 6C00460069006C0065006E0061006D0065000000530051004C0032003000310037005F0043006C00\
  422. 7200530074007200690063007400530065006300750072006900740079002E0064006C006C000000\
  423. 700027000100500072006F0064007500630074004E0061006D00650000000000530051004C002000\
  424. 53006500720076006500720020003200300031003700200076007300200043004C00520020007300\
  425. 74007200690063007400200073006500630075007200690074007900000000003400080001005000\
  426. 72006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E00\
  427. 3000000038000800010041007300730065006D0062006C0079002000560065007200730069006F00\
  428. 6E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000\
  429. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  430. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  431. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  432. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  433. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  434. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  435. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  436. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  437. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  438. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  439. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  440. 002000000C000000003A000000000000000000000000000000000000000000000000000000000000\
  441. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  442. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  443. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  444. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  445. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  446. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  447. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  448. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  449. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  450. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  451. 00000000000000000000000000000000000000000000000000000000000000000000000000000000\
  452. 0000000000000000000000000000000000000000000000000000000000000000
  453.         WITH PERMISSION_SET = SAFE;
  454. END;
  455.  
  456.  
  457. IF (OBJECT_ID(N'dbo.RegEx_IsMatch') IS NULL)
  458. BEGIN
  459.     PRINT N'Creating [dbo].[RegEx_IsMatch]...';
  460.     EXEC(N'
  461. CREATE FUNCTION [dbo].[RegEx_IsMatch]
  462. (
  463.  @Text NVARCHAR (MAX),
  464.  @Expression NVARCHAR (MAX)
  465. )
  466. RETURNS BIT
  467. WITH RETURNS NULL ON NULL INPUT
  468. AS EXTERNAL NAME [SQL2017_ClrStrictSecurity].[UDFs].[RegEx_IsMatch];
  469. ');
  470. END;
  471. GO
  472.  
  473.  
  474. SELECT dbo.RegEx_IsMatch(N'3456', N'\d{4}'); -- 1
  475. SELECT dbo.RegEx_IsMatch(N'34567', N'\d{4}'); -- 1
  476. SELECT dbo.RegEx_IsMatch(N'345', N'\d{4}'); -- 0
  477. SELECT dbo.RegEx_IsMatch(NULL, N'f'); -- NULL
  478.  
  479. /* RETURNS:
  480.  
  481. 1
  482. 1
  483. 0
  484. NULL
  485.  
  486. */
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