I am trying to work on a query to attach a database (UNC path) remotely. I have SQL Server management studio (SSMS) installed in SQL Server machine (machineSQL) and also in another machine (machineRemote) in same domain.
CREATE DATABASE "simpleData" ON (FILENAME = '\\machineRemote\ShareName\sample\sample_database.mdf'), (FILENAME = '\\machineRemote\ShareName\sample\sample_database_log.ldf') FOR ATTACH;
If I run the query in SSMS on machineSQL. The database gets attached successfully. But if I run it in SSMS on machineRemote it throws an error like :
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "\\machineRemote\ShareName\sample\sample_database.mdf". Operating system error 5: "5(Access is denied.)". Msg 1802, Level 16, State 7, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
NOTE : The issue is occurring only if windows authentication is used to log in SSMS. SQL Server authentication works just fine. I have tried domain user permission on the database file path and also tried running SQL Server services as domain user, still no luck.
Need help on this.