Solved

Access is denied on SqlFileStream()

Posted on 2009-07-02
10
4,986 Views
Last Modified: 2012-05-07
Help me to resolve an error.

When running SqlFileStream(), it throws "Access is denied" error.
The code is also tested from a Windows user account having all privileges on the whole SQL server, the effect is the same.

Query at line 16 changes the data successfully.

What happens? Why access is denied for FileStream and not for direct query?


The only thing I found on the web is that Integrated Security must be set to true (already set). Other pages tells about impersonation in ASP.NET, which does not seem very helpful in a context of a local application.
private static void DiskToDb(String SourceFilename)

{

    Trace.WriteLine("Running under " + System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString() + " account.");
 

    // Modified from http://msdn.microsoft.com/en-us/library/cc716724.aspx

    SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();

    scsb.DataSource = "<Server name here>";

    scsb.InitialCatalog = "MediaRepository";

    scsb.IntegratedSecurity = true;

    

    Trace.WriteLine("Connection string: " + scsb.ToString());

    

    SqlConnection connection = new SqlConnection(scsb.ToString());

    connection.Open();

    SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

    SqlCommand cmd = new SqlCommand("UPDATE [MediaRepository].[dbo].[ProductDemos] SET [MediaData] = CAST('hello' as varbinary(max)) WHERE [FileName] = 'Test.avi'", connection, transaction);

    cmd.ExecuteNonQuery();

    transaction.Commit();

    transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
 

    SqlCommand command = new SqlCommand("SELECT [FileName], MediaData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM [ProductDemos] WHERE [FileName] = 'Test.avi'", connection, transaction);

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())

    {

        String DbFilePath = reader.GetString(1);

        

        Trace.WriteLine("File path: " + DbFilePath);

        

        byte[] transactionContext = reader.GetSqlBytes(2).Buffer;
 

        FileStream readStream = new FileStream(SourceFilename, FileMode.Open);

        SqlFileStream writeStream = new SqlFileStream(DbFilePath, transactionContext, FileAccess.Write, FileOptions.SequentialScan, 0); // *** Throwing error ***
 

        // From http://www.developerfusion.com/code/4669/save-a-stream-to-a-file/

        int Length = 256;

        Byte[] buffer = new Byte[Length];

        int bytesRead = readStream.Read(buffer, 0, Length);

        while (bytesRead > 0)

        {

            writeStream.Write(buffer, 0, bytesRead);

            bytesRead = readStream.Read(buffer, 0, Length);

        }
 

        readStream.Close();

        writeStream.Close();

    }

    transaction.Commit();

}

Open in new window

0
Comment
Question by:MainMa
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 9

Expert Comment

by:Rahul Goel
ID: 24769535
You must use Integrated Security for FILESTREAM (and subsequently SqlFileStream) to work properly.

Second, whatever user your application is running under must have proper access to the database your FILESTREAM lives in.  This access includes reads and writes.
0
 

Author Comment

by:MainMa
ID: 24770880
To Rahu_ketu_patal:

Well, Integrated Security is set to true (is it the only thing which is required?) and as I said previously, I also tested the application from a user having full privileges on the whole server (including R/W privileges for the database used).
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 24806673
FILESTREAM is owned by the SQL Server service account.

SQL Server 2008 can store blobs in its own private NTFS namespace rather than in the database itself. The database contains pointers to that namespace (and it's contents are named for SQL server so no longer represents the original doco name). SQL Server effectively owns that folder containing those blobs.

So, it is the SQL Server (and agent) accounts that need access to that folder, and best via a domain accout for those services. The advantage is that SQL server manages it and you can hide all of it from the outside world.

Now, from the manual : "to access the FILESTREAM BLOB by using Win32, Windows Authorization must be enabled."

So, you will need to create a domain user, give them access to those folders / directories add them or associate them with a SQL login and then login using that SQL login.

One "gotcha"... The only account that is granted NTFS permissions to the FILESTREAM container is the account under which the SQL Server service account runs. So, you MUST use the OpenSqlFilestream APIs


There is a really good whitepaper : http://msdn.microsoft.com/en-us/library/cc949109.aspx

And a discussion : http://blogs.msdn.com/rdoherty/archive/2007/10/12/getting-traction-with-sql-server-2008-filestream.aspx

And an example : http://msdn.microsoft.com/en-us/library/cc716724.aspx  and http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2008/09/08/10729.aspx

And everything else you ever wanted to know about filestream (as a developer) : http://msdn.microsoft.com/en-us/library/bb895234.aspx

0
 

Expert Comment

by:rowanshr
ID: 24964870
I am getting the same error. I can only upload from particular folder which has 'Evernone' read/write access to it. From other folders, I am getting this error.

Any one please help.
0
 

Expert Comment

by:rowanshr
ID: 24975495
Still waiting for some sort of help on this...
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24975832
@rowanshr :

This is MainMa's question, not so sure that messages like "Still waiting" will help your cause.

Have you gone through all the above ?

Are you using the API's ? you MUST use the OpenSqlFilestream...

How are you accessing, and when do you get the error ?
0
 

Author Comment

by:MainMa
ID: 24975904
Sorry, I didn't answer for some time.

Thanks to mark_wills for responding.
I didn't look yet at the links. I will do it... well... later.

About folder permissions, just one thing: first, I tested the program from an domain account having entire permissions on the directory where FileStreams were stored. Secondly, I noticed that if I run the program on the server (i.e. where SQL Server is installed) but from the very same domain account, FileStreams work well.

IMHO the most strange thing is that when I type in SQL Management Studio an SQL query writing a FileStream, it works well. Why does it work from Management Studio but not from an application (running from the same domain account and with a full trust)?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24975961
*laughing* that is a very good question - doesn't quite seem to make much sense does it...

There seems to be some level of confusion and conflicts from the Windows / Winapi side of things compared to T-SQL as evidenced in http://msdn.microsoft.com/en-us/library/cc645941.aspx

Not exactly sure of the real reason, but think it stems from the whay in which the different interfaces address the filespace. Basically, windows or client apps are using handles whereas SSMS is using directly. That is also why you need to encapsulate the transaction and use the API's - to get that file handle.
0
 

Expert Comment

by:rowanshr
ID: 24992274
@mark_wills:
Thank you very much for your input... I couldn't resolve it, but I found one site(one of the expert member from this site,named Jinal, suggest me to use) works for me. I am still new in SQL server 2008, so need more input on this.

Here is the link:
http://weblogs.asp.net/aghausman/archive/2009/03/16/saving-and-retrieving-file-using-filestream-sql-server-2008.aspx

All of sudden the access denied message gone. Only problem with this code is:

You will get "The network path was not found" error message, if your SQL server is in different machine.

Here is the discussion link for it.

http://www.experts-exchange.com/Microsoft/Server_Applications/Q_24613221.html


Thanks
0
 

Author Comment

by:MainMa
ID: 25061076
Quite strange, but I am testing now the old source code from backup which always failed with "Access is denied" exception, and now it works locally and from any other domain PC. Don't really know why, but the problem is solved (I hope).

Thank you for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now