How to resolve Error: 1418 in sql server while mirroring

Question

I am trying to mirroring a database in sql server. I created a database and took a backup of it and restored on the another server. But after doing all operations and click on start mirroring, then it showing following error. I put "NO" to Witness server option.

I followed mirroring steps in this link Here it says, create an user on both principal and mirror server with the same credentials in STEP 3. Is it necessary to create same user on both servers? or any alternate solution is there?

Answer 1

I also faced same problem. you need to check the following items once.

1. Goto services.msc and check the sql server is running under which account. Make sure that sql server and sql server agent services should run with same credentials.

2. in the mirror database server you should do the same step as step 1. Give the same credentials as in principal server. If that user is not present create a new one on the both servers with same credentials.

3. Now got principal server and in sql server add the new login under Security-->Login. Give the server roles as Sysadmin and public. Add the same user on the mirror server also.

4. Now do the mirror in the principal database. You did not get any error there.

Answer 2

Same problem , error 1418. Did everything suggested here: link

In hindsight it was obvious. When completing the MIRRORING > CONFIGURE SECURITY WIZARD you get a page explaining the results. Looking at SQL1 and SQL2 nodes this line was the key:

On the principal server instance, SQL1
Listener Port: 5022
Encryption: **Yes**


On the mirror server instance, SQL2
Listener Port: 5022
Encryption: **No**

Solution. After finishing the wizard on all SQL nodes execute:

drop endpoint Mirroring
go


CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING 
(ENCRYPTION = DISABLED,ROLE=ALL)
GO

Hit start mirroring, problem solved.

Hope that helps someone Scott

Answer 3

After exhausting all the options as mentioned above, I had a variation to the mirroring environment. We had a SQL server 2008 contributing to 2014 Server in Principal-Mirror configuration. The 2014 Sql server was also being used in AlwaysOn config for HA of some other Db's. The end points are now named differently on sql 2008 and sql 2014.

After getting the above mentioned error, I found that the Encryption for 2008 is RC4 and that for 2014 is AES. As a result, it was failing handshake with the SQL2014 endpoint. I changed the encryption to match that of the SQL 2008 using the below command and viola!!

ALTER ENDPOINT [Hadr_endpoint]
    FOR DATA_MIRRORING ( ENCRYPTION  = REQUIRED ALGORITHM RC4 )

Answer 4

You also may not be leaving the copy intended for mirroring in a restore state, which it must be left in to turn on mirroring.

Typically, the procedure for SQL Server mirroring is:

1) Backup the original database and copy it to the server intended for mirroring.

2) Backup the transaction log and copy it to the server intended for mirroring.

3) Chose restore database from the MMC and locate the backup of the database and restore it with options set to leave in non-functioning state with no rollback of transactions.

4) Chose restore and chose files and then select log files and restore the log file backup also with the option of no rolloback and non-functioning. It is the second radio button down under the options page on sql server 2008.

5) go to the original database copy server and turn on mirroring. You will need to use an account that has sysadmin rights on both servers for the mirroring conversations to start up.