CREATE DATABASE with FILESTREAM Access Denied

[Scott]

Using SQL Server 2008 Express.
Have FILESTREAM enabled for Transact-SQL Access and file I/O streaming access.
Checked on server: Filestream Access Level is "Full access enabled".

I can create a database (with out a FILESTREAM) and all works.

Yet when I try to create a database with FILESTREAM the following error
occurs:
Msg 5120, Level 16, State 106, Line 1
Unable to open the physical file "c:\data\filestreamP1". Operating system
error -2147024891: "0x80070005(Access is denied.)".

Below is the script used:
CREATE DATABASE PathNameDB
ON
PRIMARY ( NAME = ArchX1, FILENAME = 'c:\data\archdatP1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = ArchX3, FILENAME =
'c:\data\filestreamP1')
LOG ON ( NAME = ArchlogX1, FILENAME = 'c:\data\archlogP1.ldf');

Anyone know how to get around this error for FILESTREAMS?
Thanks.

[Erland Sommarskog]

"Access is denied" can mean one of two things: 1) lack of permission.
2) attempt to perform an operation on a file in use.

If the above command without FILESTREAM works, I would assume that the
service account has permission to write to C:\data. I don't know anything
that creating directories would need a special permission, but I'm not
a Windows guy.

Thus, I would conclude that C:\data\FilestreamP1 already exists, and
therefore SQL Server cannot create it.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Scott]

Since I can create a database without using FILESTREAM to C:\data then SQL
Server has permission.
C:\data\FilestreamP1 does not exist so SQL Server should be able to create
this directory but it falis.

[Scott]

More information found in the event viewer log:

STREAMFCB::Startup: Operating system error 0x80070005(Access is denied.)
occurred while creating or opening file 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\filestream1'. Diagnose and correct
the operating system error, and retry the operation.

[Erland Sommarskog]

Scott (Sc...@discussions.microsoft.com) writes:
> More information found in the event viewer log:
>
> STREAMFCB::Startup: Operating system error 0x80070005(Access is denied.)
> occurred while creating or opening file 'C:\Program Files\Microsoft SQL
> Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\filestream1'. Diagnose and
> correct the operating system error, and retry the operation.

That's not C:\Data.

Does the service account have access to this directory?

On which operating system are you on? If you are on Vista or SQL 2008,
start SSMS by right-clicking and select "Run as Administrator" and try
again.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Scott]

SQL Server is running under NT AUTHORITY\NETWORK SERVICE.
The O.S. used is Windows XP SP3.

The directory C:\Data had its file permission set so NETWORK SERVICE has
Full Control. I would of thought that if SQL Server did not have permission
to C:|Data then even a database create without FILESTREAM woud not work but
it does.

So SQL cannot create the FILESTREAM folder when trying to create a database
with FILESTREAM.

Below is the error using C:\Data

STREAMFCB::Startup: Operating system error 0x80070005(Access is denied.)

occurred while creating or opening file 'c:\data\filestreamP1'. Diagnose and

correct the operating system error, and retry the operation.

[John Bell]

Hi Scott

Does "c:\data\" exist and does the SQL Server service account have
permissions to create directories in it. "C:\MyFiles\MyFilestreamData"
should not exist.

John

"Scott" <Sc...@discussions.microsoft.com> wrote in message
news:3CE85D59-D04C-4187...@microsoft.com...

[Scott]

Yes, C:\Data does exist.

The directory C:\Data had its file permission set so NETWORK SERVICE has
Full Control. I would of thought that if SQL Server did not have permission
to C:|Data then even a database create without FILESTREAM woud not work but
it does.

The filestream directory was not created so it does not exist under C:\Data

Scott

[John Bell]

I would expect it to work if it has full permissions to do that. What you
can do is change the service account to local system and try. If that works
it a permissions issue with Network Service.

John
"Scott" <Sc...@discussions.microsoft.com> wrote in message

news:1F5DC2C2-A232-4F41...@microsoft.com...

[Erland Sommarskog]

Scott (Sc...@discussions.microsoft.com) writes:
> SQL Server is running under NT AUTHORITY\NETWORK SERVICE.
> The O.S. used is Windows XP SP3.
>
> The directory C:\Data had its file permission set so NETWORK SERVICE has
> Full Control. I would of thought that if SQL Server did not have
> permission to C:|Data then even a database create without FILESTREAM
> woud not work but it does.
>
> So SQL cannot create the FILESTREAM folder when trying to create a
> database with FILESTREAM.

I did some googling around, and it seems that other people who had the same
error have been able to fix it by changing the service account to a
domain user.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:

[Scott]

Found that this is a bug in SQL Express 2008 when using NETWORK SERVICE.
Apparently patch KB956572 (http://support.microsoft.com/kb/956572/) caused
this problem.

This link describes problem:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=435855

In addition there seems to be no fix in sight but a work around is included.