Início / dba / Perguntas / 285002
Accepted
Peter Å
Asked: 2021-02-10 00:13:52 +0800 CST

Auditoria de conexões de banco de dados no SQL Server 2008 R2

  • 772

Estamos finalmente planejando encerrar nosso último servidor SQL Server 2008 R2. Antes de prosseguirmos com isso, precisamos rastrear quais usuários estão se conectando a quais bancos de dados. Tentamos diferentes opções, como usar o SQL Audit e verificar os logins no log de erros, mas em todas elas faltam as informações para qual banco de dados a conexão é feita. Também analisamos o uso do gatilho LOGON, mas como ele é acionado quando o usuário faz login na instância, ele não rastreará se o usuário alterar o banco de dados posteriormente.

Nas versões mais recentes, usei Eventos Estendidos para esse tipo de auditoria, mas os eventos sqlserver.login ainda não estão disponíveis nesta versão do banco de dados. Parece que deve ser possível usar o Profiler para isso, mas estou preocupado com a execução contínua de rastreamento, especialmente neste servidor antigo. Existem outras opções que eu perdi ou o Profiler é a única opção?

sql-server sql-server-2008-r2

3 respostas

  1. Eu enfrentei um problema semelhante há alguns anos e criei um trabalho do SQL Agent para executar a consulta abaixo a cada poucos minutos. A consulta pesquisou sys.dm_exec_sessions, sys.sysprocesses, sys.dm_exec_connections, é um pouco áspera e pronta, mas atendeu aos meus propósitos e pode ajudá-lo também

    CREATE TABLE [dbo].[log_db_connections](
[Database] [nvarchar](128) NULL,
[Login] [nvarchar](128) NOT NULL,
[nt_user_name] [nvarchar](128) NULL,
[LoginTime] [datetime] NOT NULL,
[CollectionTime] [datetime] NOT NULL CONSTRAINT [DF_CollectionTime] DEFAULT GETDATE(),
[Program] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[Host] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[net_transport] [nvarchar](40) NOT NULL,
[net_packet_size] [int] NULL,
[protocol_type] [nvarchar](40) NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NOT NULL,
[auth_scheme] [nvarchar](40) NOT NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL
) ON [PRIMARY]
GO



-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [CIX_log_db_connections] ON log_db_connections
(
    [Database] ASC,
    Login ASC,
    Host ASC,
    Program ASC,
    LoginTime ASC
) WITH (IGNORE_DUP_KEY = ON)

INSERT [dbo].[log_db_connections]
(
[Database],
[Login],
[nt_user_name],
[LoginTime],
[Program],
[client_interface_name],
[Host],
[client_net_address],
[client_tcp_port],
[net_transport],
[net_packet_size],
[protocol_type],
[endpoint_id],
[encrypt_option],
[auth_scheme],
[num_reads],
[num_writes]
)
SELECT
DB_NAME(p.dbid) ,
s.login_name ,
nt_user_name,
s.login_time as LoginTime,
s.[program_name] as Program,
s.client_interface_name,
[host_name] as Host,
c.client_net_address,
c.client_tcp_port,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.endpoint_id,
c.encrypt_option,
c.auth_scheme,
c.num_reads,
c.num_writes
FROM sys.dm_exec_sessions s
inner join sys.sysprocesses p on
s.session_id = p.spid
inner join sys.dm_exec_connections c on
s.session_id = c.session_id
WHERE s.[program_name] not like 'Microsoft SQL Server Management Studio%'
    • 1
  2. Best Answer

    Usando uma sessão de evento abaixo, você pode obter informações sobre lotes concluídos. As informações incluem database_id e nome de usuário:

    CREATE EVENT SESSION [QueryBatchTracking] ON SERVER 

ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),

ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.nt_username,sqlserver.session_id,sqlserver.username,sqlserver.sql_text)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0))))

ADD TARGET package0.asynchronous_file_target(SET filename=N'd:\ExEvents\QueryBatchTracking.xet')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION QueryBatchTracking 
ON SERVER STATE = START 
GO

    Além de obter apenas eventos de login, você obterá informações sobre as ações que o usuário realizou.

    • 1

  3. Acho que minha pergunta é por que você precisa saber quais bancos de dados estão sendo conectados pelos usuários? Saber quais usuários estão fazendo login no servidor provavelmente deve responder a todas as perguntas que você precisa para seu novo servidor, já que você planeja encerrar todo o servidor antigo de qualquer maneira. Depois de conhecer os logins dos usuários que se conectam ao servidor, você pode visualizar seus bancos de dados mapeados por meio da pasta Segurança no nível do servidor no SSMS e, presumivelmente, você deseja mapear seus usuários para os mesmos bancos de dados em seu novo servidor. Não há necessidade de tentar registrar granularmente quais bancos de dados estão sendo conectados .

    Além disso, se um usuário se conecta ao banco de dados A, mas executa uma consulta que acessa uma tabela do banco de dados B (já que a consulta entre bancos de dados é certamente possível), qual banco de dados você gostaria de saber que o usuário usou? Por exemplo:

    USE DatabaseA;

SELECT SomeColumn 
FROM DatabaseB.dbo.Table1

    O acima é executado no contexto de DatabaseA.

    • 1

relate perguntas