Category Archives: בלוג שרתים ושיווק

Auditing techniques for SQL Server databases

Monitoring (auditing) a SQL Server database does not only serve to comply with the requirements of the compliance rules. It is a practice that has become necessary for analyzing database actions, solving problems, investigating suspicious or fraudulent activities. It can also be useful to prevent inappropriate actions by users, as if there were a closed circuit TV system on its databases.

There are different auditing techniques for SQL Server:

  • Manual Auditing – can be created to meet specific requirements, but implementation takes a significant amount of time and can easily lead to errors
  • Using Extended Events in SQL Server – easy to set up, you can monitor a wide range of actions, but it does not provide information about what has been deleted / inserted nor the old and new values ​​for updates; moreover, detailed auditing can cause performance problems
  • Use SQL Server triggers – easy to set up but can cause performance problems for databases with a large number of transactions
  • Read the transaction log – it is not required to capture additional data because SQL Server already tracks the changes. Provide additional memory space and keep in mind that some actions (such as EXECUTEs) are not recorded
  • Use SQL Server Profiler and SQL Server traces – flexible and complex. It has some difficulty in reading and filtering the records

Identifying the most correct solution depends on your environment, what you need to observe, where you plan to record the captured actions and the way you intend to represent the data.

Manual auditing

A SQL Server database can be put under observation using ad hoc stored procedures and functions for tracking changes on data and objects. This provides a flexible solution which, however, involves a great deal of writing and code development, thus increasing the cost and the implementation period.

Use SQL Server Extended Events

SQL Server Audit is a feature introduced by the 2008 version that uses Extended Events to monitor SQL Server actions. It allows different verification actions, providing many details during the setup process and covering a wide range of activities in SQL Server.

To create a new Audit object in SQL Server:

  1. In SQL Server Management Studio, expand Security and right click on Audits
  2. Select New Audit
  3. Specify in the name of the audit, choose whether to save the data in the application event log, security event log or file and then set a destination for the audit file.
  4. Click OK and your audit will appear in the Audits node of the Management Studio Object Explorer
  5. By default the audit is disabled and for this it is shown with a red arrow. To enable it, right click and select Enable Audit.
  6. Choose between Server Audit Specification or Database Audit Specification depending on whether you want to monitor activity at the SQL Server instance or database level.
  7. To create a Database Audit Specification, expand the database node you want to monitor, go to Security click with the right to Database Audit Specification and choose New Database Audit
  8. In the Create Database Audit Specification dialog box, specify a name, associate the specification with the audit object created in step # 1, and specify the activities to be verified in the Audit Action Type. For a database auditing, it indicates the database, the object or schema as Object Class, the name of the audit object and the login

You can see all the activities to be monitored with SQL Server Auditing in the drop-down menu for Audit Action Type

The Principals to be selected in this dialog box are actually the users that will be monitored

As with audits, the Database Audit Specification is disabled by default. To enable them, select this option in the context menu.

SQL Server 2019: premises

Microsoft's flagship database is an important tool, with local and in-cloud versions offering powerful archiving and analysis tools. It also becomes an important application for data scientists, providing a framework for the construction and testing of machine learning models. There's a lot in SQL Server, and a new version can show you where Microsoft thinks your data needs will go in the next few years.

The latest CTP for SQL Server 2019, version 2.1, is now available to help evaluate and test the next version outside of production environments. Like its predecessor, it is available in Windows and Linux versions, although support for containers and Kubernetes has now been added. Adding container support, using Docker and the Linux version of SQL Server, is an interesting option as it allows you to create SQL Server in huge analytical engines based on Kubernetes that work with data lakes hosted by Azure using Apache Spark .

The current preview installer offers the option of a basic, quick and fast installation or a more detailed custom installation. The first option requires less disk space because they are the files needed to perform the basic installation, while a custom installation reduces the entire installation support of SQL Server 2019. For most of the basic development tasks it is a basic installation is sufficient, although we recommend a custom installation as part of a complete evaluation. You can also download the installation media if you plan to install it on more than one computer to evaluate the capabilities of the SQL Server cluster.

Machine learning is an important part of SQL Server 2019 and now includes integrated tools for creating and testing machine learning models. You can install it with support for the popular R and Python languages, so your data science team can work within the database, prepare and test models before you can format them on your data. Microsoft is using its own Open R distribution and the Anaconda Data Science Python environment, which includes additional numerical analysis libraries, including the popular NumPy.

You can also install SQL Server 2019 as a self-learning environment for machine learning. Local SQL Server instances on developer workstations will be able to use R and Python familiar tools to work directly with training datasets, without affecting production systems or server resource utilization.

Really BIG data

Working with large-scale data has long been a problem, with very few database engines designed to function as part of a distributed architecture. With SQL Server 2019 it is now possible to create what Microsoft calls Big Data Clusters, using a mix of SQL Server and Apache Spark containers on Kubernetes using the existing PolyBase capabilities of SQL Server. With public clouds that support native Kubernetes, you can deploy Big Data Clusters on Azure, AWS, and GCP as well as your infrastructure. Integration with Azure Data Studio tools simplifies the creation, execution and sharing of complex queries.

Microsoft's focus on data science scenarios fits perfectly with the intelligent cloud / business intelligence strategy. Data is essential to create machine learning tools and, by executing R and Python code within the database, it is possible to send complex queries from the SQL Server command line, using familiar tools to create and test the code before distributing and executing it. . Microsoft is providing sample code through GitHub, which shows how to combine relational data with big data. It also shares sample architectures that show how to use this as a basis for the creation of machine learning systems as well as open source technologies such as Kafka.

Other new features, such as static data masking, focus on protecting and disinfecting data so that they can be used without affecting regulatory compliance. The application of static masking of data to columns in the export of a database allows developers to work with real data and avoid loss of sensitive information. There is no way to recover the original data, as it is a one-way process. Previous versions of SQL Server introduced dynamic data masking, which works only with the original database. Exporting with static masking there is little or no risk for developers to unmask or randomly alter data in real time, leaving them to produce code that can be put into production without any modification.

At the database level, when you create indexes you can now stop and start. If a disk is being filled, you can pause an indexing operation, add more memory to the volume, and then resume from where it left off. It is not necessary to start from scratch, saving time and calculations. There is also the possibility to reboot after errors, saving more time after correcting the error that caused an index to crash.

With SQL Server 2019, Microsoft is proving that even though relational databases have been around for a long time, there is still room for improvement and innovation. Building a database engine that works like every SQL Server has worked in the past, while supporting the work with machine learning and large amounts of data on a large scale, offers a tool ready to update what you have and to support you as work with your data securely, locally and in public clouds. All you have to do is download it and see what it can do for you.

Reporting and analysis of the MS SQL server registry

Reporting and analysis of the MS SQL server registry

Monitoring database activity is a fundamental task for database administrators, as it allows you to keep track of all events and changes that occur in the database, as well as ensuring the integrity of sensitive data stored in the database. Manual database control is a superhuman task, so the best way to do it effectively is to adopt a complete solution that simplifies and automates database and activity monitoring. The solution should also allow database administrators to:

  • Monitor all significant events, such as user accesses, accesses and privileged user activities;
  • Keep track of database transactions and provide comprehensive information on authorized and unregistered changes that reveal the perpetrator of the actions, the type of shares, the time and place where they were performed;
  • Instantly detect the root cause of operational problems and provide detailed information for easy and effective resolution;
  • Instantly identify unauthorized access to sensitive data.

Check the MS SQL server databases via the EventLog Analyzer

EventLog Analyzer from ManageEngine is a comprehensive database activity monitoring tool that helps monitor all database activity, accesses and changes to the server account in the MS SQL server database.

EventLog Analyzer also has the following features:

  • Support for the 2005, 2008, 2008 R2, 2012, Enterprise and Datacenter editions of the MS SQL server;
  • Predefined reports that provide detailed information about the DML and DDL query control;
  • Real-time warning in case of unauthorized access to confidential data or to the database server itself;
  • Efficient management of MS SQL server accounts, including monitoring of changes to privileged user accounts with detailed reports and instant alerts;
  • Intuitive report control panel that allows you to drill down to the level of the raw log, which displays the content collected by the machine, thus simplifying the analysis of the root cause.

MS SQL Server DDL Audit Report: Monitoring DDL changes

  • They allow you to monitor and track changes that occur at the structural level of the database, such as changes to tables, views, procedures, activations, schemas and more;
  • Thanks to intuitive graphical reports, it is possible to obtain details on who made this change, on when and from where it was performed;
  • They allow to obtain, in real time, notifications by e-mail or SMS for any modification of the DDL level.

Available reports 

DML control report of the MS SQL server: control of DML activities

  • Thanks to the predefined reports of EventLog Analyzer, it is possible to check the activities at a functional level that take place in the database;
  • They allow to know the moment in which the functional queries are executed, the author of the same and the place of execution;
  • They allow you to track instantly all the activities of changing confidential data, such as viewing, updating, deleting data or inserting new ones.

Available reports 

MS SQL Server Account Management Report: Accou management

Managing and monitoring database server accounts is very important for setting permissions for both internal and external database resources. EventLog Analyzer simplifies database account management with real-time alerts and pre-defined reports. With this solution it is possible to:

  • Keep track of all changes to the account in relation to users, logins and logouts, passwords, and more;
  • Know when a privileged account is created, deleted or modified;
  • Control the access and disconnection activity and know the reasons for access errors;
  • Know instantly when the password of a critical account is changed and more.

Available reports 

MS SQL server audit report: monitoring server activity

  • Thanks to the immediate reports of EventLog Analyzer, you can quickly check MS SQL server activities, such as startups, shutdowns, logins, logon errors and more;
  • They allow to obtain detailed reports on database backup, restore, control, control specifications, administrator authority and more;
  • They allow to know the best database access activities and to visualize the trends of possible access errors.

Available reports 

MS SQL server security report: decrease of database security attacks

EventLog Analyzer helps reduce internal and external security breaches by providing detailed reports and real-time alerts on the various security attacks that may occur on the database. This solution:

  • It provides detailed reports on SQL injection and Denial of Service attacks, which help to conduct a detailed forensic analysis on how to carry out the attack;
  • It notifies you in real time about events, including account lockouts, privilege abuse, unauthorized copying of sensitive data and more, thus helping you to react immediately to security breaches.