Início / dba / Perguntas / 107366
Accepted
Philippe
Asked: 2015-07-18 07:17:27 +0800 CST

Exportar uma tabela e compactar com senha

  • 772

Eu gostaria, do TSQL, de usar uma visão para extrair dados de uma tabela, produzi-los com BCP e, em seguida, usar ferramentas não terceirizadas para ZIP com uma senha e criptografia AES. Opcionalmente, eu poderia usar uma ferramenta de terceiros que não exija instalação local (exe autônomo em uma unidade de rede, por exemplo).

Eu tentei usar sp_OA*, mas encontrei este post dizendo que eles eram recursos com bugs, vazamento de memória e obsoletos . Então, estou procurando alternativas, se houver alguma. Todas as pesquisas do Google retornaram às mesmas soluções: 7zip, Winrar ou sp_OA*.

Não quero o código, apenas o nome dos recursos ou métodos que podem me ajudar, para saber o que procurar.

MSSQL2008 e superior. Pode ser uma solução Powershell. Não SSIS, se possível. Só para constar, é atualizar diariamente um software com dados atuais, pois não tem acesso direto à fonte.

Se o 7zip for o melhor/único caminho a percorrer, terei uma tonelada de papelada para preencher e reuniões para comparecer para justificar a instalação de software que não seja de banco de dados em um servidor SQL. :-)

sql-server export

2 respostas

  1. Best Answer

    Sim, fique longe dos sp_OA*procedimentos de Automação OLE.

    Se você quiser/precisar lidar com isso apenas dentro do SQL Server, poderá fazê-lo usando o SQLCLR, que substitui os sp_OA*procedimentos de automação OLE. E se você não estiver usando xp_cmdshellpara mais nada, não há necessidade de ativá-lo apenas para isso (embora, para ser justo, se estiver usando o SQL Agent, uma etapa do CMD seria capaz de executar BCP e/ou 7zip sem a necessidade de habilitar xp_cmdshell).

    Com o SQLCLR você pode:

    • faça a exportação de dentro do T-SQL também e não precise mexer com o BCP, que é um processo externo (consulte FileStream ).
    • zip o arquivo, como uma etapa separada, desde então. NET possui métodos para gzip e gunzip (consulte GZipStream ).

    • criptografar com uma senha (consulte RijndaelManaged e AesManaged ). Enquanto você especificou o AES, a documentação do MSDN para AesManaged afirma:

      O algoritmo AES é essencialmente o algoritmo simétrico de Rijndael com tamanho de bloco fixo e contagem de iterações. Essa classe funciona da mesma forma que a classe RijndaelManaged, mas limita os blocos a 128 bits e não permite modos de realimentação.

      A diferença é se você estiver usando SQL Server 2005, 2008 ou 2008 R2. Essas versões são limitadas ao uso do .NET 3.5, e a documentação do MSDN para AesManaged(para essa versão) afirma que ele possui um Host Protection Attribute (HPA), o que significa que qualquer assembly usando essa classe deve ser marcado como PERMISSION_SET = UNSAFE, enquanto RijndaelManagednão tem esse problema e pode ser usado em uma EXTERNAL_ACCESSmontagem. No entanto, parece que a documentação de AesManagedmostra que, começando no .NET Framework versão 4.0 (SQL Server 2012, 2014 e 2016 usam .NET Framework 4.0 e mais recente -- atualmente até 4.6), a observação sobre o HPA foi removida . Mesmo que UNSAFEseja aceitável, observe que o HPA específico é MayLeakOnAbort , que significa "pode ​​vazar memória se a operação for encerrada".

    Nesta configuração, a senha faz parte da criptografia, não da compactação. Portanto, você tem um arquivo zip contendo um arquivo criptografado ou um arquivo criptografado contendo o arquivo zip. O método anterior fornece um arquivo que pode ser descompactado por qualquer pessoa, mas lido apenas por alguém com a senha correta. O último método requer saber a senha para obter o arquivo compactado e também permite uma melhor compactação, pois a compactação será feita em um arquivo de texto (com maior probabilidade de padrões compressíveis).

    Embora ainda não tenha implementado os algoritmos de criptografia, codifiquei a exportação de arquivo (de qualquer consulta aleatória) e GZip/GZunzip no SQLCLR e posso afirmar que eles são executados com eficiência ou, pelo menos, podem ser eficientes (e podem também ser feito para ser ineficiente). Com isso, quero dizer que os usei para extrair consultas produzindo arquivos de texto de 5 GB e GZipping esses arquivos de texto de 5 GB, e a memória nunca foi afetada negativamente, pois ambas as operações são transmitidas. Observe que a classe GZipStream fornecida pelo .NET não oferece suporte ao formato Zip64 e, portanto, está limitada a 4 GB de dados de origem.

    Para quem estiver interessado em funções SQLCLR pré-construídas para fazer a extração e compactação (as que mencionei acima), elas estão disponíveis na biblioteca SQL# (da qual sou o autor). Observe que:

    • embora exista uma versão Free do SQL#, os dois objetos mencionados aqui, DB_BulkExport e File_GZip , estão disponíveis apenas na versão Full.
    • para superar a limitação de 4 GB da GZipStreamclasse, tive que incorporar uma biblioteca de terceiros que suporta Zip64.
    • em breve haverá funções para lidar com criptografia e descriptografia de arquivos via Rijndael (disponível para SQL Server 2005 - 2016, pelo menos) e Aes (disponível para SQL Server 2012 - 2016, pelo menos).
    • 3

  2. Eu faço isso usando xp_cmdshell. Você pode exportar dados do trabalho SQL e compactar usando esse procedimento armazenado. A melhor maneira é executar um arquivo bat para zip.

    OBSERVAÇÃO: para usar esse sp você precisa que ele seja permitido (no nível do SQL Server) e você deve ter permissões para executá-lo.

    • 0

relate perguntas