November 15, 2018 at 9:25 am
I do not know (at this time) what version of SQL Server is actually involved as I am waiting for that information. We have a System Administrators out doing installs/upgrades for another unit and they have run into an issue I have not had to deal with as I personally haven't used CLR modules. I have attached the picture of the error message that they are getting and could use some advice or direction.
November 15, 2018 at 9:50 am
Hi Lynn,
Whenever a CLR module invokes resources that cannot be provided within the SQL Server itself, the assembly must be set up for External Access or Unsafe execution. A situation I work with daily that requires this is posting to a Web Service from a SQL Server Stored Procedure. CLRs that typically don't need such external support would be business specific algorithms or formulas that only require access to data stored within the SQL Server itself.
Here is SQL Server Documentation article introducing the key concepts for supporting External Access and Unsafe assembly options.
While I acknowledge that option 1 in the article is a more secure solution to including CLR code within the database environment, I have only ever been able to get the 2nd option functional. Marking the database as Trustworthy, giving the database owner permission to run External Access or Unsafe assemblies and then installing the assembly with External or Unsafe access.
My most recent experience uncovered a little twist in this process. I was deploying a database to a new server from a backup. While everything else about the database functioned, I had problems with the assemblies which kept reporting the same error as your attachment illustrates. I set Trustworthy On for the database and couldn't see any other reason why it should be blocking me. With a little tinkering, I discovered a second error message that told me that the "Owner" of the database was not valid. This reminded me that I had restored the database from an older server so while I had the same "name" for the owner, they had different security identifiers so they were not the same account. I had to repair the ownership with an ALTER AUTHORIZATION statement to clean up the ownership. Then my previously failing CLR started working again.
Hope this gives you some clues to tackle your problem.
November 15, 2018 at 10:05 am
It is giving me some places to read. I have heard back on some of what I asked while waiting. Trustworthy is already on. I am waiting to see if there are other error messages in the logs.
November 15, 2018 at 10:09 am
One question from the documentation for my edification:
USE master;
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;
GO
The executable file named in the CREATE ASYMMETRIC KEY statement is the DLL that contains the CLR assembly(ies), correct?
November 15, 2018 at 10:19 am
Lynn Pettis - Thursday, November 15, 2018 10:09 AMOne question from the documentation for my edification:
USE master;
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;
GO
The executable file named in the CREATE ASYMMETRIC KEY statement is the DLL that contains the CLR assembly(ies), correct?
Yes. You have to have a copy of the DLL that was loaded as an Assembly in SQL Server. The key will be used as a signature to ensure that only that assembly will be trusted for external access.
November 15, 2018 at 11:11 am
Okay, looks like the problem was found. The version of SQL Server 2014 being used is 32-bit and the DLL it was trying to use was 64-bit. They were able to pull the 32-bit DLL off the old server and everything appears to be working now.
November 15, 2018 at 11:16 am
gee. why did they install a 32 bit SQL version?
November 15, 2018 at 11:18 am
frederico_fonseca - Thursday, November 15, 2018 11:16 AMgee. why did they install a 32 bit SQL version?
That is what I just asked.
November 16, 2018 at 1:03 pm
Lynn Pettis - Thursday, November 15, 2018 11:18 AMfrederico_fonseca - Thursday, November 15, 2018 11:16 AMgee. why did they install a 32 bit SQL version?That is what I just asked.
obviously to save space, that's 32 less bits!
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 17, 2018 at 1:08 am
This was removed by the editor as SPAM
November 17, 2018 at 1:10 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply