How to fix Error 1802 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 1802 – CREATE DATABASE Failed

Why This Error Happens

Error 1802 typically occurs when the SQL Server service account lacks the necessary NTFS permissions to write to the target directory where the database files (.mdf, .ldf) are being created. This can also happen if the specified path does not exist, if a file with the same name already exists and is locked, or if the disk volume has insufficient space. Additionally, this error can surface if the SQL Server service is running under a virtual account that has not been granted explicit access to a non-default data directory.

Step-by-Step Resolution Guide

Step 1: Verify the Target Directory Exists and Has Space

  • Open File Explorer or PowerShell and confirm the destination path (e.g., D:\SQLData\) is valid and spelled correctly in your CREATE DATABASE statement.
  • Run Get-PSDrive D in PowerShell to check for sufficient free disk space on the target volume.
  • Ensure there are no leftover orphaned files (e.g., DBName.mdf) from a previously failed creation attempt in that folder.

Step 2: Grant the SQL Server Service Account Proper NTFS Permissions

  • Right-click the target folder (e.g., D:\SQLData\) and select Properties > Security > Edit.
  • Add the SQL Server service account (commonly NT Service\MSSQLSERVER or a custom domain service account) and grant it Full Control.
  • Apply the permissions to the folder, subfolders, and files, then click OK to save.
  • Restart the SQL Server service via services.msc to ensure the token refreshes with the new permissions.

Step 3: Check for File Locks or Antivirus Interference

  • Use Resource Monitor (resmon.exe) or Process Explorer to check if another process (such as a backup agent or antivirus scanner) has a lock on the .mdf or .ldf file path.
  • Add an exclusion for the SQL Server data directory (e.g., D:\SQLData\*.mdf, *.ldf) in Windows Defender or your third-party antivirus console to prevent real-time scanning conflicts.

Step 4: Validate the SQL Server Default Paths Configuration

  • Open SQL Server Management Studio (SSMS), right-click the instance, and select Properties > Database Settings.
  • Verify that the “Database default locations” for data and log files point to valid, accessible paths.
  • If using a command-line approach, re-run the creation script explicitly defining the FILENAME parameter to bypass potentially misconfigured defaults:
  CREATE DATABASE TestDB
  ON PRIMARY (NAME = TestDB_Data, FILENAME = 'D:\SQLData\TestDB.mdf')
  LOG ON (NAME = TestDB_Log, FILENAME = 'D:\SQLData\TestDB_log.ldf');

Alternative or Verification Steps

  • Verify the Fix: Re-run the CREATE DATABASE command via SSMS or sqlcmd. If successful, confirm the physical .mdf and .ldf files appear in the target directory using File Explorer.
  • Alternative Solution: If permissions issues persist, create the database in the default SQL Server directory first (usually C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) and then use ALTER DATABASE ... MODIFY FILE combined with a detach/re-attach process to move it to the desired volume once permissions are correctly configured.