Problem attaching a database file to my SQLServer using Integrated Security

[MichelVanderbeke]

Hello everyone,

I have a problem attaching a database file to my SQLServer using Integrated Security.
Depending on where my .mdf and _log.LDF files are located it works or doesn’t work.

The connectionstring in the app.config-file of a C# project is:

providerName="System.Data.SqlClient"
connectionString="Data Source=PREDATORG9-793;
AttachDbFilename = D:\_Michel Vanderbeke\Documents\Visual Studio 2017\Projects\Michels gegevens\Michels gegevens\Gegevens\Michels gegevens.mdf;
Integrated Security=true;
Connect Timeout = 30"

This way the program runs fine.

When I move the .mdf file to another location and adjust the connectionstring to:

providerName="System.Data.SqlClient"
connectionString="Data Source=PREDATORG9-793;
AttachDbFilename = C:\Test_C\Michels gegevens.mdf;
Integrated Security=true;
Connect Timeout = 30"

I get an error message:
{"Unable to open the physical file \"C:\\Test_C\\Michels gegevens.mdf\". Operating system error 5: \"5(Access denied.)\".\r\nAn attempt to attach an auto-named database for file C:\\Test_C\\Michels gegevens.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

Can someone help me with this error? Why can’t I put the database-files on a different location? The only difference between the two connection strings is the map where the database-files are.

Many thanks and greetings,

Michel

 

[fredericofonseca]

The user under which the SQL Server instance is executing requires access to the folder where the MDF file is located - that is the error you are getting.

Nothing to do with your authentication to the server.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

There is a reason for the folder MSSQL puts its own master/temp etc databases, the system account of the service has access to it.

If you need to automate granting file permissions, you may use cacls.exe or icacls.exe for that:

https://technet.microsoft.com/en-us/library/cc753525(v=ws.11).aspx

A list of well known SIDs to use as icacls parameter is here:

https://support.microsoft.com/en-us...rity-identifiers-in-windows-operating-systems

eg S-1-5-20 for NT Authority/Network Service

Bye, Olaf.