Recover CLR assemblies from SQL Server

This is an amazingly short post covering how to get save a CLR assembly out of SQL Server via PowerShell. Original code was on a stackoverflow question I don't have reference to at the moment. Run as is, this code will extract all the user assemblies in a given database catalog and save them out to the C:\ssisdata folder. If you know the particular assembly you want, you can uncomment lines 17 (tsql), 32 (PowerShell) and update line 50 to point to the correct assembly name. Run the query template if you don't know all the assembly names in a given database. And obviously, fix line 48 to point to the correct database.

   1:  #[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null

   2:   

   3:  $assemblyListQuery = "SELECT A.name FROM sys.assemblies A WHERE A.is_user_defined = 1 ORDER BY 1 ASC "

   4:   

   5:  $queryTemplate = "

   6:  SELECT 

   7:      AF.content

   8:  ,    A.name    

   9:  FROM 

  10:      sys.assembly_files AF 

  11:      INNER JOIN 

  12:          sys.assemblies A 

  13:          ON AF.assembly_id = A.assembly_id 

  14:  WHERE 

  15:      AF.file_id = 1

  16:      AND A.is_user_defined = 1

  17:      --AND A.name = @assemblyName

  18:  "

  19:   

  20:  Function Save-Assemblies()

  21:  {

  22:      param

  23:      (

  24:           [string]$connectionString

  25:      ,   [string]$query

  26:      )

  27:          $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

  28:          $command = New-Object System.Data.SqlClient.SqlCommand($query)

  29:          

  30:          $connection.Open()

  31:          $command.Connection = $connection

  32:          #$hush = $command.Parameters.AddWithValue("@assemblyName", $assemblyName)

  33:          $reader = $command.ExecuteReader()

  34:          

  35:          while ($reader.Read())

  36:          {

  37:              #$bytes = New-Object System.Data.SqlClient.SqlBytes()

  38:              $bytes = $reader.GetSqlBytes(0)

  39:              $name = $reader.GetString(1)

  40:              $bytestream = New-Object System.IO.FileStream("C:\ssisdata\$name.dll", [System.IO.FileMode]::Create)

  41:              $bytestream.Write($bytes.Value, 0, $bytes.Length)

  42:              $bytestream.Close()

  43:          }

  44:              

  45:  }

  46:   

  47:   

  48:  $bnode = "Data Source=localhost;Initial Catalog=FOO;Integrated Security=SSPI;"

  49:   

  50:  $assemblyName = "NiftyAssembly"

  51:      

  52:  Save-Assemblies $bnode $queryTemplate

Now that you have the assembly, use a tool like Reflector or ILSpy to recover as much of the source code as you can. Happy decompiling.