Excepts from a conversation between Sumit and Leena, that I overheard last week. Don’t know who they are?? Never mind, I will introduce them later!
Leena: “Sumit, I am playing with the FILESTREAM access level and I am bit confused as I am getting a ‘3’ while querying!”
Sumit: “I warned you not to play while at work!”
Leena: “Come on Sumit, I do have a real problem. I am getting a different value than what I have set when I query for it afterwards.”
Sumit: “Must be a WYSIWIG”
Leena: “What?”
Sumit: “What You SET Is What You Get. You get something because you have SET it. You can make mistakes (as always), but not SQL Server.”
Leena: “No, I am not GETting what I have SET. In other words, I have not SET what I am GETing. I am setting the FILESTREAM access level to ‘2’ (Transact SQL and Win32 Access) but why am I getting ‘3’ when I query for the current configuration”
Sumit: “bol”
Leena: “bol? What else should I say? (BOL [hindi] means “Say/Speak”). I want you to take a look at this and help me to figure out what is wrong”
Sumit: “BOL (Books Online) Please!”
Leena: “I looked at Books online. Books online has mentioned only ‘0’, ‘1’ and ‘2’. ‘0’ means FILESTREAM disabled, ‘1’ means TRANSACT SQL Access enabled and ‘2’ means both TSQL and Win 32 Access enabled. Nowhere I see ‘3’ mentioned. Please take a look at http://msdn.microsoft.com/en-us/library/cc645956.aspx”
Sumit: (Wakes up). “I will take a look at it and let you know tomorrow”. (dozes again)
Next Day
Leena: “Sumit, did you get a chance to look at the problem we discussed yesterday?”
Sumit: “Yes”
Leena: “So what is the reason for that strange behavior?”
Sumit: “OCSF”
Leena: (quickly googles for OCSF but makes sure that Sumit does not see it) “Object Client Server Framework?”
Sumit: “Order Coffee and Sandwiches First!”
Leena: (Orders coffee and sandwiches)
Sumit: “Well, there is something undocumented here. Though the documentation shows only 0, 1 and 2, there really exists another value that is ‘3’.”
“FILESTREAM Access Level ‘0’ and ‘1’ are interpreted exactly as documented (0 for Disabled and 1 for TSQL Access). The documentation says that ‘2’ stands for ‘TSQL and Win32 Access’ which is also known as ‘Full Access’”.
“The FULL Access may be of two flavors. TSQL + Win32 Access (Local) and TSQL + Win32 Access (Remote). It means that the configuration value ‘2’ means TSQL and Local Win32 API Access. Configuration value ‘3’ stands for TSQL and Remote Win32 API Access”
Leena: “Oh, that is surprising. However, in my case, I am setting FILESTREAM access level to ‘2’ (TSQL + Local WIN32 Access as per your explanation). So how come SQL Server configures it with ‘3’ and give me back 3 when I query the current settings?”
Sumit: “Well, SQL Server takes the configuration values a little bit differently. FILESTREAM configuration on a SQL Server 2008 instance needs to be done at the Windows Administrator level as well as SQL Server Instance level. A windows administrator can enable or disable IO streaming access to FILESTREAM data from SQL Server configuration Manager”
“If IO streaming is enabled for remote access, then SQL Server will translate your ‘2’ to ‘3’. If it is disabled, your ‘2’ remains to be ‘2’”.
Leena: “Oh, that is funny. But what is the configuration dialog you are referring to?”
Sumit: “Go to SQL Server Configuration Manager, right click on the SQL Server instance and go to the properties. You will see a new TAB there named “FILESTREAM”. This is where you enable or disable remote streaming access to FILESTREAM data.”
“So let me explain this. Currently you have got remote access to your FILESTREAM data enabled at the SQL Server instance level. Now let us try the following.”
-- Set FILESTREAM Access Level to 1 (TSQL Only)
EXEC sp_configure filestream_access_level, 1
RECONFIGURE
-- Query the FILESTREAM Access Level
SELECT SERVERPROPERTY ('FilestreamEffectiveLevel')
/*
FilestreamEffectiveLevel
-------------------------
1
*/
Leena: “It works as expected!”
Sumit: “Now look at this:”
-- Set FILESTREAM Access Level to 2 (TSQL + Win32)
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
-- Query the FILESTREAM Access Level
SELECT SERVERPROPERTY ('FilestreamEffectiveLevel')
/*
FilestreamEffectiveLevel
-------------------------
3
*/
Leena: “Here is the problem that makes me crazy since yesterday!”
Sumit: “You are right. It might make even normal people like me crazy!”
Leena: “Did you intend to say that I am not normal?”
Sumit: “Not really. I always try to avoid redundant data. Let us get back to the problem. When you set the FILESTREAM Access Level to ‘2’, SQL Server internally interpreted it as ‘3’ because remote streaming access to FILESTREAM data is currently enabled.”
“Now let us go to the configuration page and disable remote FILESTREAM access.”
“Note that we have disabled I/O streaming access from remote clients. Now let us run the same code again.”
-- Set FILESTREAM Access Level to 2 (TSQL + Win32)
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
-- Query the FILESTREAM Access Level
SELECT SERVERPROPERTY ('FilestreamEffectiveLevel')
/*
FilestreamEffectiveLevel
-------------------------
2
*/
Leena: “Wow! Now it is a WYSIWYG now. I am getting the same value that I have configured. Sumit, you are a genius!”
Sumit: Grins, nods in acknowledgement and silently thanks all those folks who helped him throughout the previous night to figure this problem out.
Sumit and Leena were born in third week of January 2010. They made their first public appearance on 30th January 2010 at Bhaikaka Hall, Ahmedabad during the Community Techdays Event. Sumit is a Database Administrator and Leena is an application developer. I hope they will post more information on their linked-in and facebook profiles soon.