Today’s quest is similar to the blog post that I wrote two months ago about auditing security changes: we’re admin of our non-production environnements of SQL Server and we’d like to put in place some auditing. In this case we’d like to store all the login attempts on our database/server.
The core issue is that we have many users and logins on our databases and we have huge doubt their respective needs. The root cause is identified: sometimes, for a short period of time, we’re making exceptions to our own rules and let a few other friend projects access to our DEV database. On some other cases, we’re connecting our own solution in DEV environnement to the QA environnement of another solution. Why … planning, data quality issue, … we’ve valid reasons to do it … but these exceptions should be removed as soon as possible. And you know what? People forget. Nowadays, on our largest solution, we have 20 users but only 7 of them are expected and documented … other should be removed. But before executing this cleanup, we’d like to be sure that these users are not effectively used by other solutions. If it’s the case, we’ll need to update first the configuration of the corresponding solution.
To know if a user is effectively in use on a database, you need to capture the attempts of connexion (success and failure). Technically, you’ve several ways to implement this in SQL Server but I decided to use the audit feature to accomplish this task.
The first step is always to create an audit on the server or database (here, I’d like to track the whole server).
create server audit [Audit Login Informations] to file ( FILEPATH = N'N:\Temp\Trace\' , MAXSIZE = 1024 MB , MAX_FILES = 10 , RESERVE_DISK_SPACE = OFF ) with ( QUEUE_DELAY = 1000 , ON_FAILURE = CONTINUE , AUDIT_GUID = '33e055a9-1924-47c3-9777-e8875a5bcd37' );
This audit must be activated
alter server audit [Audit Login Informations] with (state=on); go
Then the specification of this audit must be described. In our case we’ll intercept five groups of events related to login attempts (successful or not) and to contained database or not.
create server audit specification [Audit Login Informations specification] for server audit [Audit Login Informations] add (FAILED_DATABASE_AUTHENTICATION_GROUP) , add (FAILED_LOGIN_GROUP) , add (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP) , add (SUCCESSFUL_LOGIN_GROUP) , add (DATABASE_LOGOUT_GROUP) with (state=on); go
By experience this kind of script is probably more useful and less breakable, if you add a cleanup phase before creating your audit and its specification.
Let’s start by testing if the audit is already existing. The view
sys.dm_server_audit_status will let you check if the audit is existing and if it’s running. If it’s the case then you need to stop it and drop it (and its specification).
if exists( select * from sys.dm_server_audit_status where name='Audit Login Informations' ) begin alter server audit specification [Audit Login Informations specification] with (state=off); drop server audit specification [Audit Login Informations specification]; alter server audit [Audit Login Informations] with (state=off); drop server audit [Audit Login Informations]; end
That’s it! You can now query your audit to get all the connection attempts on your server by the means of this simple query:
declare @LogFilePath varchar(255); set @LogFilePath = N'N:\TEMP\Trace\'; declare @AuditName varchar(255); set @AuditName = N'Audit Login Informations'; select @LogFilePath=log_file_path from sys.server_file_audits where name=@AuditName; set @LogFilePath=@LogFilePath + REPLACE(@AuditName, ' ', '%5') + '*.sqlaudit'; select session_server_principal_name ,database_name ,object_name ,m.class_type_desc ,statement ,convert(datetime, switchoffset( convert(datetimeoffset, event_time) , datename(TzOffset, sysdatetimeoffset()) ) ) from sys.fn_get_audit_file (@LogFilePath,default,default) f inner join sys.dm_audit_class_type_map m on m.class_type=f.class_type order by event_time desc;