Archive

Archive for September, 2012

Trabalhando com Credenciais e Proxy Account no SQL Server

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

Advertisements

Configurando o Operador no SQL Server

Mais uma vez, bom dia galera. Hoje esse será um post rápido, pois, ta muito tarde(Post Agendado..rs.) e está na hora de ir para a cama. Este post de hoje é dedicado ao um homem que sempre me espelhei e sempre me deus bons conselhos, meu Pai, José Adão Carlos.

Em um ambiente de banco de dados corporativo, o DBA sempre deve ter em mente uma coisa: Como automatizar meu trabalho, para que sempre possa agir com pró-atividade? A resposta é meio simples,  automatize e sempre seja avisado dos problemas antes de eles começarem aparecer. Na prática não é tão fácil, mas, tente manter isso em mente.

No post anterior eu falei sobre database email e como configurá-lo. Mas, até ai beleza, o que faço com isso? Vamos automatizar nossas rotinas!?

Imagine que você criou um job de backup, e que ele deve ser feito toda  noite. Esse job é um job critico, e no caso dele falhar você precisa ser informado. Criaremos um operador, que trará a lista dos emails que devem ser informados. Além do meu e-mail adicionarei o pessoal do suporte que fica 24×7, assim, a cobertura da solução é maior.  Com o Database e-mail configurado e o operador criador, apenas temos que mudar uma opção no JOB em questão. Let’s go.

Expanda o item SQL Server Agent e clique com o botão direito sobre Operator e em seguida em New Operator.

Aparecerá a seguinte tela:
Name: Nome do Operador.
Email name:  Lista de emails que irão receber a notificação (os emails devem ser separados por ; )

Preencha os dados e clique em OK.

Notificação

Após estes procedimentos as notificações devem ser adicionadas nos Jobs.

Clique em um job com o botão direito vá em propriedades. Em notification coloquem as seguintes configurações:

Espero ter ajudado….

Abs e até o próximo post

Configurando o Database Email

Olha eu aqui de novo galerinha! Muita correria pra poder estar sempre atualizando o blog, mas, no final sempre é gratificante. Esse post, como de costume dedico ao Emerson Vasconcelos,  super líder de projetos e ótimo técnico.

O Database Mail foi à solução que a Microsoft encontrou para o envio de mensagens de E-Mail, o Database Mail fornece um recurso de notificação para as instâncias do SQL Server. Quando o Database Mail é configurado em um computador com SQL Server, os aplicativos de banco de dados podem enviar mensagens de e-mail aos usuários com resultados das consultas, o recurso Database Mail é usado principalmente para enviar mensagens de alerta para os administradores, a fim de notificá-los sobre as condições de desempenho ou alterações feitas em objetos.

O Database Mail foi introduzido no SQL Server 2005 como substituto SQL MAIL, que estava presente em versões anteriores ao SQL Server 2000, o motivo para essa substituição foi simplificar a configuração, gerenciamento e fornecer um envio de mensagens de maneira rápida e segura. Para transmitir as mensagens de correio. O Database Mail usa o protocolo de envio de mensagem SMTP (Simple Mail Transfer Protocol), esse protocolo utilizada a porta padrão 25 em uma rede TCP.
O ponto central dentro do Database Mail são os perfis, para cada perfil criado podemos associar várias contas de e-mail obtendo assim, um recurso que vamos falar detalhadamente nos próximos artigos o Failover, isso quer dizer o seguinte, um perfil do Database Mail lhe permite especificar mais de um servidor SMTP. Caso um servidor SMTP fique indisponível, as mensagens poderão ser entregues por outro servidor SMTP.

Após acessar o Management Studio e conectar no servidor que irá configurar, acesso o Object Explorer , expanda o item Management e clique com o botão direito no item Database Mail e depois no item Configure Database Mail.


Clique em Next e adicione nome para o profiler, a descrição e  clique em Add.

Aparecerá a próxima tela as informações devem ser preenchidas como a seguir:

  • Account Name: Um nome descritivo para essa conta
  • Description: Uma descrição para a conta
  • E-mail Address: O e-mail a ser utilizado para envio
  • Display Name: O nome a ser exibido quando os e-mails são encaminhados
  • Reply E-Mail: Opcionalmente o e-mail que aparecerá no campo “Responder para” na mensagem de e-mail
  • ServerName: O Servidor de e-mail
  • PortNumber: A porta utilizada para realizar a conexão (usualmente a porta 25)
  • Autenticação: Configure conforme a política de autenticação de seu servidor.. Nesse usamos a conta SQLServices do domínio.


Clique em Ok e termos a tela do profiler com o acount configurado.

Clique em Next. Marque o CheckBox e deixe o profiler SQLServices como default.

Clique em Next e aparecerá a janela de configuração dos parâmetros.


Pode ser proibido o uso de alguns arquivos dentro do Database Email, basta clicar na caixa com as reticências na linha Prohibited Attachment File Extensions irá se abrir uma janela com as extensões de arquivo. Neste caso adicionei o mp3

 Clique em OK e na tela de parâmetros clique em Next. Aparecerá a tela de finalização.

Clique em Finish. Aparecerá a janela com os passos que foram realizados e o status.

Clique em Close.

Habilitando o Data Base Email no SQL Server Agent

Vá ao SQL Server Agent, clique o botão direito sobre ele e escolha a opção Properties.

Clique em Alert system, marque a opção Enable Mail Profiler e escolha o profiler SQLServices que foi configurado anteriormente.

A sua tela deve ser parecida com esta:

Espero ter ajudado.

Abs

Categories: Administração Tags: ,

O que é o DTA?

Amigos da comunidade, blz? Esse post é dedicado a um outro amigo de trabalho. O DBA Felipe Melo , conhecimento SQL Geek em replicação, tunning e T-SQL. Sempre trocamos uma “figurinha” nos desafios profissionais, afinal, sempre é bom ter uma outra opinião. A certeza é: Nunca estamos totalmente certos! Mão na massa!?

O Database Engine Tuning Advisor é uma das “novas” features do SQL Server, ele analisa arquivos de carga de trabalho e propõe alterações no banco de dados, a fim de melhorar seu desempenho geral.

Para as alterações propostas, o Tuning Advisor também mostra o impacto que causará cada modificação.

Entre as suas capacidades, estão:

  • Query Optimazer, para propor índices e visões indexadas;
  • Recomendação de particões;
  • Análise de impacto das recomendações;
  • Fornecimento de informações sobre o número de consultas e o número de índices.

Opções de ajuste:

  • Quais objetos o Tuning Advisor poderá recomendar;
  • Quais partições analisará;
  • Quais estruturas serão mantidas no banco de dados;
  • Espaço máximo para recomendações;
  • Número máximo de columas por índice.

Criando analises com o DTA

Para iniciar o DTA, vá em StartAll / Programs / Microsoft SQL Server 2008 / Performance Tools / DataBase Engine Tunning Advisor.

Quando o programa for iniciado, clique em File/New Connection.

Aparecerá a tela de conexão para qual servidor você deseja criar a análise

Obs: Para o exemplo será usado um ambiente de testes.

O servidor será mostrado no canto superior direito da tela, clique com o botão direito e escolha a opção New Session.

A seguinte tela será mostrada:

Clique na guia Tunning Options e será mostrado a seguinte tela:

Physical Design Structure(PDS) to use in database: Esta opção avalia o que a Engine do DTA irá analisar referente a objetos de design na estrutura do banco(índex,índex views e etc). A opção de Índex views é desabilitada no SQL Server STD.

Partitioning strategy to employ: A opção de particionamento, verifica se existem objetos que podem ser particionados e avalia o particionamento que já existente. Disponível somente na versão Enterprise do SQL Server.

Physical Design Structure(PDS) to keep in database: Avalia a estrutura fisica do banco, abalia se existem índices clustereds e nonclustereds devem ser deletados(ele não deleta os indices), ou se existe algo divergente na no modelo como um todo. O padrão é Keep All existing PDS.

Voltando a guia General, aonde:

  • Session Name: Nome da Sessão
  • WorkLoad: A origem de ondes virão os dados que serão analisados(no caso do profiler o mesmo pode ser um arquivo ou uma tabela)
  • DataBase for WorkLoad Analysis: Aonde serão gaurdadas as analises temporarias.
  • Select DataBase and tables to tune: Lista de banco de dados e de tabelas que serão parte da analise. (sempre coloque os bancos de dados que [*] foram filtrados no profiler)

Coloque o nome no profiler e escolha o caminho aonde está o arquivo de trace do profiler. Ao clicar no Radio Button File, clique no binóculo a esquerda e será aberta a tela do Windows. Escolha o profiler que deseja analisar.

Clique em abrir.

Caso o trace tenha sido guardado em uma tabela, marque o Radio Button Table, clique no binóculo a esquerda e será aberta a tela de conexão:
Escolha qual banco, schema e tabelas estão os profilers que foram armazeandos.

Obs:  O login qual abriu a sessão no DTA deve ter acesso as tabelas que estão os traces, caso contrário, uma mensagem de erro será retornada.

 

O preenchimento deve ficar parecido com a tela abaixo:

 

Clique no botão Start Analysis. Após clicar em Star Analysis, uma tela de progresso será exibida.

 

Na guia recomendações, é apresenta a porcentagem de melhoria da análise.

 

No menu Actions aparecerá a opção de aplicar as recomendações ou savá-las.
Clique em Save Recommendations

Será gerado um arquivo .sql com as recomendações.

Espero Ter ajudado

 

Atualizando a Edição do SQL Server 2008

Caros, bom dia.

Este post é dedicado ao meu amigo Lucas Souza, um DBA com quem venho aprendendo muito. Está sempre me acompanhando nos processos aqui na empresa e me instruindo.

Hoje vamos falar sobre um assunto bastante simples, porém, foi a primeira vez que tive que realizar o procedimento, atualizar a edição do SQL Server. Hoje temos aqui na empresa o ambiente de desenvolvimento, produção, testes e homologação. Junto com a equipe começamos uma iniciativa de padronizar a instalação do SQL Server, isso inclui Edição utilizada, service pack e cumulative updates. Após baixar o SP2 da versão do SQL Server 2008 R2, comecei a aplicar a instalação no ambiente de desenvolvimento, testes e homologação. Quando me deparei com um ambiente de homologação que está com a versão Standard do produto. E agora? Preciso reinstalar o SQL Serve novamente? Claro que não…

Vamos ao passo-a-passo

Inicialmente você deve copiar o instalador do SQL Server para a máquina qual deseja atualizar. De um duplo clique no setup.exe a seguinte tela será apresentada:

Clique em Yes e aparecerá o SQL Server Installation Center. No lado esquerdo clique em Maintenance.

Clique no Edition Upgrade, após isso, apenas devemos seguir os passos. Vamos fazer juntos?

O SQL Server irá fazer uma verificação, se todos os itens verificados passarem por essa verificação, prossiga. Caso algum item falhar, recomendo que seja ajustadoo item em questão e o processo seja refeito.

É altamente recomendado que todos os itens sejam aprovados no check que o SQL Server faz, só assim podemos garantir que não teremos problema futuramente.

Clique em OK.

Esse processo é bem parecido com o processo de instalação, após o OK confirmado na tela acima, aparecerá a opção da chave do produto, Clique em Next:

Marque o checkbox de “Aceito os termos” e clique em next:

Na próxima tela, podemos perceber que o instalador traz a versão atual do produto e para qual instancia será feito o “upgrade”.

Clique em next.O SQL Server fará uma verificação antes de lhe dar a opção de Update.

Clique em next. Você verá um sumario bem importante, que diz para qual versão está sendo modificado o SQL Server, qual ação está sendo tomada (No caso Edition Upgrade) e a lista de features que sofrerão com esta atualização:

Clique em Upgrade. Após isso é só aguardar o SQL Server terminar de atualizar todos os binários necessários para essa atualização. Quando isso acontecer, reinicie a máquina.

Obs: No post não foi focado cuidados que devem ser tomados no caso dessa atualização. É uma boa prática sempre realizar backup dos databases de sistema, quando for realizar alguma alteração que afeta as configurações da instancia do SQL Server

Espero ter ajudado

Abs

Trabalhando com Certificados no SQL Server 2008

Boa tarde pessoal.

Este post é dedicado a um amigo da empresa, Rogério Santini, extremamente inteligente, sempre me da os conselhos certos.No post anterior falamos sobre a importância de utilizar database roles para controlar o acesso aos servidores de banco de dados através das aplicações. Hoje iremos falar sobre um caso real que foi aplicado aqui na nossa empresa. Temos nossas aplicações executando as procedures através de database roles, porém, existe uma migração acontecendo que estamos trabalhamos com informações se sistemas legados.

Vamos partir do nosso exemplo anterior onde tínhamos uma tabela de clientes com a coluna IdCliente que tem a propriedade identity de 1,1. Imagine que essa tabela será populado pelo nosso sistema atual. A cada inserção, será atribuído um valor incremental, até ai nenhuma novidade, comportamento normal. Agora aparece uma variável em nossa implantação. O sistema legado que temos, ainda ficará em funcionamento, sendo assim, ele precisará realizar inserções em nossa tabela de clientes. Thiago não entendi!? Aonde você quer chegar? Vamos lá meu caro blog…rs.

O sistema legado já existe os seus IdClientes, com uma sequencia própria dele, neste caso, as procedures farão uso da opção SET IDENTITY_INSERT. E dai? Como já tenho permissão de execução de procedures no sistema, nada muda certo? ERRADO. Para realizar a operação de SET IDENTITY_INSERT o usuário deve ter permissão de DDL_ADMIN. No momento que liberar essa permissão, o login da aplicação pode criar qualquer objeto dentro do banco de dados e esse é um risco muito grande. Thiago então qual a solução? Vamos lá bloguinho. A partir do SQL Server 2005, aprimorou-se as features de segurança do produto. Uma das “novidades” é o uso de certificados e máster key. Podemos realizar o uso dessas features, para fazer com que o login que chamou a procedure possa executar a mesma com o comando set identity_insert sem ter permissão de DDL_ADMIN. Vamos ver como isso funciona?

Vamos se conectar ao banco de dados dbPermissoes e alterar a procedure stp_ins_cliente que usamos no exemplo anterior para pode adicionar o comando SET_IDENTITY_INSERT.

alter  PROCEDURE dbo.stp_ins_cliente(@Id int, @Nome VARCHAR(50), @Cpf CHAR(11))

AS

begin

       SET IDENTITY_INSERT dbo.Clientes ON

            INSERT INTO dbo.Clientes(IdCliente, NomeCliente,CpfCliente) VALUES(@id, @Nome, @Cpf)

      SET IDENTITY_INSERT dbo.Clientes OFF

 END

go

Executando a procedure após de alterada, temos a seguinte mensagem de erro:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Msg 1088, Level 16, State 11, Procedure stp_ins_cliente, Line 5

Cannot find the object “dbo.Clientes” because it does not exist or you do not have permissions.

A mensagem acima é clara, ou não tenho permissão ou o objeto não existe. Segundo que a hipótese da permissão é verdadeira. Vamos dar permissão de DDL_ADMIN para o login e testar a execução, para garantir que é isso mesmo.

USE [dbPermissoes]

GO

EXEC sp_addrolemember N’db_ddladmin’, N’AplSistema’

GO

Executando novamente:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Vejamos o resultado e o ID 8 foi inserido.

Iremos remover o login do database role do DD_ALMIN e faremos a solução “Elegante”.

Criaremos o database máster key dentro da base dbPermissoes:

use dbPermissao

go
CREATE MASTER KEY ENCRYPTION BY password = ‘MkE2012@#’

Criamos o certificado:

CREATE CERTIFICATE CrtPermissoes WITH SUBJECT = ‘Schema user Certificate’

Agora precisamos criar um usuário para a base de dados em questão. Esse usuário é o que chamamos de “without login”. Quando criamos um usuário no banco de dados que não está atribuído a nenhum login, a sintax é completamente simples. Na criação do usuário, atribuo ele aio certificado:

CREATE USER UsrCert FROM CERTIFICATE CrtPermissoes

No momento da mágica, apenas realizamos o comando abaixo:

GRANT ALTER ANY SCHEMA TO UsrCert ADD SIGNATURE TO dbo.stp_ins_cliente BY CERTIFICATE CrtPermissoes

GO

Esse comando da permissão de “ANY SCHEMA” para o usuario UsrCert que criamos, e essa permissão é atribuida pelo certificado CrtPermissoes. Como o Usuario AplSistema tem permissão de execução de procedures a solução torna-se viável e “clean”.  Iremos remover o login do DDL_ADMIN e realizar uma execução de insert, inserindo o IdCliente 666.

USE [dbPermissoes]

GO

EXEC sp_droprolemember N’db_ddladmin’, N’AplSistema’

GO

Executamos a procedure:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 666 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

E BAZINGA!!!!!

Brinquedinho de Processamento

Galera, boa noite.

Pra descontrair!! Esse é o meu mais novo brinquedinho……

 

Por enquanto o SQL Server dorme…