SQL Express 2012 client > SQL Express 2012 server with FILESTREAM

Question

I have developed a client/server database application using SQL Server Express 2012 to store the database. I have configured the database with FILESTREAM and am storing client files (JPG, PDF) in a FileTable. The application was developed using Visual Studio 2012 and uses a table adapter to get data from the database.

I have noticed that when running my application on a client where SQL Express 2012 is installed (default SQL installation) and connecting to a remote database on the LAN I receive the following error: Microsoft .NET Framework - "DataReader.GetField Type(2) returned null", and no data is pulled from the database. If the same client has SQL Express 2008 instead of 2012 installed, the application runs as expected and without error. I have received the error on all of the clients (Windows 7 Pro 32- and 64-bit) that have SQL Express 2012 installed.

Using SQL Profiler I can see that a connection to the database is made and SELECT statement issued.

If both the database and client are run from the same computer there is no error and the application runs as expected.

It seems that my remote clients need to use SQL Express 2008 to connect to a SQL Express 2012 database server. Does anyone know why SQL Express 2012 on a client fails to connect to a SQL Express 2012 database server?

Thanks.

Answer 1

Hello,

I am far to be a specialist of FILESTREAM but i found this link

http://msdn.microsoft.com/en-us/library/cc645940.aspx

The part Obtaining a Win32 File Handle seems to be interesting as ( if i have understood correctly what there is the code part VB or VC# ), if a 'null" value is returned , it means that the application has not the rights ( permissions )  to access to the file in which is stored.

In http://msdn.microsoft.com/en-us/library/gg471497.aspx , in the part Integrated Security, i found

"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"

and also "SQL logins will not work with FILESTREAM containers. Only NTFS authentication will work with FILESTREAM containers"

Please, could you tell us what kind of authentification are you using to connect to your database ?

If you are using Windows authentification , i think that there is no problem.But if you are using SQL Server authentification ( application on a computer , database on another computer + both computers not belonging to the same Windows domain, managed by a Windows Server domain controler ), i think you will have access security problems.

I will try to find a FILESTREAM specialist

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Papy-

I am using Windows Authentication. I apologize for leaving out that little bit of critical information.

Thanks for the links. I have looked at them, and if I understand them correctly they assume that I have already retrieved data from the database. I'm not exactly sure what goes on behind the scenes when using table adapters to retrieve data, but it appears that the error is happening just after my SELECT statement is issued but before the data is delivered.

I'm not sold on the idea that the problem is with FILESTREAM, since FILESTREAM can be implemented in SQL 2008 as well as SQL 2012. I do know that FileTables are currently only available in SQL 2012, which makes my situation all the more puzzling to me. In creating my FileTable, I followed the procedure here.

Having taken the defaults when installing both SQL Express 2008 and SQL Express 2012 on the clients I'm assuming the same SQL Server service account is being used. I'm currently confirming this.

Thank you for responding. Looking forward to any other input.

Mats

Answer 3

Hello,

Have you checked the last checkbox (Allow remote access for clients ) ? It is not checked on default for SQL Server Express 2012 ( and 2008 R2 )

You may have problems of security permissions especially with Windows 7 Pro  ( unknown problem on an old XP Pro  ). But i have not tested this feature as if my own W7 is an Ultimate edition, for the other W7 is a Home Premium ( so i need SQL Server authentification )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Yes, 'Allow remote clients access to FILESTREAM data' is checked. (I don't think I'd be connecting with SQL Express 2008 if it wasn't ;-))

Answer 5

I was able to finally use SQL Express 2012 on my Win7Pro client without error, after installing SSMS on the client then uninstalling 'Shared features only'. Which I believe is to say, install SSMS then uninstall it.

If it weren't for the 900+MB download for SSMS I'd call it a day, a buggy one at that, and go with the workaround.

"Looks like I picked the wrong week to quit amphetamines."