Network Programming in .NET

You can do very basic string matching using the LIKE operator in SQL server, but you quickly hit the limits of what you can easily do using the simple operations supported. .NET offers full Regex (Regular expression) support, and you can use this within SQL server by creating a UDF CLR (User defined function).

TL;DR; here is the Github repo: https://github.com/infiniteloopltd/SQLServerRegex

Which defines this one function;

using System.Data.SqlTypes;
using RX = System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Regex(string input, string regex)
{
var match = RX.Regex.Match(input, regex).Groups[1].Value;
return new SqlString (match);
}
}

Which you can install into your SQL server database as follows;

EXEC sp_changedbowner ‘sa’
ALTER DATABASE [<your database here>] SET trustworthy ON

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO

sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

CREATE ASSEMBLY [DbRegex] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030079D4F85D0000000000000000E00022200B0130000008000000060000000000003A260000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E82500004F00000000400000A002000000000000000000000000000000000000006000000C000000B02400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000040060000002000000008000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E000000000000000000000000000040000042000000000000000000000000000000001C2600000000000048000000020005008C200000240400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002002400000001000011000203280500000A6F0600000A176F0700000A6F0800000A0A06730900000A0B2B00072A2202280A00000A002A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000080010000237E0000EC010000AC01000023537472696E6773000000009803000004000000235553009C030000100000002347554944000000AC0300007800000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C0000000200000002000000020000000A000000040000000100000001000000030000000000A800010000000000060050000E01060070000E0106002700FB000F002E01000006009101BD000A003B00DA000A0098003D010E00A00152010E00A20052010E00C40052010E00D40052010E001F005201000000000100000000000100010001001000710100001500010001005020000000009600A0013A0001008020000000008618F5000600030000000100980100000200A6010900F50001001100F50006001900F5000A003100F50006004100A2001600490086011D005100B400220061008E0028003900F5002C002900F50006002000230072002E000B0041002E0013004A002E001B00690010000480000000000000000000000000000000009E010000040000000000000000000000310016000000000004000000000000000000000031000A00000000000400000000000000000000003100BD00000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200436170747572650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E67004D6174636800446252656765782E646C6C006765745F4974656D0053797374656D0047726F7570436F6C6C656374696F6E0047726F7570004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E546578742E526567756C617245787072657373696F6E730055736572446566696E656446756E6374696F6E73006765745F47726F757073004F626A65637400696E707574004462526567657800726567657800000000000F4DE26091FA75478EA1FCAB7323A50F000420010108032000010520010111110507020E111D06000212250E0E0420001229052001122D080320000E042001010E08B77A5C561934E089060002111D0E0E0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000079D4F85D00000000020000001C010000CC240000CC06000052534453DAF00DA62F5BD1478B583CA3D74FE38B01000000433A5C55736572735C46696163685C736F757263655C7265706F735C446252656765785C446252656765785C6F626A5C44656275675C446252656765782E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001026000000000000000000002A2600000020000000000000000000000000000000000000000000001C260000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D006500000044006200520065006700650078002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000044006200520065006700650078002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000003C3600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE

CREATE FUNCTION [dbo].[regex] (@text [nvarchar](MAX), @regex [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]

Once that’s done, then you can run SQL commands like;

select dbo.regex(‘1234abc123′,’\d+([a-z]+)\d+’)

My disclaimer is that I don’t speak greek, and this code is far from a perfect transliteration from english to greek, but it’s a good starting point I guess.

It transliterates English to Greek; in a SQL UDF

create function Transliterate (@english nvarchar(max))
returns nvarchar(max)
as
begin
set @english = replace(@english,N’th’,N’θ’)
set @english = replace(@english,N’ch’,N’χ’)
set @english = replace(@english,N’ps’,N’ψ’)
set @english = replace(@english,N’q’,N’κο’)
set @english = replace(@english,N’w’,N’ου’)
set @english = replace(@english,N’a’,N’α’)
set @english = replace(@english,N’b’,N’β’)
set @english = replace(@english,N’c’,N’χ’)
set @english = replace(@english,N’v’,N’β’)
set @english = replace(@english,N’g’,N’γ’)
set @english = replace(@english,N’d’,N’δ’)
set @english = replace(@english,N’e’,N’ε’)
set @english = replace(@english,N’z’,N’ζ’)
set @english = replace(@english,N’i’,N’η’)
set @english = replace(@english,N’k’,N’κ’)
set @english = replace(@english,N’l’,N’λ’)
set @english = replace(@english,N’m’,N’μ’)
set @english = replace(@english,N’n’,N’ν’)
set @english = replace(@english,N’x’,N’ξ’)
set @english = replace(@english,N’o’,N’ο’)
set @english = replace(@english,N’p’,N’π’)
set @english = replace(@english,N’r’,N’ρ’)
set @english = replace(@english,N’s’,N’σ’)
set @english = replace(@english,N’t’,N’τ’)
set @english = replace(@english,N’y’,N’υ’)
set @english = replace(@english,N’h’,N”)
set @english = replace(@english,N’j’,N’ι’)
set @english = replace(@english,N’f’,N’φ’)
set @english = replace(@english,N’u’,N’υ’)
return @english
end

The AWS CLI allows you to interact with AWS from the command line, and an obvious use of the tool, is to upload your Database Backups to S3, to keep them safe.

Once you’ve configured AWS to run from the command line on your server, and tested it, then you try the exact same command from xp_cmdshell and it fails with “unable to locate credentials“.

What is happening here, is your remote desktop user, is probably “Adminstrator” but SQL server agent runs under the username MSSQLSERVER

You can verify the username used by SQL server agent by runing the command

xp_cmdshell ‘echo %username%’

The AWS CLI is looking for the configuration file under c:\users\MSSQLSERVER\.aws – but the configuration file is actually under c:\users\Adminstrator\.aws , so you need to copy the configuration file using xp_cmdshell as follows

1. Using remote desktop, copy the configuration file to an accessible folder, like C:\temp
2. xp_cmdshell ‘md C:\users\MSSQLSERVER\.aws’

   xp_cmdshell ‘copy c:\temp\credentials c:\users\MSSQLSERVER\.aws’

    

3. And then test with
   xp_cmdshell ‘”C:\Program Files\Amazon\AWSCLI\bin\aws.exe” s3 ls’