Search code examples
c#sql-serverfilestream

Error when reading data from SQL Server database

I have a filestream enabled database

I am able to write to the database but when I try to read the data back i get this error

An unhandled exception of type 'System.ComponentModel.Win32Exception' occurred in System.Data.dll Additional information: The user name or password is incorrect

The code I am trying to execute is this

public void ReadFromDatabase()
{
    using (SqlConnection connection = new SqlConnection(sql_ConnectionString))
    {
        connection.Open();
        SqlCommand cmd = new SqlCommand("SELECT TOP(1) Video.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Library", connection);

        SqlTransaction sqlTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Transaction = sqlTrans;

        using (SqlDataReader reader = cmd.ExecuteReader()) 
        {
            while (reader.Read())
            {
                string path = reader.GetString(0);
                byte[] transContext = reader.GetSqlBytes(1).Buffer;

                using (Stream fileStream = new SqlFileStream(path, transContext, FileAccess.Read, FileOptions.SequentialScan, allocationSize: 0))
                {
                    byte[] data = new byte[fileStream.Length];
                    fileStream.Read(data, 0, (int)fileStream.Length);

                    File.WriteAllBytes(@"C:\Users\Georgi\AppData\Local\VideoPresenter\temp.mp4", data);
                }
            }
        }
    }
}

I am using integrated security so the user and password are unchanged and hard coded into the application.

Here is the connection string code

//Create a string builder object
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
//Set the properties of the builder
builder.AsynchronousProcessing = false;
builder.DataSource = "xxx.xxx.xxx.xxx";
builder.IntegratedSecurity = true;
builder.InitialCatalog = "VideoLibrary";
//Set the connection string and connection objects' data
sql_ConnectionString = builder.ToString();
//try to connect to the server

And then I just use the string to open connections

The connection string passed is

"Data Source=xxx.xxx.xxx.xxx;Initial Catalog=VideoLibrary;Integrated Security=True"

Here is the stack trace

System.Data.dll!System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(string path, byte[] transactionContext, System.IO.FileAccess access, System.IO.FileOptions options, long allocationSize)   Unknown
System.Data.dll!System.Data.SqlTypes.SqlFileStream.SqlFileStream(string path, byte[] transactionContext, System.IO.FileAccess access, System.IO.FileOptions options, long allocationSize)   Unknown

SQL FILESTREAM TEST CLIENT.exe!SQL_FILESTREAM_TEST_CLIENT.SQLOperations.ReadFromDatabase() Line 158 C# SQL FILESTREAM TEST CLIENT.exe!SQL_FILESTREAM_TEST_CLIENT.MainWindow.GetButton_Click(object sender, System.Windows.RoutedEventArgs e) Line 47 C# PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args) Unknown PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs e) Unknown PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnClick() Unknown PresentationFramework.dll!System.Windows.Controls.Button.OnClick() Unknown PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(System.Windows.Input.MouseButtonEventArgs e) Unknown PresentationCore.dll!System.Windows.UIElement.OnMouseLeftButtonUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e) Unknown PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target) Unknown PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown PresentationCore.dll!System.Windows.UIElement.ReRaiseEventAs(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args, System.Windows.RoutedEvent newEvent) Unknown PresentationCore.dll!System.Windows.UIElement.OnMouseUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e) Unknown PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target) Unknown PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args) Unknown PresentationCore.dll!System.Windows.UIElement.RaiseTrustedEvent(System.Windows.RoutedEventArgs args) Unknown PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs args, bool trusted) Unknown PresentationCore.dll!System.Windows.Input.InputManager.ProcessStagingArea() Unknown PresentationCore.dll!System.Windows.Input.InputManager.ProcessInput(System.Windows.Input.InputEventArgs input) Unknown PresentationCore.dll!System.Windows.Input.InputProviderSite.ReportInput(System.Windows.Input.InputReport inputReport) Unknown PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.ReportInput(System.IntPtr hwnd, System.Windows.Input.InputMode mode, int timestamp, System.Windows.Input.RawMouseActions actions, int x, int y, int wheel) Unknown PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.FilterMessage(System.IntPtr hwnd, MS.Internal.Interop.WindowMessage msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown PresentationCore.dll!System.Windows.Interop.HwndSource.InputFilterMessage(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown WindowsBase.dll!MS.Win32.HwndWrapper.WndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown WindowsBase.dll!MS.Win32.HwndSubclass.DispatcherCallbackOperation(object o) Unknown WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.InternalRealCall(System.Delegate callback, object args, int numArgs) Unknown WindowsBase.dll!MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(object source, System.Delegate method, object args, int numArgs, System.Delegate catchHandler) Unknown WindowsBase.dll!System.Windows.Threading.Dispatcher.LegacyInvokeImpl(System.Windows.Threading.DispatcherPriority priority, System.TimeSpan timeout, System.Delegate method, object args, int numArgs) Unknown WindowsBase.dll!MS.Win32.HwndSubclass.SubclassWndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam) Unknown [Native to Managed Transition]
[Managed to Native Transition]
WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame) Unknown WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrame(System.Windows.Threading.DispatcherFrame frame) Unknown WindowsBase.dll!System.Windows.Threading.Dispatcher.Run() Unknown PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore) Unknown PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window) Unknown PresentationFramework.dll!System.Windows.Application.Run(System.Windows.Window window) Unknown PresentationFramework.dll!System.Windows.Application.Run() Unknown SQL FILESTREAM TEST CLIENT.exe!SQL_FILESTREAM_TEST_CLIENT.App.Main() C# [Native to Managed Transition]
mscorlib.dll!System.AppDomain.ExecuteAssembly(string assemblyFile, System.Security.Policy.Evidence assemblySecurity, string[] args) Unknown Microsoft.VisualStudio.HostingProcess.Utilities.dll!Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() Unknown mscorlib.dll!System.Threading.ThreadHelper.ThreadStart_Context(object state) Unknown mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx) Unknown mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx) Unknown mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Unknown mscorlib.dll!System.Threading.ThreadHelper.ThreadStart() Unknown

When I open Management Studio I see the row that is filled with the data but I am not able to read it from the application

NOTE: There is the default SQL Server user 'sa' only. And I did try using it too but the result is the same. I create the database with the the Windows User and not with the 'sa' one

NOTE: If I am to run the code with a DataSource=(local) in the connection string it works.

NOTE: Database is in READ_WRITE mode

What I have tried:

I tried recreating the table and the whole database

I tried reinstalling SQL Server Express

I tried add features to Windows, particularly Internet Information Services -> World Wide Web Services -> Security -> Windows Authentication/Basic Authentication

I tried manipulating the rights of the user I am using but it is a dbo so there should be no problem.

I tried running the same code on a different machine (With the same Microsoft account)*

I tried setting tried to set FILESTREAM( NON_TRANSACTED_ACCESS = FULL )

** I know that FileStream is working only with Windows Authentication and I am using windows 8 so everything is the same (or at least should be) in terms of accounts** Am I missing something?

Solution

  • If I am to run the code with a DataSource=(local) in the connection string it works.

    Because this works and using the IP Address does not, I suspect SQL Server is treating your connection as a remote connection instead of local.

    Open SQL Server Configuration Manager. Select SQL Server Services. Locate the SQL Server instance you are using, right click and select Properties. On the FILESTREAM tab, select Allow remote clients access to FILESTREAM data.

    If I am correct, that will solve your issue.