Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange

Turns out this is an issue in the SQL Server code and the error is a bit noisy during attach database.

When opening the database files, SQL Server calls SetFileIoOverlappedRange (when enabled properly) in order to help improve I/O performance. This is commonly done under the SQL Server, service account; which requires locked pages privilege. When the privilege is not held the Windows error (1314 - A required privilege is not held by the client) is logged in the SQL Server error log, shown below.

Starting with SQL Server 2005, when attaching a database, SQL Server impersonates the client connection when opening the files to validate proper security (ACLs.) In doing so the SQL Server invokes SetFileIoOverlappedRange under the impersonated account and not the SQL Server, service account. This can lead to the 1314 error condition.

The error is more noise than a problematic issue. Using ALTER DATABASE OFFLINE and ONLINE will re-open the database files under the SQL Server, service account and allow SetFileIoOverlappedRange to complete successfully for the database.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

Oct 19 2012 13:38:57

Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

2013-10-16 03:23:20.010 Server Using locked pages in the memory manager.

2013-10-16 09:21:35.970 spid52 Starting up database 'dbAttachTest'.

2013-10-16 09:25:10.300 spid52 SetFileIoOverlappedRange failed, GetLastError is 1314

WARNING – Make sure you have the following applied to avoid unexpected issues as well.