Error when reading data from SQL Server database

Question

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?

If you are using integrated security then you should not specify any username or password in your connection string. The idea of integrated security is that the identity executing the process will be used to authenticate against the SQL database. — Darin Dimitrov
I don't specify them, I did try using SQL server authentication but the error is the same so I removed the user and password parameters from the connection string and now I only specify integrated security, datasource and initial catalog — EnvelopedDevil
Could you please show the connection string you are using and the exact error you are getting? — Darin Dimitrov
As a side note, I strongly recommend you use Stream.CopyTo instead of allocating a buffer the size of the (potentially huge) file. — Remus Rusanu
It has to be integrated security to use the filestream, so the sa account will not work. Are you sure you are correctly impersonating your current user and that you are not using the ASP.net identity (or NetworkService) to connect to your database? — Paddy

Lefka Lefka · Accepted Answer · 2015-01-12T15:33:00

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.

Error when reading data from SQL Server database

5 Answers