[ABAP]_엑셀 다운로드시 매크로 관련 오류

 [ 출처 : https://answers.sap.com/ ]

As per SAP Note 3247649 , the certificate needs to be installed manually under Trusted Publishers by each user or can be done through group policies. In order to get the certificate you need to at least have any one specific user/system who can export the certificate from the templates present in attachment of SAP Note 1992004 and also make sure the steps are performed in system.

[ 출처 : https://help.sap.com/ ]

1 - SAP SE certificate

• The SAP SE certificate is delivered with the front end installation.
• You can find the SAP SE certificate here: 
  • MS Excel > Options > Trust Center > Trust Center Settings > Trusted Publishers
  • MS Internet Explorer > Internet Options > Content > Certificates > TAB Trusted Publishers
• On the "General" tab , you can check the validity.
• On the "Certification Path" tab ,the path should be displayed as shown in following screenshot and the message "This certificate is OK" should be shown:
  
  

2 - How to remove an expired SAP SE certificate?

• Check folder C:\Program Files (x86)\SAP\Business Explorer\BI you can find the file BExAddin.dll. In the context menu for this file open the properties and switch to tab "Digital Signatures". You will find the SAP SE certificate. Press the button "Details" and then button "View Certificate" to see the validity of this certificate. As long any file has the "SAP SE certificate", irrelevant of the validity date, you can be assured that the file is a part of SAP certified software. The validity only shows the time period that SAP can use this certificate to certify a file. 

• Now you can find the expired SAP public key in Internet Explorer, Microsoft Edge or Excel Options under Trusted Publisher. The key is updated according to the setting/rules/option maintained in operating system.
• If the validity of this key is expired, you can remove the key following these steps: 

1. Start Microsoft Edge
2. Open More Tools > Internet Options > on TAB Content > press button Certificates.  
3. Under Trusted Publisher select SAP SE certificate and press button Remove:

3 - How to import the SAP SE certificate manually?

If you cannot find the certificate or you want to replace an expired certificate perform these steps:

1. In Microsoft Edge > More Tools > Internet Options > Advanced tab, activate the flag "Check for publisher's certificate revocation":
   
   
2. In Excel > Trust Center make sure that the following properties are active:
1. Add-ins:
1. [x] Require Application Add-ins to be signed by Trusted Publisher:
   
   
   
2. Macro Settings according note 1962327: 
   1. (o) Disable all macros except digitally signed macros
   2. [x] Trust access to the VBA project object model:
      
      
      
3. Close Internet Explorer and Excel.
4. RESTART THE WORKSTATION
5. Confirm the settings are still there on Excel and proceed
6. Open folder C:\Program Files (x86)\Common Files\SAP Shared\BW.
7. Start BExAnalyzer.xla file.
8. MS Excel opens and a MS Excel Security PopUp asks for confirmation:
   
   
   
9. Press button 'Trust all from publisher'. This imports the SAP SE certificate under the Trusted Publishers path.

Note:

• Each certificate has a limited validity, it expires on a certain date. A new certificate with updated/extended validity is delivered with the BEx front end patch (see: Current BI ADDON for SAP GUI 760).
• SAP releases the next patch before the old certificate expires, so you just have to install the newest patch to have a valid certificate.
• If the root certificate 'VeriSign Class 3 Code Signing 2010 CA' has been expired, you will have to update it as described in note 711648.

 

4 - Certificates in workbooks

• A new created workbook which is based on the SAP default workbook is signed and saved with the current installed certificate. So in the workbook a certificate exists with a limited validity.
• When opening an old workbook containing an expired certificate you will receive a warning from Excel stating that the certificate with which the workbook was signed has expired. The warning messages state that the signature is invalid. It is not really a problem since it is just informing you about the status of the workbook. You can still work with the workbook.
• When you save a workbook containing an expired digital certificate Excel automatically removes the invalid certificate from the workbook before saving it.

Note:

• The SAP delivered SAP Default Workbook contains macros. For security reasons this workbook has to be digitally signed so that customers know that the workbook is from SAP and safe to use.
• Workbooks which have been created by the customer do not necessarily require a signature. The customer determines if the workbook should be saved with a digital signature or not. If it is saved with a digital signature then the signature is typically the signature of the customer whom the workbook belongs to.

Here is how you can find the workbook certificate/signature:

• Open the workbook > press Alt+F11 > in MS VB navigate: Tools > Digital Signature
  
  
  
  
  

---

SAP Note 711648 - Macro certificate signature not verified

 

[ 출처 : https://answers.microsoft.com/ ]

Macros from the internet will be blocked by default in Office: Block macros from running in Office files from the Internet

https://docs.microsoft.com/en-us/DeployOffice/security/internet-macros-blocked#block-macros-from-running-in-office-files-from-the-internet

매크로 보안 관련하여 기본 디자인이 최근에 바뀌었습니다. 여기서 이 레지스트리가 ‘구성되지 않음’ 상태일 경우 원래는 Office 앱의 보안 센터 설정을 따라가는 것이 기본 디자인이었으나, 이제는 ‘구성되지 않음’ 상태인 경우에도 Office 앱의 보안 설정과 상관없이 차단하는 것으로 디자인이 바뀐 것으로 보입니다.

Windows Registry Editor Version 5.00

 

[HKEY_CURRENT_USER\SOFTWARE\Policies\Microsoft\office\16.0\excel\security]

"blockcontentexecutionfrominternet"=dword:00000000

 

위 내용을 그대로 복사하여 메모장에 붙여넣고, .reg 파일로 저장한 뒤 실행하여 병합합니다.

(메모장에서 저장시 '파일 형식'을 모든 파일로 지정하고 파일 이름 뒤 .reg를 붙이면됩니다)

 

 

 

이후 재부팅한 뒤, 매크로 파일을 열어보는 작업을 진행해 보시기 바랍니다.

 

이 레지스트리는 인터넷에서 다운로드된 오피스 파일에 포함된 매크로를 차단하는 것을 명시적으로 사용하지 않게 설정합니다.

 

이렇게 하면 Office 프로그램이 Excel 보안 센터의 매크로 설정을 따르게끔 조치할 수 있을 것으로 보입니다.

 

 

 

 

 

또한 매크로 설정을 위와 같이 매크로를 사용하는 쪽으로 모두 변경해 주셔야 할 수 있습니다.

 

인터넷에서 다운로드 된 파일에 포함된 매크로는 기본적으로 차단되는 것이 Microsoft의 권장 사항이며, 아무 조건 없이 실행시키도록 구성하는 것은 컴퓨터 보안 측면에서 위험할 수도 있습니다.

Microsoft Community를 이용해주셔서 감사합니다.

안내 드린 답변이 도움이 되었기를 바라며, 문제가 해결되지 않았거나 추가 질문이 있으실 경우

[응답] 버튼을 눌러 답변 부탁드립니다.

감사합니다.