Debugging Assembly Load within SQL Server 2012

16 สิงหาคม 2555 2:38

ผู้ดูแล

You didn’t get any error at CREATE ASSEMBLY time, only at runtime?

There is a SQL Profiler and XEvent event for assembly loading, but I’m not sure that that will provide all that much more help.

You might get some better information using the Fuslogvw tool (http://msdn.microsoft.com/en-us/library/e74a18c4(v=vs.100).aspx), or Sysinternals tools like Process Monitor or File Monitor.

Hope this helps, Bob

16 สิงหาคม 2555 3:10

0

Dear Bob

Fuslogvw does not show anything. I already tried it. I did some debugging: So here is the simplest test code that does not do anything at all except forcing assemblies to load:

[Microsoft.SqlServer.Server.SqlProcedure(Name = "CLR_PivotNameRangeDB")]
        public static void CLR_PivotNameRangeDB(SqlBytes FileText, SqlXml PivotXML)
        {
            // dummy calls for debugging dependent assemblies
            Accessibility.AnnoScope _scope = Accessibility.AnnoScope.ANNO_THIS;
            System.Drawing.ContentAlignment _alignment = System.Drawing.ContentAlignment.BottomCenter;
            System.Xaml.Schema.AllowedMemberLocations _locations = System.Xaml.Schema.AllowedMemberLocations.Any;
            System.IO.Packaging.EncryptionOption _option = System.IO.Packaging.EncryptionOption.RightsManagement;

            if (_scope != Accessibility.AnnoScope.ANNO_THIS) return;
            if (_alignment != System.Drawing.ContentAlignment.BottomCenter) return;
            if (_locations != System.Xaml.Schema.AllowedMemberLocations.Any) return;
            if (_option != System.IO.Packaging.EncryptionOption.RightsManagement) return;
return;
}

Here is the SQL Server log during assembly installation:

Unsafe assembly 'accessibility, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil' loaded into appdomain 21 (GGIDM_T.dbo[ddl].20).

Unsafe assembly 'system.drawing, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil' loaded into appdomain 21 (GGIDM_T.dbo[ddl].20).

Unsafe assembly 'system.xaml, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil' loaded into appdomain 21 (GGIDM_T.dbo[ddl].20).

Unsafe assembly 'windowsbase, version=4.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35, processorarchitecture=msil' loaded into appdomain 21 (GGIDM_T.dbo[ddl].20).

And here is log from actual execution:

Unsafe assembly 'ggidm_clr, version=1.0.4611.24735, culture=neutral, publickeytoken=c21c5979c7d7a0b7, processorarchitecture=msil' loaded into appdomain 17 (GGIDM_T.dbo[runtime].16).

Unsafe assembly 'accessibility, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil' loaded into appdomain 17 (GGIDM_T.dbo[runtime].16).

Unsafe assembly 'system.drawing, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil' loaded into appdomain 17 (GGIDM_T.dbo[runtime].16).

Unsafe assembly 'system.xaml, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil' loaded into appdomain 17 (GGIDM_T.dbo[runtime].16).

After that there is only an error thrown in the SSMS

Msg 6522, Level 16, State 1, Procedure CLR_PivotNameRangeDB, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLR_PivotNameRangeDB": 
System.IO.FileNotFoundException: Could not load file or assembly 'WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException: 
   at GGIDM_CLR.StoredProcedures.CLR_PivotNameRangeDB(SqlBytes FileText, SqlXml PivotXML)

It appears to me that dependent assemblies load fine but there is a problem with loading WindowsBase which loads into GGIDM_T.dbo.[ddl] but fails to load into GGIDM_T.dbo.[runtime]

After upgrade I'm using .NET framework 4.0.30319. The server is Microsoft SQL Server 2012 - 11.0.2100.60 (X64), Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I will try to play with FileMonitor but I do not really understand how this would help as assemblies would be already loaded within SQL Server so there would not be any physical file access?

Regards,

Maciej

mjsk

16 สิงหาคม 2555 5:36

0

Dear Bob

As the issue was stopping my colleagues from work we just did full restore database to the state when this functionality was last working and problem disappeared. I have not had a chance to compare assembly images for WindowsBase as it is within sys.assembly_files with one in C:\Windows\Microsoft.NET but will try to do this and will let you know if these are any different.

Thank you for your help

Regards,

Maciej

mjsk

17 สิงหาคม 2555 7:38

0

Dear Bob

I have managed to reproduce problem on a different server so I can compare assemblies. The only difference I can find is in the size of system.drawing.dll. We do not use it but because it is dependency for WindowsBase it still gets loaded.

It appears that the size in sys.assembly_files on server where it does not work it is 616024 and on a server where it does work (restored) it is 607064. This dll was recently updated by security patch http://support.microsoft.com/kb/2656368. Unfortunately, I seem to have difficulties to get the original file.

I will confirm if I have any luck with it.

Regards,

Maciej

mjsk

20 สิงหาคม 2555 6:31

0

Unfortunately I tried with 3 different versions of WindowsBase 4.0.30319 and all results are the same.

I will mark this email as an answer as I will not be able to work on this for another month but if anyone would be will to let me know how to use CLR to read data from Excel in open document format I would be grateful. Admitedly we have another solution using DocumentFormat.OpenXml.dll but it is rather slow and heavyweight.

I understand that WindowsBase is not supported within SQL Server but it is misfortune that System.IO.Packaging is included into unsupported dll. I guess for now we will stick to SQL Server 2005 where this works.

Regards,

Maciej

mjsk

ทำเครื่องหมายเป็นคำตอบโดย Maciej Skierski 20 สิงหาคม 2555 6:32
ยกเลิกการทำเครื่องหมายเป็นคำตอบโดย Maciej Skierski 21 สิงหาคม 2555 6:02

21 สิงหาคม 2555 6:03

0

For completness here is the output from Fusion Log

Msg 6522, Level 16, State 1, Procedure CLR_PivotNameRange, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLR_PivotNameRange": 
System.Exception: WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Fusion Log:
Assembly manager loaded from:  C:\Windows\Microsoft.NET\Framework\v4.0.30319\clr.dll
Running under executable  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
--- A detailed error log follows. 

=== Pre-bind state information ===
LOG: User = ******
LOG: DisplayName = WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
 (Fully-specified)
LOG: Appbase = file:///C:/Program Files/Microsoft SQL Server/MSSQL11.MSSQLSERVER/MSSQL/Binn/
LOG: Initial PrivatePath = NULL
Calling assembly : (Unknown).
===
LOG: This bind starts in default load context.
LOG: No application configuration file found.
LOG: Using host configuration file: 
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework\v4.0.30319\config\machine.config.
LOG: Post-policy reference: WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
LOG: The same bind was seen before, and was failed with hr = 0x80070002.

Could not load file or assembly 'WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

Regards

Maciej

mjsk

11 ตุลาคม 2555 4:10

0

I found a few more people complaning about what appears to be the same (or closely related issue):

Blog by Mika Wendelius and Bug Report by Mirela Aciu

I have also opened an issue with Microsoft Support. Currently we are discussing meaning of MSDN documentation Supporter .NET Framework Libraries. My opinion is that although "unsuported" it should still load with with decision about security and reliability in the hands of DBA. See the following quote from documentation

Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability. For example, the System.DirectoryServices namespace is not supported. You must register the System.DirectoryServices.dll assembly with UNSAFE permissions before you can call it from your code. The UNSAFE permission is necessary because classes in the System.DirectoryServices namespace do not meet the requirements for SAFE or EXTERNAL_ACCESS. For more information, see CLR Integration Programming Model Restrictions and CLR Integration Code Access Security.

Regards,

Maciej

mjsk

11 ตุลาคม 2555 22:29

0

I have finally received the asnweer and it appears that loading of WindowsBase.DLL is disabled by design. Here is the answer:

--------------- Begining of answer

My name is Eric. I am the Technical Lead of Peng, Support Engineer, who has been working with you on the captioned issue.

Peng referred this case to me for review, as this is part of our process to ensure our support engineers are delivering highest quality of service to our customers. After reviewing your case, I understand this case is about the error “System.IO.FileNotFoundException: Could not load file or assembly 'WindowsBase’” reported when SQL Server tries to load a CLR Stored Procedure that references namespace “System.IO.Packaging”. This worked fine originally in SQL Server 2005 but it failed in SQL Server 2012.

In the previous 3 weeks, Peng performed a lot of tests to troubleshoot the issue and also involved escalation resources to debug the assembly loading mechanism of SQL Server. With the investigation we confirmed the issue is occurred because the library “System.IO.Packaging” is not a supported library for SQL Server 2012 CLR objects, which is documented in the following documentation:

Supported .NET Framework Libraries

http://msdn.microsoft.com/en-us/library/ms403279(v=sql.110).aspx

I understand there might be some confusion about how the document explains the supportability of our specific case. Technically, the problem is not because the DLL “WindowsBase.dll” itself is not secure or reliable, the actual problem is the reliability of SQL Server service when we have DLLs like “WindowsBase.dll” running in the SQL Server process. In .NET Framework 4.0, the “WindowsBase.dll” includes not only “System.IO.Packaging” but also a lot of other libraries that are UI related. As SQL Server is supposed to run as a service on a server platform, which is not desktop interactive, loading libraries like “System.Drawings” ,which is desktop interactive, could cause potential problems (eg. leaving security holes or leaking orphaned Windows handles). As the DLL runs within the SQL Server process, all these problems will directly affect the health of the whole database system. To a database server system, reliability and security are on the top of our concerns. That is the reason why we blocked the loading of “WindowsBase.dll” into SQL Server 2012 to protect SQL Server itself. As for SQL Server 2005 CLR objects, it was designed in old days and is based on CLR runtime 2.0. The reason why it worked before on SQL Server 2005 is probably because 1) it references .NET Framework 3.0 version of “System.IO.Packaging”, which has different architecture; 2) we did not protect the SQL Server process so thoroughly in old days.

With above in regard, I would like to further explain the following statement, which is included in the document Peng shared earlier. We do allow users to load some unsupported libraries into SQL Server with UNSAFE permission. For those libraries, they might still function well but we have not fully tested them to guarantee 100% reliability and security. However for our case, because the library “System.IO.Packaging” is in DLL “WindowsBase.dll”, with the reasons explained above, it is not regarded as a general unsupported library which we still allow you to load up. Instead, it is completely blocked to ensure SQL Server can work stably.

Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability.

I do understand the inconvenience caused by this behaviour change of SQL Server 2012, and as a support service we do would like to assist you to find a best solution with all factors considered. At the current stage, we would recommend moving this part of logic out of SQL Server and leverage a client application (or an application server) to process the same request. We have tested and can confirm your sample code can work perfectly in a separated process. This ensures SQL Server can still work stably while preserving the previous business logic.

Peng has explored and exhausted all possible avenues while working on this case. His activities were in line with our highest Professional Support standards. With our current analysis result and suggestions provided, he is recommending closing of this case and I concur with his decision. I have informed Peng to close this case this week.

I would like to take this opportunity to refer you to our Frequently Asked Questions (FAQ) on the boundary of Professional Support. You can find them at http://support.microsoft.com/gp/proffaq/en-gb. After your review, please feel free to contact me if you have any further questions or concerns.

Thank you very much for giving the Microsoft the opportunity to serve you. We look forward in serving you in the future.

--------------- End of answer

I suspect that the solution now is to look for alternative technologies. One possibility would be to use deprecated extended system stored procedures. They still work in 2012 and with lifespan of the product of 8 years they will last for a while. During that time Microsoft may find another way how to provide SQL Server extendibility without blocking their own products.

Regards,

Maciej

mjsk

ทำเครื่องหมายเป็นคำตอบโดย Maciej Skierski 11 ตุลาคม 2555 22:29

12 ตุลาคม 2555 4:14

ผู้ดูแล

0

I agree with you, and disagree in general (the case you mention doesn't prove this for the reasons stated in the email you received) with the statement that "some unsafe assemblies are more unsafe than others".

And I'd also state that extended stored procedures have more capacity to compromise the stability of the server process that unsafe assemblies. And they are not blocked. On the other hand, it has always been the case that folks seem to want to use unsafe assemblies with impunity; they've been abused (by number of users) more than extended stored procedures were.

This DOES represent a change between SQL Server 2005-2008 R2 and SQL Server 2012. This change should be documented, along with a list of the libraries that are "blocked by definition". Along with the behavioral reason for this classification. I do know of a few programming constructs (like dynamic web service proxies) and some libraries (like the SMO libraries) that have been blocked in SQLCLR in past.

Cheers, Bob

12 ตุลาคม 2555 4:31

0

Dear Bob

I think there are a few issues here that annoyed me as an architect and developer:

Something that used to work within SQL Server 2005 stops working and it is not advertised under breaking changes or deprecated features which I did check.
The messages we are getting do not say something along the lines "SQL Server blocked access to this dll because its failed verification" it just fails with an internal error from .NET suggesting a bug.
MSDN documentation says that you should be able to load dlls as long as you register these. It does not mention anything about blcoking anything.

I really need to move on. I spent far too much time on resolving this issue without achieving anything productive. I suspect that a bunch of other threads in this discussion group can be also closed for the above reason.

Thank you for your help

Regards,

Maciej

mjsk

Debugging Assembly Load within SQL Server 2012

ตอบทั้งหมด