从 SQL Server 2005 中提取 .NET 程序集

Question

我正在尝试帮助一位私人朋友（现在也是客户）使用 SQL CLR 相关问题。他有一个 SQL Server，其中的数据库有 3 个 .NET 程序集 嵌入其中。他让我帮他从里面提取组件 数据库并将它们保存为磁盘上的 .dll 文件。这可能吗？

Answer 1

是的，这是可能的。程序集的实际二进制表示形式
在您的服务器的 SQL 目录中。也就是说，如果您在
sys.assemblies_files 和 sys.assemblies 你可以得到你想要的所有信息
需要。程序集二进制文件位于 sys. assembly_files 的内容列中
看法。

但是为了从 SQL Server 中提取二进制表示形式并将其转换为
磁盘上的文件，您将必须编写一些需要在磁盘上运行的.NET代码
您引用的程序集现在所在的数据库相同。视觉上
Studio 启动一个 SQL CLR 项目并使用以下代码向其中添加一个类：

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;

namespace ExtractSqlAssembly {
    [PermissionSet(SecurityAction.Demand, Unrestricted = true, Name = "FullTrust")]
    public partial class SaveSqlAssembly {

        [SqlProcedure]
        public static void SaveAssembly(string assemblyName, string path) {
            string sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyname";
            using (SqlConnection conn = new SqlConnection("context connection=true")) {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) {
                    SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
                    param.Value = assemblyName;
                    cmd.Parameters.Add(param);

                    cmd.Connection.Open();  // Read in the assembly byte stream
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    SqlBytes bytes = reader.GetSqlBytes(0);

                    // write the byte stream out to disk
                    FileStream bytestream = new FileStream(path, FileMode.CreateNew);
                    bytestream.Write(bytes.Value, 0, (int)bytes.Length);
                    bytestream.Close();
                }
            }
        }
    }
}

然后构建该项目并将其部署到数据库。确保 CLR
在 SQL Server 上启用启用配置选项。这大概是
已经启用，因为上面有程序集。如果 clr 执行不
启用后，您可以在 SSMS 上运行以下代码来启用它：

sp_configure 'clr enabled', 1
go

reconfigure
go

您还需要注意的另一件事是，默认情况下 SQL Server 可能会
不允许您从 .NET 代码写入磁盘。如果你得到一个 FileIO
通过调用以下存储过程来运行上述代码时出现安全错误
SSMS，您需要为
集会。您可以通过 SSMS 执行此操作：右键单击新程序集并查看
属性对话框中的权限集。将其设置为外部访问。现在轮到你
应该能够通过在 SSMS 中运行以下代码来导出程序集：

exec SaveAssembly 'AssemblyName', 'f:\path\to\assemblyname.dll'

希望这对您有用...

Answer 2

是的。

执行 select * from sys.assemble_files 来查找所需程序集的 id

DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'c:\temp\myassembly.dll', 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

Answer 3

Jonas 的方法也可以很好地用作控制台应用程序或 Linqpad 脚本 - 正如他所暗示的那样，代码不需要在 SQL 进程中本地执行。例如，从数据库中提取 tSQLt 程序集（测试工具）：

void Main()
{
    var assemblyName = "tSQLtCLR";
    var serverName = "localhost";
    var databaseName = "MyDb";
    var targetDir = Environment.ExpandEnvironmentVariables("%TEMP%");
    var targetFile = Path.Combine(targetDir, assemblyName) + ".dll";

    var sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyName";

    var connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=true", serverName, databaseName);
    using(var connection = new System.Data.SqlClient.SqlConnection(connectionString)){
        connection.Open();

        var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.Add("@assemblyName", assemblyName);

        using(var reader = command.ExecuteReader()){
            if(reader.Read()){
                var bytes = reader.GetSqlBytes(0);

                File.WriteAllBytes(targetFile, bytes.Value);
                Console.WriteLine(targetFile);
            }else{
                throw new Exception("No rows returned");
            }
        }
    }
}

Answer 4

Preet 的解决方案对我有用，但我必须配置 Ole Automation 才能在 SQL Server 2008 R2 上工作。另请注意，SaveToFile 不起作用，也不会给出错误消息，除非 SQL Server 具有该目录的权限。就我而言，我使用了 SQL Server 实例的数据文件夹，效果很好。

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE
GO

DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65546

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myassembly.dll', 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken  

EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Answer 5

我找到了解决此问题的更简单的解决方案，这是必要的，因为 sp_OACreate 似乎不适用于 SQL Server 2017（至少不适用于 Linux 版本）。

您可以使用 BCP 实用程序将程序集写入磁盘上的文件，如下所示：

/opt/mssql-tools/bin/bcp "SELECT content FROM sys.assembly_files WHERE name = '${ASSEMBLY_NAME}'" \
    queryout /tmp/my_assembly.so -f bcp.fmt \
    -S localhost -U sa -P "${SA_PASSWORD}" -d master

并使用此格式文件 (bcp.fmt)：

13.0
1
1       SQLBINARY           0       0       ""   1     content                          ""

生成的文件 (/tmp/my_ assembly.so) 可用于创建装配，像这样：

CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [dbo]
FROM '/tmp/my_assembly.so' WITH PERMISSION_SET = SAFE;

Answer 6

采用 Preet 和 Nate 的解决方案并将其转换为一个脚本，该脚本将使用游标导出所有 clr 过程：

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE
GO

RAISERROR ('Starting...', 0, 1) WITH NOWAIT

DECLARE @ObjectToken INT
DECLARE @AssemblyLocation VARCHAR(MAX)
DECLARE @Msg VARCHAR(MAX)
DECLARE @Content VARBINARY(MAX)
DECLARE @Count AS INT = (SELECT COUNT(name) FROM sys.assembly_files)

DECLARE AssemblyFiles CURSOR FAST_FORWARD
FOR
  SELECT 
    CAST(ROW_NUMBER() OVER (ORDER BY name) AS VARCHAR(10)) + ' of ' + CAST(@Count AS VARCHAR(10)) + ' - ' + name AS Msg,
    '[a location the server can write to]' + name + '.dll' AS AssemblyLocation,
    content    
  FROM
    sys.assembly_files
  ORDER BY 
    name

OPEN AssemblyFiles
FETCH NEXT FROM AssemblyFiles
INTO @Msg, @AssemblyLocation, @Content

WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @Content
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @AssemblyLocation, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken  

    RAISERROR (@Msg, 0, 1) WITH NOWAIT

    FETCH NEXT FROM AssemblyFiles
    INTO @Msg, @AssemblyLocation, @Content
  END

CLOSE AssemblyFiles
DEALLOCATE AssemblyFiles

RAISERROR ('Done', 0, 1) WITH NOWAIT

EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Answer 7

无需任何编码的替代解决方案是 VisualStudio 数据库项目。
创建新的数据库项目并导入您的数据库与程序集。您将获得 DDL 脚本和程序集。

请注意，您需要 安装适用于 Visual Studio 的 SSDT。