How to add/Setup Witness Server in Existing Database Mirroring in SQL 2012 and How to solve Microsoft SQL Server Msg 1456

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 

'TCP://witness_address:witness_port'. The database mirroring configuration was not changed. 

Verify that the server is connected, and try again.

From this we are using Following instances in Database Mirroring

Principal                SQLNODE01 

Mirror                   SQLNODE03

Witness                 SQLNODE01\SQL2012NODE01 (Named instance of SQLNODE01)

when you are trying to configure Database mirroring using basics steps

Take Backup in Principal

Restore Backup in Mirror

Right Click Principal properties and configure database mirroring with Principal,Mirror,Witness with endpoint

after successful wizard it will give same window like below

We will not give Do start mirroring and again if we click mirroring we got below Error

To check Endpoint Encryption same on all Principal and Mirror and Witness

If we are checking with error further with Event viewer Run-->Eventvwr

we need to check algorithm used on Endpoint should be same or both mirroring endpoint

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'.  [CLIENT: xxxxxxx]

Here you can see below both are different

after changed all encrypt same as see below

Kindly run below Query On the Principal & Mirror & Witness Server:

select encryption_algorithm_desc from sys.database_mirroring_endpoints

After correcting this error while we trying to alter database name on principal to set witness

Kindly run below  Query on Principal

Alter database [DBMirroringTest] set Witness ='TCP://SQLNode01.SQLtechi.com:5023'

we see same error on Event viewer

Database Mirroring login attempt by user 'Node\SQLServiceAccount.' failed with error: 'Connection handshake failed. The login 'Node\SQLServiceAccount' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 192.168.2.52]

To resolve Connection handshake failed. The login 'Node\SQLServiceAccount' does not have CONNECT permission on the endpoint. State 84 Error


1.We should need to check SQL Services are running on Domain Account or Services account other than Local System or others

2. We should need to Grant Connect permission on Domain Account using below Query

Grant connect on ENDPOINT::Mirroring to [Node\SQLServiceAccount]


below you can see Witness Server SQL2012NODE01 is running default account

it should need to run on Service account or Domain account

here you can see we are changing from default to domain account on SQL Services

After we have changed account on SQL Services @ Witness server

also we have granted connect permission on domain account

now we are alter database on Principal to set witness

this time it ran successfully

now you can see we are successfully added Witness on existing Database Mirroring configuration

Script:

To check Encryption details

select encryption_algorithm_desc from sys.database_mirroring_endpoints


To Grant Connect to Endpoint to Account


Grant connect on ENDPOINT::Mirroring to [Node\SQLServiceAccount]

To see Endpoints details

Select * from sys.database_mirroring_endpoints

--To stop 

ALTER ENDPOINT mirroring STATE=STOPPED

--To start

ALTER ENDPOINT mirroring STATE=STARTED

To Make database from No Recovery to usable mode of users

restore database databasename with recovery

To TCP endpoint info

SELECT type_desc, port FROM sys.tcp_endpoints;

SELECT Name,state_desc,Role FROM sys.database_mirroring_endpoints;

To see Grantor and Grantee info

SELECT EP.name, SP.STATE, 

   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 

      AS GRANTOR, 

   SP.TYPE AS PERMISSION,

   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 

      AS GRANTEE 

   FROM sys.server_permissions SP , sys.endpoints EP

   WHERE SP.major_id = EP.endpoint_id

   ORDER BY Permission,grantor, grantee; 

GO

Ref:

https://blogs.msdn.microsoft.com/docast/2015/07/30/database-mirroring-configuration-failure-scenarios/

demonstration purpose, I am using SQL 2012 instances on Windows 2012 R2 server.

Principal Server: SQLServer\SQL2012
Mirror Server: SQLServerDR\SQL2012
Witness Server: Witness\Witness
Mirrored Database: DBMirror
Mirroring Port : 5022
Service Account: SQLServiceAccount@contoso.lab

SCENARIO 1:

Before starting the database mirroring configuration, the mirror database must be created by restoring WITH NORECOVERY a recent full backup and, perhaps, log backups of the principal database onto the mirror server.

To start the Database mirroring configuration, I started the Database Mirroring Security Wizard on the Principal Server. While trying to connect to the Mirror Server instance from Principal Server, I was getting the below connectivity error:

Cannot connect to SQLSERVERDR\SQL2012.
ADDITIONAL INFORMATION:
Failed to connect to server SQLSERVERDR\SQL2012. (Microsoft.SqlServer.ConnectionInfo)
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

To check if the SQL Server Mirror instance TCP port and SQL Server browser UDP port is blocked or not, I used the tool PortqueryUI. The tool can be downloaded from: http://www.microsoft.com/en-us/download/details.aspx?id=24009

 

Figure 1. Portquery output for SQL Server Mirror Instance Port

 

Figure 2. Portquery output for SQL Server browser Port

PortQuery output indicated that the SQL Server Mirror Instance Port and SQL Server Browser port was blocked on the Mirror Server. I created Windows Firewall rules for 49450 (Mirror Instance Port) and 1434 (SQL browser UDP Port) and post which I was able to connect to the Mirror Instance and successfully configure Database Mirroring.

If you encounter any further issues with SQL Connectivity, please follow: http://blogs.msdn.com/b/docast/archive/2014/10/24/tips-and-tricks-to-fix-the-sql-connectivity-issues.aspx

SCENARIO 2:

In this scenario, I was able to create the database endpoints on the Principal and the Mirror Server. However, while starting Mirroring (below screenshot), I was getting errors:

 

Figure 3. Database Mirroring ‘start Mirroring’

 

Figure 4. Error while configuring mirroring

An error occurred while starting mirroring.
Additional information:
Alter failed for Database ‘DBMirror’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address “TCP://sqlserverdr.Contoso.lab:5022” cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error:1418)

This is a very common error message while configuring mirroring. Database endpoints are configured on Port 5022 by default. I used the PortQueryUI tool again to check if the mirroring port was blocked or not.

 

Figure 5. PortQuery output for Database Mirroring Port

PortQueryUI output showed “FILTERED”, which indicates that the mirroring Port was blocked. So I created a Windows Firewall rule for the SQL Mirroring Port. After creating the rule, I was able to configure mirroring successfully.

 

Figure 6: Successful configuration of Database Mirroring

SCENARIO 3:

Database mirroring is already configured, but the data synchronization from Principal to Mirror fails with the error:

No connection: cannot connect to the mirror server instance

 

Figure 7. Mirroring Synchronization failure

Looking at the Error log on the Principal Server:

Database Mirroring login attempt by user 'Node\SQLServiceAccount.' failed with error: 'Connection handshake failed. The login 'Node\SQLServiceAccount' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 192.168.2.52]

The above error indicates that the SQL Service account doesn’t have connection permission on the mirroring endpoints:

I executed the below query which resets the CONNECT permission for SQL Service account on database mirroring endpoints which fixed the synchronization issue.

Grant connect on ENDPOINT::Mirroring to [Node\SQLServiceAccount]

SCENARIO 4:

Database mirroring is already configured, but the synchronization from Principal to Mirror fails with the error:

No connection: cannot connect to the mirror server instance

 

 Figure 8. Mirroring Synchronization Failure.           

Reviewing the SQL Server Error logs on the primary instance:

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'.  [CLIENT: 192.168.2.52]

As per the error message “There is no compatible encryption algorithm”. ENCRYPTION is set to REQUIRED by default. This means that all connections to this endpoint must use encryption. However, you can disable encryption or make it optional on an endpoint.

I reviewed the encryption algorithm used:

On the Principal Server:

select encryption_algorithm_desc from sys.database_mirroring_endpoints

AES

On the Mirror Server :

select encryption_algorithm_desc from sys.database_mirroring_endpoints

RC4

Scripted out the Mirroring endpoint on the Principal and Mirror Servers:

Principal Server:

CREATE ENDPOINT[Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)

FOR DATA_MIRRORING (ROLE=PARTNER,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM AES)

Mirror Server:

CREATE ENDPOINT[Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)

FOR DATA_MIRRORING (ROLE=PARTNER,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM RC4)

This error usually occurs if the mirroring endpoints are pre-staged. To fix the issue, I had to alter the mirroring endpoint to use the same encryption algorithm.