In the previous articles of this series, we discussed the reasons behind auditing the different SQL Server instance and database events, how to audit the SQL Server instance manually and using the Extended Events and Triggers methods. In this article, we will show how to audit your SQL Server instance by reading the content of the SQL Transaction Log file of the user database.
Read more »
This article will provide an overview of manually creating a SQL Server audit using SQL Server Extended events and triggers. It will provide an overview of SQL tracing/profiling as well as extended events. I’ll walk you through how to create a new extended events session and how to use it, in a worked example, to audit failed logins. Finally, we’ll touch on an auditing approach using triggers
Read more »
A SQL Server audit can be performed using various number of methods. This includes using the built-in SQL Server Audit feature, using third party tools from the SQL Server market or simply perform the audit task using the legacy methods manually. In the previous article of this series, SQL Server audit overview, we discussed the main concept of a SQL Server audit, the importance of auditing the SQL Server instance changes and the SQL server audit checklists. In this article, we will go through the manual procedures for auditing the SQL Server instance.
Read more »
To fill an optimal role in all business’s computerized operations, information technology systems and infrastructure should be guaranteed, well controlled, aligned with the business goals and secured. Technology auditing ensures the evaluation and examination of these business requirements on an organization.
Read more »
In this article we are going to talk about Static Data Masking, a new feature for the SQL Databases in SSMS. Static Data Masking feature previously was available only for the Azure SQL DB.
SQL Server 2019 offers powerful new features to help in safeguarding your data and complying with various privacy regulations, which we’ll be covering in this article
Read more »
This collection of content presents an overview of top SQL Server database auditing tools, with product page links for each of the tools and screenshots if available. If you noticed a tool we missed, please let us know in the comments below.
All database auditing tools are sorted by alphabetical order.
Read more »
Business transformation requires solid tools to automate complex integration to seamless deployments. In today’s modern data-rich world, nothing is more important than data management, making it critical to know how to safeguard and meet compliance requirement is very critical and the key to the business success.
Read more »
In this article, we’ll discuss how to read SQL Server transaction logs. This article should provide and insight into how the workload is being captured in the transaction log files. We are going to see how to get the transaction meta-data details and the history of the data will give us a clear idea of how the system is working and helps to decide peak usage hours, also helps to log information about who is doing what?
Read more »
SQL Server auditing has gone from a nice to have to a legal requirement, especially following new legislation like HIPAA and GDPR. Organizations are now tasked with auditing access to records, reporting suspicious and potentially malicious activity, forensically auditing data changes, as well are tracking login attempts, security changes and much more.
Read more »
Our organization must restrict permissions and prove to an independent party that we investigate access and restrict permissions. We restrict permissions to objects using least permissions and give full access to an exceptional few based on an organizational design that follows best practices. What can we do additionally that will help us prevent unauthorized access or catch when someone who shouldn’t have access is able to infiltrate our systems, especially in the context of showing a third party that we track this.
Read more »
Auditing is a key feature in any application or any system as it provides end users with better analysis for administrators. Apart from analysis, auditing can be used as a troubleshooting mechanism too. Apart from organizational reasons, there are compliance reasons for enabling auditing depending on the domain of operation.
Auditing is mainly about answering four questions, i.e. who, when, what and where. However, depending on the situation, it might be decided what questions of the mentioned four should be answered.
Read more »
In a previous article “SQL Server Audit feature – discovery and architecture“, we’ve seen the basics to build an auditing system based on SQL Server Audit feature. In the introduction, we talked a little bit about the « General Data Protection Regulation » a.k.a. GDPR that allows personal data circulation (in Europe) that is a subject in the mouth of a lot of IT professionals.
Read more »
Intended audience
This document is intended for database administrators who plan to develop, deploy, assess or implement auditing solutions in Microsoft SQL Server on Microsoft Windows platform.
Read more »
Audit tables are used to track transactions for a particular table or tables. For every important transaction table, it’s important to create an audit table. Auditing can be helpful if you need to track who is Inserting/Updating and Deleting data on a sensitive table and/or view before and after data change information.
Read more »
Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.
As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.
Read more »
In multi-user environments, changes may occur frequently to the architecture, data, or overall structure that creates work for other users. In this series, we look at some ways that we can track changes on the data and architecture layer for pin-pointing times, changes, and using the information for alerting, if changes should be kept to a minimum. SQL Server comes with some built-in tools that allow us to monitor changes, and depending on the architecture, we can create tools that allow us also to monitor and identify changes near the time that they occur.
Read more »
As a part of a Big Data project, we are often asked to find the best way to track the changes applied to the database tables, so that, no requirement is created to load all the huge tables to the data warehouse database at the end of the day, if not all of the data was changed.
Read more »
As a DBA I am often asked to provide lists of all active users ID’s or groups for a specific server or database. In addition to this, I am also asked to provide a list of failed logins. These requests are frequent enough that I decided to automate the process of gathering this data and provide two nicely formatted HTML emails. I am not going lie, manager types love these reports, and anything that makes managers love my work I am all about! I’m also all about automating anything that makes my job easier. Call me lazy or call me prepared, I hate having to do something over and over that I could easily throw into an SSIS package or Agent Job and just schedule it to do it for me. This entire process consists of using SQL Server Integration Services (SSIS), 4 tables and a SQL Agent Job containing 2 reports (Failed Logins and Active SQL Server Users). The SSIS package, along with all of the queries and scripts are attached at the end of the article.
Read more »
With the advent of the Information Era, data is being collected on a massive scale. Information Technology systems have made access to this data faster and easier. It has also made it easier for data to be misused.
Read more »
The purpose of security auditing is to identify all attacks, unlawful or malicious activities which might be taking place on your server. Criminals have become increasingly inventive and as a DBA you might not have considered or even be aware of all activities which may be putting your data at risk.
Read more »
Deciding what method to use to collect audit information can be a task in and of itself. Depending on your needs there are a number of different ways to collect the information. There are settings such as Login Auditing and Common Criteria Compliance that are fairly simple to implement but only collect very specific sets of information. There are also coding solutions such as Triggers and Audit Columns that are very flexible but can be tricky to get right. Then there are Profiler Traces but those have been deprecated and will be removed in a future version of SQL Server. Their replacement, Extended Events, has become easier and easier to use in recent versions and can be used to collect a huge variety of information. However Extended Events does not include any Audit specific information. If you are setting up an auditing solution in SQL Server you are probably going to want to consider using SQL Server Audits. Audits are based on Extended Events and so have all of the benefits of Extended Events but also include the audit specific events. Audits have a different interface than Extended Events and a different set of T-SQL Commands. Fortunately they are very easy. Read more »
In the world of information, auditing serves an important purpose. It helps to provide an assurance that the data involved is accurate and safe. The level of assurance of course depends on any number of factors including the level of trust in those performing an audit (or collecting the data for the audit), the frequency the data is collected, and the types of data collected.
Any time the collection of data is this important to a process you can bet that a DBA is going to be involved. We get asked to create and run queries to pull data (frequently ridiculously complex queries, and rarely some simple ones). You could almost make it into a joke. Read more »
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy