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());
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);
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();
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);