I am just testing something internal and now the TRUSTWORTHY is set to ON security risks.

1. I am wondering though, if TRUSTWORTHY is ON, can an UNSAFE assembly stop working if it is signed and and has a .pfx key file in it?
2. Not sure if a certificate was created with it years ago or either the certificate or key can expire?

This question relates to: A SQL CLR Assembly set to UNSAFE with a key is no longer working

CodePudding user response：

1. If TRUSTWORTHY is ON, then module signing isn't required.

   Also, assemblies do not contain .pfx files. The pfx file is a password-protected file that contains the private key. An assembly, if signed with a certificate (or snk / asymmetric key), contains the public key, which is used for validation.

2. No, you needn't worry about the certificate's expiration date (especially not when merely validating something that has been signed, which is all that is happening here).

   If you want to know if an assembly has been signed by a strong-name key (snk) / asymmetric key, just look at the assembly properties (e.g. SELECT name, clr_name FROM sys.assemblies;). If "publickeytoken" (in clr_name) has a value (i.e. not "null"), then the assembly has been strongly-named (i.e. signed with an asymmetric key).

   If you want to know if an assembly has been signed by a certificate, simply try to create a certificate from that assembly:

   CREATE CERTIFICATE [_TempTest] FROM ASSEMBLY [Microsoft.SqlServer.Types];
   -- DROP CERTIFICATE [_TempTest];
   

   If the assembly was signed with a certificate, the CREATE statement will succeed. Then, drop the certificate.

   If the assembly was not signed with a certificate, you will get the following error:

   Msg 15208, Level 16, State 31, Line XXXXX
   The certificate, asymmetric key, or private key file is not valid or
     does not exist; or you do not have permissions for it.
   

   Please note that it is possible to sign an assembly with an asymmetric key and one or more certificates.