Cannot patch an instance of SQL Server 2005 if there is a mounted drive under the Data folder
In the following example, I tried to apply Service Pack 2 on top of an existing instance of SQL Server 2005 which had one mounted point under the instance's Data folder.
In such case, setup failed and SQL9_Hotfix_KB921896_sqlrun_sql.msp.log revealed the following.
Configuring ACL:
Object: E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data
ACL: (A;OICI;FA;;;[EMEA\ESTR_s_SQLSRV_SSTR170C])
Action: 0x103
Failed ACL:
SetSecurityFileDescriptor is failed at the error code 18; Converted SDDL: '(A;OICI;FA;;;S-1-5-21-1482476501-1450960922-725345543-989418)'
Error Code: 0x80077342 (29506)
Windows Error Text: Source File Name: sqlca\sqlsddlca.cpp
Compiler Timestamp: Sat Oct 7 09:43:41 2006
Function Name: ExceptionInSDDL
Source Line Number: 65
---- Context -----------------------------------------------
Setting status of unmanaged components and removing unmanaged resources
MSI (s) (54!74) [17:51:51:338]: Transforming table Error.
.
.
.
MSI (s) (54!74) [17:51:51:525]: Product: Microsoft SQL Server 2005 (64-bit) -- Error 29506. SQL Server Setup failed to modify security permissions on file E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\ for user emea_mytestuser. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
Error 29506. SQL Server Setup failed to modify security permissions on file E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\ for user emea_mytestuser. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
I started by live debugging Service Pack's setup beginning with the Custom Action that was failing: Do_sqlFileSDDL. And this is what I found there:
0:005> kL
ChildEBP RetAddr
008dc154 485b3c61 _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileSingle+0xa5
008dc41c 485b3d3a _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileRecursive+0xe1
008dc6e4 485b3d3a _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileRecursive+0x1ba
008dc9ac 485b414e _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileRecursive+0x1ba
008dca34 485af6b4 _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileDescriptor+0x13e
008dcd0c 48641af6 _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!Do_sqlFileSDDL+0x314
008df4c4 4857a1af _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!LaunchInvokeFunction+0x186
008df5b0 4857a63d _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!LaunchFunction+0x21f
008df5f4 48640ac8 _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!Redirector+0x5d
008df608 4913a23d _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!RdrDo_sqlFileSDDL+0x18
008dfa54 4913a6d1 MSI33B!launchFunction+0x24d
008dfcc0 4913913a MSI33B!Redirector+0xa1
008dfcd8 745fe754 MSI33B!RdrDo_sqlFileSDDL+0x1a
008dfcf4 745fe6ac msi!CallCustomDllEntrypoint+0x25
008dffb8 77e66063 msi!CMsiCustomAction::CustomActionThread+0x223
008dffec 00000000 kernel32!BaseThreadStart+0x34
Here, we can see that the call to CreateFile returned an invalid handle (ffffffff), when we tried to open directory "E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Data-D01\System Volume Information". The reason was (see GetLastError below) an access denied. So, the user running setup (ntdev\administrator) didn’t have access to open such Hidden+System directory.
This is the point just after returning from CreateFile:
0:005> r
eax=ffffffff ebx=00000000 ecx=7c8315e1 edx=00000015 esi=745ea818 edi=00000000
eip=485b39b5 esp=008dc100 ebp=008dc154 iopl=0 nv up ei ng nz na pe nc
cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000286
_36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileSingle+0xa5:
485b39b5 8945b4 mov dword ptr [ebp-4Ch],eax ss:0023:008dc108=ffffffff
0:005> u 485b39b5-6
_36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileSingle+0x9f:
485b39af ff1514134d48 call dword ptr [_36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!_imp__CreateFileW (484d1314)]
485b39b5 8945b4 mov dword ptr [ebp-4Ch],eax
485b39b8 837db4ff cmp dword ptr [ebp-4Ch],0FFFFFFFFh
485b39bc 7431 je _36DE7054_38A0_4541_9F9E_01AB1BA3B331_sqlca!SetSecurityFileSingle+0xdf (485b39ef)
485b39be 8b4d10 mov ecx,dword ptr [ebp+10h]
485b39c1 51 push ecx
485b39c2 0fb655bb movzx edx,byte ptr [ebp-45h]
485b39c6 52 push edx
0:005> dv
path = 0x008dc3d4
.
.
.
0:005> dt path -r1
Local var @ 0x8dc15c Type std::basic_string<unsigned short,std::char_traits<unsigned short>,std::allocator<unsigned short> >*
0x008dc3d4
+0x000 _Alval : std::allocator<unsigned short>
=484d0000 npos : 0x905a4d
+0x004 _Bx : std::basic_string<unsigned short,std::char_traits<unsigned short>,std::allocator<unsigned short> >::_Bxty
+0x000 _Buf : [8] "䡐ªᅀ籂闈???"
+0x000 _Ptr : 0x00aa4850 "E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Data-D01\System Volume Information"
+0x014 _Mysize : 0x4d
+0x018 _Myres : 0x4f
0:005> !gle
LastErrorValue: (Win32) 0x5 (5) - Access is denied.
LastStatusValue: (NTSTATUS) 0xc0000022 - {Access Denied} A process has requested access to an object, but has not been granted those access rights.
Then I went to see the security settings of the folder mentioned above, and noticed it wasn't configured to inherit from its parent, but it had been set explicit full control permissions to ONLY the SYSTEM account, overriding any permissions originally inherited from its parent.
Apparently, that's the way Windows ACL'es those junctions by default.
A bug is filed for this issue so that it is taken into consideration for SQL Server 2005 Service Pack 3 and SQL Server 2008, since it shouldn't be necessary to ACL the Data folder and its objects every time a setup or hotfix is installed.
In the meantime, the safest workaround I have come up with consists on creating the junction point to the mounted point on a directory which is completely off from the standard directory tree created by SQL Server setup. That way, we would guarantee that setup won't try to change the ACLs for the objects residing in the mounted point. In the example above, instead of mounting the disk holding the data in E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\data-d01, I could mount it in E:\myData\data-d01.
See you in a future post.