Endpoint for Mirroring with Windows & Certificate Authentication in SQL Server

Sometimes we fall in problem when need to mirror multiple databases by using different Windows-Authentication and Certificate.

in this case, we have to create Endpoint using both authentications along with the required certificate ...

to achieve dual authentication in the same Endpoint, we have to use WINDOWS NEGOTIATE and CERTIFICATE CER_NAME as AUTHENTICATION  and also we have to mansion the ENCRYPTION algorithm for both.

in this example, I have used RC4 and AES for WINDOWS and CERTIFICATE authentication.

CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022  
      , LISTENER_IP=ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION =  WINDOWS NEGOTIATE CERTIFICATE SQLHOST_CER 
      , ENCRYPTION = REQUIRED ALGORITHM RC4 AES
      , ROLE = ALL
   );

:::: SQLHOST_CER is the SQL Server Certificate authentication

CREATE CERTIFICATE SQLHOST_CER
   WITH SUBJECT = 'SQLHOST certificate for database mirroring',
   EXPIRY_DATE = '01/01/2020';

::::: set ENCRYPTION as per your need; you can set any encryption for any authentication.

-- To check available Endpoints use below SQL
SELECT * FROM sys.database_mirroring_endpoints;

-- To Drop an Endpoint
DROP ENDPOINT Endpoint_Name

////***
If you need to create Endpoint with only one authentication then use any one as AUTHENTICATION option


For WINDOWS Authentication
-----------------------------
CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022  
      , LISTENER_IP=ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION =  WINDOWS NEGOTIATE
      , ENCRYPTION = REQUIRED ALGORITHM RC4 
      , ROLE = ALL
   );


For CERTIFICATE Authentication
-----------------------------
CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022  
      , LISTENER_IP=ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION =  CERTIFICATE SQLHOST_CER 
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );

:::: SQLHOST_CER is the SQL Server Certificate authentication

CREATE CERTIFICATE SQLHOST_CER
   WITH SUBJECT = 'SQLHOST certificate for database mirroring',
   EXPIRY_DATE = '01/01/2020';