syntaxhelp.com Your online cheat sheet!

Creating a Login for a Domain User Account for Partner Instances

-- Domain Name: SYNTAXHELP
-- User Name: jacob

USE master ;
GO
CREATE LOGIN [SYNTAXHELP\jacob] FROM WINDOWS;

Grant connect permissions on Endpoint to login account

-- Domain Name: SYNTAXHELP
-- User Name: jacob
-- Endpoint Name: Mirroring

USE master ;
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [SYNTAXHELP\jacob];

Creating a Database Master Key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Abc@1234';
GO

Creating an Encrypted Certificate

-- Server Name: ServerPri
-- Subject: "Certificate for DB Mirroring"
USE master;
CREATE CERTIFICATE ServerPriCert 
   WITH SUBJECT = 'Certificate for DB Mirroring';
GO

Creating a Mirroring Endpoint that uses an encrypted Certificate

CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024,
      LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE ServerPriCert,
      ENCRYPTION = REQUIRED ALGORITHM AES,
      ROLE = ALL
   );
GO

Back up the certificate

BACKUP CERTIFICATE ServerPricert TO FILE = 'C:\ServerPricert.cer';
GO

Creating a Mirroring Endpoint that uses Windows KERBEROS Authenticatio

CREATE ENDPOINT mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
       AUTHENTICATION = WINDOWS KERBEROS,
       ENCRYPTION = SUPPORTED,
       ROLE=ALL);
GO

Creating a SQL Server Login for Mirroring

-- Login Name: jacob
USE master;
CREATE LOGIN jacob WITH PASSWORD = 'Jacob@1234';
GO

Creating a SQL Server User for the Login

-- Login Name: jacob
-- User Name: jacob
CREATE USER jacob FOR LOGIN jacob;
GO

Associating a Certificate with a User

-- Certificate Name: ServerPriCert
-- Login Name: jacob
-- Certificate Backup File: c:\ServerPriCert.cer

CREATE CERTIFICATE ServerPriCert
   AUTHORIZATION jacob
   FROM FILE = 'C:\ServerPricert.cer'
GO

Granting Connect Permission on the Login for Remote Mirroring Endpoint

-- Login Name: jacob
GRANT CONNECT ON ENDPOINT::Mirroring TO [jacob];
GO

Making a Database Trustworthy

-- DatabaseName: AdventureWorks
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON