I am trying to write data to a FILESTREAM column of an MS SQL database. When running it on the local machine, it works fine, but as soon as connecting to a remote one, it locks up.
Here is what I do (Using NHibernate):
private static readonly string QUERY_GET_PATH =
"select CompressedData.PathName() as path, GET_FILESTREAM_TRANSACTION_CONTEXT() as con from Data.TimeSeries where FS_ID = :dataId";
private static readonly string QUERY_SET_BLANK =
"update Data.TimeSeries set CompressedData = Cast('' as varbinary(max)) where FS_ID = :dataId";
// ...
ISession session = ...
var q1 = session.CreateSQLQuery(QUERY_SET_BLANK);
q1.SetGuid("dataId", ts.DataId);
q1.ExecuteUpdate();
var q2 = session.CreateSQLQuery(QUERY_GET_PATH);
q2.SetGuid("dataId", ts.DataId);
var results = q2.List();
var item = (object[])results[0];
var path = (string)item[0];
var context = (byte[])item[1];
return new SqlFileStream(path, context, FileAccess.Write);
Running this locally works fine, but then I connect to a remote database (this is not in my hands, I just have to write to it) using a connection string like this:
Server=10.0.0.5; Database=TheDatabase; User Id=foo; Password='barfoo';
The returned path looks like:
\\SOME-HOST\SOMEWHERE\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\Foo\Data\TimeSeries\%!CompressedData\BAD566ED-CD86-42DE-AC71-D13125E89990\VolumeHint-HarddiskVolume1
First I thought it was because of name resolution from 10.0.0.5 to SOME-HOST but it keeps locking up when adding an appropriate entry to my hosts file (ping to the database works).
What might be wrong?
After waiting for some minutes, I get a Win32Exception with the message The user name or password is incorrect.
I am posting this as an answer so I can mark it as resolved although it is more like a workaround - but I'd like to at least leave an explanation here for others who might face the same or a similar issue.
As I found out, FILESTREAM only works with SSPI, and SSPI in turn only works when the client and server are either in the same domain or when accessing the database locally. This explains why it worked in first place when I accessed the database locally.
Since the database server is in a system set up by a third party working with us, I can't get my client machine into their domain, and I can't get their server into ours. Our workaround is to prepare everything, zip it up, transfer that archive to the database server and have a service running there, locally connected to the database, put everything in. From a security point of view that's okay because both systems only run in a local network with no access to the outside whatsoever.