Issue with SQL Server mirroring endpoint creation using SQL Authentication

Question

Issue with SQL Server mirroring endpoint creation using SQL Authentication

Dan

Old Hand

Points: 335
More actions
September 7, 2022 at 9:14 am

#4084804

I am trying to create EndPoint in SQL Server 2019 using transact 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 Authentication for creation of mirroring endpoints. I can use SQL Management studio to do that, but could not find any examples on SQL Authentication method for transact SQL
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Best you can do is to script out the endpoint you've created using SSMS and SQL authentication and see what it used.

We always user the sql server service accounts ( or computer account ) because it provides better communication security options.

(WINDOWS KERBEROS + ENCRYPTION = REQUIRED )

Check "The following AUTHENTICATION and ENCRYPTION arguments are common to the SERVICE_BROKER and DATABASE_MIRRORING options."

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dan Old Hand Points: 335 More actions · Answer 2

Thanks Johan, After setting up SQL Authentication endpoint

when I run the Script Action, it shows a message "There is no action to be scripted"

I will try to do the windows Authentication, it has to be separate Windows accounts for Principal and Secondary servers and cross account permissions.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

during the setup process, the used accounts are being granted connect to the endpoint, so that is covered.