Errors creating an SQL server database on a freenas SMB share

[DiDiMau]

Environment

• Freenas 9.10
• Windows 2012R2 server
• Microsoft SQL server 2012

I have an SMB share called Databases a server called freenas. In SQL management studio running on the windows server with SQL login sa.
This command to create the database fails

Code:

CREATE DATABASE [TestDB3] ON  PRIMARY
( NAME = N'TestDB3', FILENAME = '\\freenas\Databases\testing\TestDB3.mdf' ,  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

And generates this error

Code:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "\\freenas\Databases\testing\TestDB3.mdf". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

But the database file IS created \\freenas\Databases\testing\TestDB3.mdf and written to. I think unsupported calls are being made to samba on freenas or freenas is not configured to support some feature.

Any workaround for this? The guest account has full rights on \\freenas\Databases\testing\ and CREATE DATABASE works on another share on a other windows server.

I have a tcpdump trace file which was created on freenas as I tried to create the database. It contains some confidential information so I cant post it here. Here are screen shots from the final SMB 2 request and response.

Please let me know if you need anything else.

TIA DDM..

Request

Response

 

[MatthewSteinhoff]

Your directory/file permissions are the problem. Create/delete are controlled by the directory's permissions. Modify is a file permission.

My guess is that the directory permission is set to allow creation but the file, once created, is unable to be modified. Check your creation masks.

You can prove this out... create a new file (hello: Notepad) in the directory. Close the file. Open the file. Change something. See if you can save the file. If not, it's a permissions problem. I don't think it has anything to do with the database specifically.

Cheers,
Matt

 

[DiDiMau]

Thanks Matt. I can save and edit the a text file without error.

 

[MatthewSteinhoff]

From the same host with the same credentials as the database server?

Cheers,
Matt

 

[DiDiMau]

Yes and also with file mask 0744 and no difference. I know SQL management studio changes file rights on the database when a database is detached or attached when logged into SQL management studio with a windows user account. The fix for that was to login to SQL server with an SQL user account but maybe management studio is still trying to do something strange.

 

[DiDiMau]

Actually just to clarify. I am logged into the Windows server with an domain administrator account and starting SQL management studio then logging into SQL server with an SQL login. When I edited the text file the rights would be those of the domain administrator account. The question is when creating the database is it SQL management studio or some delegated process.