Trabalhando com Credenciais e Proxy Account no SQL Server

Tempo de leitura: 4 minutos

Este post é dedicado à mulher mais importante da minha vida, que fez ser o que sou hoje com muita luta e dedicação minha mãe, Francisca de Alencar.

Após a dedicatória, vamos falar sobre SQL.

Bom dia. Galera, blz? Hoje vamos falar de alguns recursos que ganharam muita força a partir do SQL Server 2005. Segurança, no SQL Server foram incluídas diversas features, como por exemplo: TDE, Certificados, Credenciais, Proxy Account e etc.

No post , falamos de como podemos utilizar certificados para atribuir permissões especificas, sem ter que aumentar o nível de acesso de nossos logins. Nesse post vamos falar de como utilizar credenciais e proxy account. Um dos problemas que tínhamos no SQL Server 2000 era que quando era necessário executar o comando xp_cmdsheel, o login que disparava a chamada do comando precisava ser membro do server role sysadmin para poder executar com sucesso. A partir do SQL Server 2005, temos como permitir esse comportamento, sem que o login seja membro do sysadmin utilizando proxy account.

Imagine o cenário: No nosso ambiente temos diversos pacotes SSIS de integrações, porém, o login que os executa é um login do tipo SQL Server authentication que não pode fazer parte do server role sysadmin. Um dos motivos é que a publicação do pacote é feita através de file system. Thiago existe outra maneira mais segura de fazer isso? Claro, que sim meu querido blog, mas, no momento foi essa solução implementada.

Obs: Com o file system, é aberto uma brecha de segurança, pois, o login que tiver acesso a pasta onde os arquivos serão publicados, podem abrir o webconfig do pacote.

É triste dizer, mas, vi muitos DBAs que tiveram esse problema e colocaram o login dentro do server role sysadmin para “resolver” o problema. A solução que encontrei sem ter que dar a permissão “motherfucker” para o login que executa o pacote será explicada a seguir.

Vamos criar o login do tipo SQL Server dentro do server role bulkadmin.

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’PkgSSIS’)

DROP LOGIN [PkgSSIS]

GO

 

/* For security reasons the login is created disabled and with a random password. */

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

CREATE

            LOGIN [PkgSSIS] WITH PASSWORD=N’pkgssis’,

                  DEFAULT_DATABASE=[master],

                  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 EXEC sys.sp_addsrvrolemember @loginame = N’PkgSSIS’, @rolename = N’bulkadmin’

GO

 ALTER LOGIN [PkgSSIS] ENABLE

GO

Vamos atribuir permissão para a base de dados utilizadas nos post anteriores e para o msdb. No banco dbPermissoes o login terá que ficar dentro do database role DR_EXEC. Dentro do msdb o login ficará dentro do database role SQLAgentUserRole e SQLAgentReaderRole.

dbPermissoes:

Atribuição ao Role:

Msdb e atribuição aos roles:

 Por enquanto até aqui sem muitas surpresas e complicações. Agora queremos que esse login tenha permissão de executar pacotes, quando tentamos executar pacotes sem ser membro do server role sysadmin, a mensagem é retornada:

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy accoutnt.

Primeiro temos que criar uma credencial, conforme script:

–Cria a credencial utilizando a conta de dominio SQLSVC e coloque o password da conta.

USE [master]

GO

CREATE CREDENTIAL [SSISCredencial] WITH IDENTITY = N’Dominio\SQLSvc’, SECRET = N’PasswordAqui’

GO

Vai ficar da seguinte forma:

Agora iremos criar um proxy account , atribuindo a essa credencial.

USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

IF  EXISTS (SELECT name FROM msdb.dbo.sysproxies WHERE name = N’SSISProxy’)

begin

      EXEC msdb.dbo.sp_delete_proxy @proxy_name=N’SSISProxy’

end

GO

 USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’SSISProxy’,@credential_name=N’SSISCredencial’,

            @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=3 — SubSystem: CMDExec

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=11 — SubSystem: SSIS

GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’SSISProxy’, @login_name=N’PkgSSIS’

GO

O subsystem de Id 3 e 11 são os subsystem para utilizar CmdExec e SSIS..No modo gráfico é possível verificar que eles estão “ticados”:

Clique na gui “Principals” e verifique que o login PkgSSIS está marcado como SQL Login:

Tudo isso, foi feito no script anterior. Isso está sendo mostrado para que o leitor consiga identificar o que está sendo criado e onde fica.

Calma galera! Já estamos quase lá. Agora vamos ver onde essas informações devem ser atribuídas no Job. Neste meu caso o job já foi criado, apenas colocarei os prints aonde tive que modificar.

O owner do job, que executará será o login: Pkgs:

No steps podemos ver que o job é um pacote SSIS. Temos apenas que mudar quem executa o nosso job que não será mais a conta do SQL Server Agent e sim no Proxy.

Agora iremos executar o job e “Eureca”.

O que tem dentro do pacote? Apenas uma procedure que executa um comando update sem Where dentro de uma proc. A procedure é chamado dentro de um “Execute SQL Task” do SSIS. Não existe nada de funcionalidade, pois, o post é apenas pra mostrar que é possível executar um pacote sem ter que dar permissões “absurdos” para um login especifico.

Espero que tenha ajudado.

Abs