Solved

SQL Filestream in VB 2010 --- Access Denied error

Posted on 2010-08-26
12
1,285 Views
Last Modified: 2012-05-10
I'm getting an access denied error when I trying to copy a file that I load into a filestream
that I then send to a byte array which is then sent to a Stored Procedure on an SQL Server 2008.

The SQL Server procedure works through a local querry on the SQL Server. The filestream is also
poperly configured and setup.

Here is my vb code... I also included a copy of the error I get as a JPG.

    Public Shared Sub WriteSQLFileStream(ByVal pathSource As String)
        'Dim cnn As SqlConnection
        Dim tx As SqlTransaction = Nothing
        Dim Description As New String("TEST")
        Try
            Using cnn As New SqlConnection(My.Settings.RVSoftMainDataConnectionString)

                'Open a connection and start a transaction
                cnn.Open()
                tx = cnn.BeginTransaction

                'Insert empty blob
                Dim cmd As New SqlCommand("FSInsert", cnn, tx)
                cmd.CommandType = CommandType.StoredProcedure
               
                Dim r As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                'Read the id and sql path back from the procedure
                r.Read()
                Dim ID As String = r(0).ToString
                Dim path As String = r(1).ToString
                r.Close()

                'Get the transaction token, required for filestream operations
                Dim cmd2 As New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", cnn, tx)
                Dim obj As Object = cmd2.ExecuteScalar

                Dim txCtx As Byte()
                txCtx = CType(obj, Byte())

                'Open the special managed "SQLFileStream" and write bytes
                Try
                    Using fsSource As FileStream = New FileStream(pathSource, _
                        FileMode.Open, FileAccess.Read)
                        ' Read the source file into a byte array.
                        Dim bytes() As Byte = New Byte((fsSource.Length) - 1) {}
                        Dim numBytesToRead As Integer = CType(fsSource.Length, Integer)
                        Dim numBytesRead As Integer = 0

                        While (numBytesToRead > 0)
                            ' Read may return anything from 0 to numBytesToRead.
                            Dim n As Integer = fsSource.Read(bytes, numBytesRead, _
                                numBytesToRead)
                            ' Break when the end of the file is reached.
                            If (n = 0) Then
                                Exit While
                            End If
                            numBytesRead = (numBytesRead + n)
                            numBytesToRead = (numBytesToRead - n)

                        End While
                        numBytesToRead = bytes.Length

                        'Write file to SQLStream
'''' Obviously this is where I get an error! ''''
                        Dim fs As New SqlTypes.SqlFileStream(path, txCtx, FileAccess.Write)
                        fs.Write(bytes, 0, numBytesToRead)
                        fs.Close()

                        tx.Commit()
                    End Using

                Catch ioEx As FileNotFoundException
                    Console.WriteLine(ioEx.Message)
                End Try
            End Using

        Catch ex As Exception
            'tx.Rollback()
            MessageBox.Show("The files stream operation failed ... Error: " & ex.ToString)
        End Try
    End Sub


If you have any comments about other parts of the code, I'm open to suggestion and more efficient code!

Thanks for your help!

Patrick Miron
error.jpg
0
Comment
Question by:PatrickMiron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33541400
Where is the FileStream configured to write to on the server?
0
 

Author Comment

by:PatrickMiron
ID: 33543402
Do you mean the data path of the Filestream? or do you mean the connection string?
  ---> Using cnn As New SqlConnection(My.Settings.RVSoftMainDataConnectionString)

I get a token back from the SQL Server and the connection strings works up to the line where I intentiate the SQLFileStream.

Or are you talking about the configuration of the SQL Server?
0
 

Author Comment

by:PatrickMiron
ID: 33545221
This is my script on the SQL Server side (stored procedure) to return a token...

CREATE procedure [dbo].[FSInsert]
WITH EXECUTE AS 'USERTHATHASALLSECURITYENABLED'
as
Declare @id uniqueidentifier
Set @id = NEWID()
insert into dbo.BillImages (ImageFSID, ImageFS)
values (@id, CAST('Image Placeholder' as varbinary(max)))
select imageFSID, ImageFS.PathName() from dbo.BillImages where ImageFSID=@id

GO
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:PatrickMiron
ID: 33545252
I verified the path that is return by the token and I can't get access to it on the SERVER where the SQL Server is running. I'm assuming that is supposed to happen because I did not enable "remote" access to that shared folder. Am I right?
0
 

Author Comment

by:PatrickMiron
ID: 33545568
Is it possible that the filestream storage is corrupted and if so, how do I create another and remove the old one. Not sure when I add filegroups where things go and if it removes the other filegroups...???
0
 

Author Comment

by:PatrickMiron
ID: 33545844
Just to clarify my comment on the path that is returned. It is return as a UNC path \\SERVER\NAMEOFFILEGROUP\...

But I can access the folders not under the shared path but under the path on the c:\Program Files\MSQL\....

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33547784
FILESTREAM Overview
http://technet.microsoft.com/en-us/library/bb933993.aspx

"Only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container. We recommend that no other account be granted permissions on the data container."
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33547795
I am still studying FILESTREAMS, so take any advice with a grain of salt.

One test possibility might be to move the FILESTREAM to a different location:

Moving FileStream data to new location
http://blogs.msdn.com/b/sriramg/archive/2010/06/03/moving-filestream-data-to-new-location.aspx?wa=wsignin1.0
0
 

Author Comment

by:PatrickMiron
ID: 33559979
I have tried changing the location and the backup/restore options.

I'm still trying to figure out wht comment about the NTFS permission to the filestream container.... Do you have more information on this and how I would go about configuring the account properly for that. I tried giving allrights to the SQL Server Agent runing on our server and also configuring a SQL Account only with all access to make this work. Again, the accounts where able to create the filestream localy on my Store Procedure, but I can't seem to have the rights to send a request to write to the filestream from my VB software. I'm starting to thinks its an Windows server 2008 problem.

Arrrrggg!!!
There has to be someone that knows about this issue???!!!

Patrick Miron
0
 

Accepted Solution

by:
PatrickMiron earned 0 total points
ID: 33570741
Well, you will never believe this. I was so sure that I did not need to enable the "Allow remote clients to have streaming acces to FILESTREAM data" that I misread this. That options seems to actually mean that if your software is not running on the same machine as the SQL Server is, then you have to enable this. I was under impression that it was an option to access the files through a local UNC path.

If you guys have more comments I still would appreciate them. I hope that my 5 days of searching for an answer actually helps other!

Cheers!
0
 

Expert Comment

by:lcog_kg
ID: 37766781
I enabled "Allow remote clients to have streaming access to FILESTREAM data" and it worked for me, thanks for your help.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 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