Auditing SQL Server is a necessity for identifying potential security issues and ensuring that the integrity and confidentiality of critical data is maintained. In addition to this, auditing SQL Server is a compliance requirement for many regulations including PCI DSS and HIPAA.
Visibility over exactly where important SQL database files are stored is also essential for several reasons. These include the facilitating of backup and recovery processes, transferring files to another location, or to locate a folder if a server instance was installed in a non-standard location.
There are two file extensions used by SQL Server for storing data:
- MDF files: These are data files that hold data and objects such as tables, indexes, stored procedures, and views.
- LDF files: These are the transaction log files that record all transactions, and the database modifications made by each transaction.
The default SQL server data file location path depends on the version of the Microsoft SQL Server software and these are as follows:
SQL Server 2017 — C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\
SQL Server 2019 — C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\
SQL Server 2022 — C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\
Native Methods to View SQL Server Database File Locations
The two native methods are either by using the SQL Server Management Studio (SSMS) or alternatively by using a T-SQL query. One disadvantage of using the SSMS method is that it allows you to check only one database at a time; however, a disadvantage of writing and executing a T-SQL query is that it requires expertise and time.
Using the SQL Server Management Studio (SSMS): This is done by right-clicking on the instance name and navigating to the Database Settings tab.
Using a T-SQL query:
- Start Microsoft SQL Server Management Studio (MSSMS)
- On the File menu, click Connect Object Explorer.
- In the Server type list box, select Database Engine
- In the Server name box, type the name of the SQL cluster server
- In the Authentication list box, choose your SQL Server Authentication method and specify the user credentials. If you do not want to re-type the password every time you connect to the server, check Remember password.
- Click Connect
- Upon connection, click New Query and paste the following script into the query field:
SELECT
mdf.database_id,
mdf.name,
mdf.physical_name as data_file,
ldf.physical_name as log_file,
db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)),
log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))
FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf
JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf
ON mdf.database_id = ldf.database_id
- Click Execute (or hit the F5 key).
- Review the list of SQL database locations in the query execution results:
How Lepide can Help
With the Lepide Solution implemented into your organization, all aspects of SQL server can be audited with ease. All changes made to the configuration of SQL users, permissions, logins, triggers, databases, and other SQL elements are audited. The raw logs are aggregated into meaningful audit data, saved long-term in a SQL Server database, and displayed in pre-defined audit reports. You can customize these reports to create a long audit-trail to understand how a change was made and to track a specific critical change.
Here is an example of the All Database Object Changes Report from the Lepide SQL Server Auditor:
This report is run as follows:
- Select Lepide Auditor, Reports
- Select All Database Object Changes from SQL Server reports
- Specify a Date Range
- Select Generate Report
The report is generated and can be filtered, sorted and exported to CSV and PDF format.
