Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue

ERROR
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

Cannot use file 'D:\MSSQL\Data\AttachDemoDatabase.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Problem (Cause):
May be the contained file disk not added to the cluster resource
OR
Disk not added as SQL Server resource dependency
Check as below
Goto to failover cluster manager -->  Expand Roles --> select Role --> goto Resources Tab --> Right click on SQL Server properties and
In SQL Server properties window click on dependencies there you need to see disks information

 


Resolution:
SQL Stopping you to create Database on D drive which is not a dependency of the SQL Server. Dependency tell OS the order in which they can come online. The child resource must come online before an attempt is made to bring parent resource online in a cluster. And this chain is an important aspect of SQL Clustering. A resource group has several resources in it. All the resources in a single resource group act as a single group. They failover as a one unit; Thus providing high availability on the available nodes.
Need to add this new drive to the dependency chain for SQL Server Resource Group.
--> Goto Failover Cluster Manager

--> Under the cluster name select Role
--> In right hand pane Select the Resource Group that contain SQL Server Resources.
--> In bottom window  select Resource Tab
--> Under Other resources Right click on SQL Server as shown in below image

--> In properties window Click on Insert Button

--> On add a row select Share Disk D that stores you database data files (.mdf)
--> In next row add all other required share disk which will stores your database log files and your server database backups and tempdb files.

-->  Make Sure that all resources are required (there is an AND next to subsequent resources) then click OK

Now you are good to go to attach your .mdf file to create database with existing data file.

Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON in SQL Server

When you got below error while executing below SQL Statement

INSERT INTO dbo.tlbTargetTable SELECT * FROM dbo.tlbSrcTable

Msg 8101, Level 16, State 1, Line 19

An explicit value for the identity column in table 'dbo.tlbTargetTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SET IDENTITY_INSERT dbo.tlbTargetTable ON

Go

INSERT INTO dbo.tlbTargetTable SELECT * FROM dbo.tlbSrcTable

Go

SET IDENTITY_INSERT dbo.tlbTargetTable OFF

Go

You will get below error message after switching on identity insert

Msg 8101, Level 16, State 1, Line 20

An explicit value for the identity column in table 'dbo. tlbTargetTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

You have to add column list for insert statement

SET IDENTITY_INSERT dbo.tlbTargetTable ON

Go

INSERT INTO dbo.tlbTargetTable (id, col1, col2, col3, col4, col5) SELECT id, col1, col2, col3, col4, col5 FROM dbo.tlbSrcTable

Go

SET IDENTITY_INSERT dbo.tlbTargetTable OFF

Go