We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access is denied on SqlFileStream()

MainMa
MainMa asked
on
Medium Priority
7,134 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

Comment
Watch Question

Rahul Goel ITILSenior Consultant - Deloitte
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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).
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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.

Commented:
Still waiting for some sort of help on this...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@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 ?

Author

Commented:
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)?
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
*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.

Commented:
@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.

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


Thanks

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.