Dica – Exibindo informações sobre Logins utilizados em Autenticação SQL para SQL Server 2005, 2008 e R2.

Dica – Exibindo informações sobre Logins utilizados em Autenticação SQL para SQL Server 2005, 2008 e R2.

Anúncios

Pessoal, bom dia!

Tudo bem?  Graças a deus estou bem, trabalhando e estudando muito.

Bom, na dica de hoje vou destacar como podemos obter informações de um dos processos mais importantes do SQL Server, estou me referindo ao Processo de Autenticação de Usuários.

A autenticação de todo e qualquer usuário em uma aplicação é a técnica de identificação deste indivíduo, que indica para os mecanismos de segurança que esta tentando estabelecer o acesso ou conexão com uma Base de Dados ou qualquer outro tipo de objeto responsável em prover informações.

No SQL Server isso não é diferente, como todos devem saber existem dois tipos de Autenticação: 

  • Windows Authentication – Utilizando contas de usuários existentes na máquina local ou em seu Controlador de Domínio;

 

  • SQL Server Authentication ou Mixed Mode – Conhecido como Modo Misto que possibilita utilizar ao mesmo tempo contas de usuários Windows ou contas de usuário SQL Server.

 

Neste momento não vou me preocupar em querer definir qual dos métodos é mais seguro, este não é objetivo desta dica.

Muito bem, no código de exemplo utilizado nesta dica, você terá a possibilidade de obter uma relação de informações sobre as autenticações de usuários realizadas no SQL Server através do Mixed Mode.

A seguir apresento o Código 1:

CREATE TABLE #LoginInfo

(BadPasswordCount SQL_VARIANT,

BadPasswordTime SQL_VARIANT,

DaysUntilExpiration SQL_VARIANT,

DefaultDatabase SQL_VARIANT,

DefaultLanguage SQL_VARIANT,

HistoryLength SQL_VARIANT,

IsExpired SQL_VARIANT,

IsLocked SQL_VARIANT,

IsMustChange SQL_VARIANT,

LockoutTime SQL_VARIANT,

PasswordHash SQL_VARIANT,

PasswordLastSetTime SQL_VARIANT,

username VARCHAR(25))

 

DECLARE @BadPasswordCount SQL_VARIANT,

@BadPasswordTime SQL_VARIANT,

@DaysUntilExpiration SQL_VARIANT,

@DefaultDatabase SQL_VARIANT,

@DefaultLanguage SQL_VARIANT,

@HistoryLength SQL_VARIANT,

@IsExpired SQL_VARIANT,

@IsLocked SQL_VARIANT,

@IsMustChange SQL_VARIANT,

@LockoutTime SQL_VARIANT,

@PasswordHash SQL_VARIANT,

@PasswordLastSetTime SQL_VARIANT,

@username VARCHAR(25)

 

DECLARE useracct_cursor CURSOR FOR

 SELECT NAME FROM sys.server_principals

 WHERE (type = ‘S’ AND NAME NOT LIKE ‘##%’)

OPEN useracct_cursor

FETCH NEXT FROM useracct_cursor INTO @username

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

SET @BadPasswordCount = (SELECT loginproperty(@username, ‘BadPasswordCount’))

SET @BadPasswordTime = (SELECT loginproperty(@username, ‘BadPasswordTime’))

SET @DaysUntilExpiration = (SELECT loginproperty(@username, ‘DaysUntilExpiration’))

SET @DefaultDatabase = (SELECT loginproperty(@username, ‘DefaultDatabase’))

SET @DefaultLanguage = (SELECT loginproperty(@username, ‘DefaultLanguage’))

SET @HistoryLength = (SELECT loginproperty(@username, ‘HistoryLength’))

SET @IsExpired = (SELECT loginproperty(@username, ‘IsExpired’))

SET @IsLocked = (SELECT loginproperty(@username, ‘IsLocked’))

SET @IsMustChange = (SELECT loginproperty(@username, ‘IsMustChange’))

SET @LockoutTime = (SELECT loginproperty(@username, ‘LockoutTime’))

SET @PasswordHash = (SELECT loginproperty(@username, ‘PasswordHash’))

SET @PasswordLastSetTime = (SELECT loginproperty(@username, ‘PasswordLastSetTime’))

 

INSERT INTO #LoginInfo

(BadPasswordCount, BadPasswordTime,  DaysUntilExpiration,  DefaultDatabase,

 DefaultLanguage,  HistoryLength,  IsExpired,  IsLocked,  IsMustChange,

 LockoutTime,  PasswordHash,  PasswordLastSetTime,  username)

VALUES ( @BadPasswordCount, @BadPasswordTime,  @DaysUntilExpiration,  

@DefaultDatabase, @DefaultLanguage, @HistoryLength, @IsExpired,  @IsLocked,

@IsMustChange,  @LockoutTime,  @PasswordHash,  @PasswordLastSetTime,

@username)

 

FETCH NEXT FROM useracct_cursor INTO @username

END

 

CLOSE useracct_cursor

DEALLOCATE useracct_cursor

 

SELECT username AS ‘SQL Login Name’,

BadPasswordCount AS ‘Bad Password Count’,

BadPasswordTimeAS’Bad Password Time’,

DaysUntilExpiration AS ‘Days Until Expiration’,

DefaultDatabaseAS’Default Database’,

DefaultLanguageAS’Default Language’,

HistoryLengthAS’History Length’,

IsExpiredAS’Is Expired’,

IsLockedAS’Is Locked’,

IsMustChangeAS’Is Must Change’,

LockoutTimeAS’Lockout Time’,

PasswordHashAS’Password Hash’,

PasswordLastSetTimeAS’Password Last Set Time’

FROM #LoginInfo

 

DROP TABLE #LoginInfo

GO

Os dois elementos mais importantes para o funcionamento de código estão relacionados a System View:

  • sys.server_principals: Existente a partir do SQL Server 2005, tem a finalidade de retornar uma linha para cada Server Principal existente em uma instância SQL Server.

 

  • LoginProperty(): Existente a partir do SQL Server 2005, tem a finalidade de retornar informações relacionadas aos processos de Logon de usuários, com base, no seu conjunto de opções apresentados abaixo na Tabela 1.

Valor

Descrição

BadPasswordCount Retorna o número de tentativas consecutivas de fazer logon com uma senha incorreta.
BadPasswordTime Retorna a hora da última tentativa de fazer logon com uma senha incorreta.
DaysUntilExpiration Retorna o número de dias para que a senha expire.
DefaultDatabase Retorna o banco de dados padrão do logon do SQL Server como armazenado em metadados ou o mestre se nenhum banco de dados for especificado. Retorna NULL para usuários provisionados não-SQL Server; por exemplo usuários autenticados do Windows.
DefaultLanguage Retorna a linguagem padrão de logon como armazenado em metadados. Retorna NULL para usuários provisionados não-SQL Server, por exemplo usuários autenticados do Windows.
HistoryLength Retorna o comprimento de hora que o logon que tem controlado usando o mecanismo de imposição de diretiva de senha.
IsExpired Retorna informações que indicarão se o logon está expirou.
IsLocked Retorna informações que indicarão se o logon está bloqueado.
IsMustChange Retorna informações que indicarão se o logon deve alterar sua senha na próxima vez conectar.
LockoutTime Retorna a data em que o logon no SQL Server foi bloqueado porque excedeu o número permitido de tentativas de logon com falha.
PasswordHash Retorna o hash da senha.
PasswordLastSetTime Retorna a data em que a senha atual foi definida.

 

Tabela 1 – Relação de opções existentes para a função LoginProperty().

 

Bem pessoal, vou encerrando esta dica, espero que todos possam ter gostado e que este post possa ajudar.

Mais uma vez obrigado. 

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

5 comentários em “Dica – Exibindo informações sobre Logins utilizados em Autenticação SQL para SQL Server 2005, 2008 e R2.”

  1. Júnior, parabéns pelo artigo, muito bacana, tem muitas informações que eu nem tinha idéia que dava pra extrair da base, inclusive tenho até uma dúvida. C sabe pq quando migramos logins SQL SERVER a senha não é migrada?

    O HASH dela será que tem a ver com mac de placa de rede, nome do servidor, etc?

    Fiz uma migração recente e todos os logins com autenticação SQL Server tiveram que ter suas senhas “setadas” novamente.

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s