Issue with SQL Server mirroring endpoint creation using SQL Server authentication

userTXp5 0
Viewed 45

I am trying to create an endpoint in SQL Server 2019 using T-SQL:

CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED AS TCP ( LISTENER_PORT = 5022 )
    FOR DATABASE_MIRRORING
    ( AUTHENTICATION = WINDOWS NEGOTIATE,
      ENCRYPTION = SUPPORTED,
      ROLE=ALL);

GO

I want to use SQL Server authentication for creation of mirroring endpoints. I can use SQL Server Management Studio to do that, but could not find any examples on SQL Server authentication method for T-SQL.

Thanks

mirroring endpoint sql-server sql
1 Answers

As documented, it is not supported to use SQL Authentication for ENDPOINT, there is also no syntax for it.

  • If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints.....
  • If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication....

So if you cannot use Windows Authentication, then I suggest you use Certificates. This is documented here.

Charlieface0
Related