SQL server can't access network share

Article
04/14/2015

Question

_{Tuesday, April 14, 2015 12:39 PM}

Hello,

I am facing an issue where the SQL server can't access the network share. The network share is mounted by a domain user who is also configured as Administrator on the server.

Through SQL Server management studio as well as PoweShell script when i try to access the share in order to attach the database contained within the share, i get the error "Unable to find the file specified". I can't mention the network drive to the SQL management GUI file browser too if i try to attach the database through GUI.

For all SQL services, I changed the service log on account to be the same account which mounts the network share.

As work around -

I launched a command prompt under NT Authority \ SYSTEM (LocalSystem) account and mounted the drive to the same location which lead to adding one more connection but under local system account. With this change, the SQL server was able to access the network share!

I couldn't understand whey SQL expects the network share to be mounted under local system account even if all the SQL services are running under domain account ? I want SQL to be able to access the network share without opting for above work around.

All replies (8)

_{Wednesday, April 15, 2015 10:56 AM ✅Answered}

I am backing up the SQL server database and storing it on host A. Later on in certain scenarios, the user may have to read some data from backed up SQL database (not the entire database). The SQL server is not hosted on host B. Please note that in no circumstances, SQL server will be hosted on the same server where the database files are present. I must do my implementation with this deployment model. Hence only way, the SQL server gets access to the database is by using network share. I am mapping the share to a local drive.

And again: this is a very bad idea. Use a UNC path. Don't use drive letters.

And overall, attaching a database on network share - drive letters or UNC path - is dubious and unsupported. If you attach the database as read_only, it work better.

If this does not fit with your current plans, you need to back to the drawing-board and make a new design.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Wednesday, April 15, 2015 2:24 PM ✅Answered}

I also still agree with the others, hosting a database file on a NAS will cause problems and ends up in poor performance.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Tuesday, April 14, 2015 9:30 PM}

Are you trying to access the network share with a drive letter? You should use an UNC path.

Having a database on a network share is terrible idea, and as I recall it is not supported.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Wednesday, April 15, 2015 12:17 AM}

SQL Server will not allow to attach the database for files which are in network share folder. Even though if you try to map the nwetwork share with drive letter also not allowed.

You must ask to your SAN engineer to create a drive to the machine and copy the files and try to attach.

Microsoft generally recommends that you use a Storage Area Network (SAN) or locally attached disk for the storage of your Microsoft SQL Server database files because this configuration optimizes SQL Server performance and reliability. By default, use of network database files (stored on a networked server or Network Attached Storage [NAS]) is not enabled for SQL Server.

Still if you want to use more please refer: https://support2.microsoft.com/default.aspx?scid=kb;EN-US;304261

You can enable the Trace Flag 1807 and attach database it on your OWN RISK. please refer the above KB article

Thanks,

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

_{Wednesday, April 15, 2015 2:52 AM | 1 vote}

Hello,

I couldn't understand whey SQL expects the network share to be mounted under local system account even if all the SQL services are running under domain account ? I want SQL to be able to access the network share without opting for above work around.

It doesn't have to be. The workaround is only a temporary fix and is certainly not a good idea. I would not use mounted folders with SQL Server, at all, period.

The reason you're having difficulty is that when the folder is mounted, it's mounted for YOUR session not ALL sessions. SQL Server has already started its services and is running under its' own session and is not privy to the things you do to change the environment. For example, if you changed a system or user variable (for example, a SET variable) SQL Server would have no idea about this because when it started, that variable either didn't exist or was something else. This is not SQL Server's fault but how Windows works (this information is stored in the Process Environment Block [PEB]) for any windows process.

As other's have said, don't use mounted drives - use a UNC path. UNC paths are 100% supported on 2012+ as well as running databases on a SMB 3.x share (UNC pathed of course).

If you absolutely must use a mounted drive, enabled xp_cmdshell access (through sp_configure) and run the net use command through xp_cmdshell. I, once again, would highly advise not doing this and using an UNC path.

-Sean

The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

_{Wednesday, April 15, 2015 5:25 AM}

Thank you all for your suggestions.

Let me explain the scenario within which i am doing this. My ultimate goal is to achieve everything programmatically. I am trying few steps manually at the moment just to see of things work as i perceive them to be.

I am backing up the SQL server database and storing it on host A. Later on in certain scenarios, the user may have to read some data from backed up SQL database (not the entire database). The SQL server is not hosted on host B. Please note that in no circumstances, SQL server will be hosted on the same server where the database files are present. I must do my implementation with this deployment model. Hence only way, the SQL server gets access to the database is by using network share. I am mapping the share to a local drive.

The drive to which network share is mapped is seen when i do net use and i can browse through it using explorer, command prompt as well. The network share is mapped by the domain user and hence the drive can be seen when net use is invoked within the log on session of domain user.

The SQL server throws file not found errors when i try to attach database contained within the share. Please note i changed the service log on account for all SQL services to be the same domain user who maps the drive. I found many posts where there were need for SQL to access the network share which were resolved after changing service account for SQL services.

It works only with the above workaround which i have mentioned above which i want to avoid.

_{Wednesday, April 15, 2015 5:42 AM}

the user may have to read some part of backup SQL database (not the entire database). The SQL server is not hosted on host B. Please note that in no circumstances, SQL server will be hosted on the same server where the database files are present.

Hello,

You can not "read part of backup" and regarding hosting a database file on a NAS see the following lins:

SQL Server and Network Attached Storage (NAS)

SQL Databases on File Shares - It's time to reconsider the scenario.

Install SQL Server with SMB Fileshare as a Storage Option

Description of support for network database files in SQL Server

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Wednesday, April 15, 2015 7:23 AM}