Microsoft sql server ошибка 1418 - Решение и исправление самых разных ошибок на TopOshibok.ru

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?

Mihir

8,69618 gold badges56 silver badges87 bronze badges

asked Jun 14, 2012 at 12:19

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

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.
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.
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.
Now do the mirror in the principal database. You did not get any error there.

answered Jun 15, 2012 at 11:14

MihirMihir

8,69618 gold badges56 silver badges87 bronze badges

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

answered Oct 24, 2013 at 9:55

scott_lotusscott_lotus

3,18122 gold badges51 silver badges69 bronze badges

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 )

answered Nov 9, 2015 at 19:53

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.

answered Feb 8, 2013 at 16:22

I met this issue before, all the validation pass.
Use a new endpoint at last, it works.
The root cause is 5022 may block other session.

answered Apr 15, 2014 at 7:01

For me, I had this issue crop up and resolved it ultimately by dropping and recreating the mirroring endpoints (SSMS -> Server Objects -> Endpoints -> Database Mirroring)

answered Nov 10, 2015 at 17:37

Mark SowulMark Sowul

10.2k1 gold badge45 silver badges51 bronze badges

My error 1418 was resolved by using the same drives letter on both servers…

Although MS suggests that you can use different letters, I decided to use the same drives letter…

From MS:

Also, we recommend that, if it is possible, the file path (including
the drive letter) of the mirror database be identical to the path of
the principal database. If the file paths must differ, for example, if
the principal database is on drive ‘F:’ but the mirror system lacks an
F: drive, you must include the MOVE option in the RESTORE statement.

KlajdPaja

9591 gold badge8 silver badges17 bronze badges

answered Jun 25, 2015 at 16:37

In case none of the above worked, here is what caused my problem.
Run the following query on both servers and look closely

SELECT @@Version

I had some updates on my principal server that were not installed on my mirror server.

Also, you can see additional info in SQL Server Log to reasons why the mirroring doesn’t work. That’s how I realized I had a different version on both servers.

answered Oct 20, 2016 at 17:49

On SQL Server 2016, the solution is to have your [domain account] as sysadmin on both principal and mirror server. Then create a new account as of the following

Principal server

use [master]
GO
CREATE LOGIN [domain\mirrorservername$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [domain\mirrorservername$]
GO

Mirror Server

use [master]
GO
CREATE LOGIN [domain\principalservername$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [domain\principalservername$]
GO

You need to create those account and grant CONNECT permission as those accounts are the ones used during the handshake between principal and mirror server.

For more details on the error, check your sql error logs.

Hope this post helps someone.

Fejs

2,7443 gold badges21 silver badges40 bronze badges

answered Sep 14, 2017 at 5:29

I had the same issue. I was trying to setup mirroring on Windows 2008 — R2, on a Domain setup. As suggested above by one of the experts, I checked the SQL Server ‘logon’ accounts for the SQLSERVER and SQLSERVERAGENT and used the same Domain account for the the SQL Server services on both machines. I was able to setup the mirroring.

Thanks for the help!

answered Dec 13, 2017 at 16:57

Источник

Are you stuck with SQL Server Mirroring Error 1418? We’ll help you fix this Mirroring Error.

The Database mirroring helps in creating redundant copies of a database and increasing the availability of a SQL Server database.

Here at Bobcares, we often receive requests to fix the Mirroring Error 1418 as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix the mirroring error 1418 for our customers.

What is SQL Server Mirroring 1418 Error?

The SQL Server Database Mirroring includes two SQL Server instances.

One instance acts as a primary instance and it is Principal instance. And the other one is a mirror instance.

The SQL Server Mirroring 1418 error occurs when the server network endpoint is not responding. Usually, it doesn’t respond because the specified server network cannot reach or it does not exist.

How to fix SQL Server Mirroring Error 1418?

Recently one of our customers contacted us with the error. Let’s discuss how our Support Engineers resolve the error for our customers.

The Mirroring Error 1418 mainly occurs due to the connection problems between the endpoints. We follow the below steps to fix the SQL Server Mirroring Error 1418.

1. We make sure that the system Firewall is not blocking the SQL Server port.

2.Then we go to Computer Management > Service and Application > SQL Server 2005 Configuration > Network Configuration > Enable TCP/IP protocol.

Also, we make sure that SQL SERVER port is 1433.

3. Then we check that the TCP/IP protocol is enabled or not. We go to Computer Management > Service and Application > SQL Server 2005 Configuration > Client Configuration > Enable TCP/IP protocol.

If the error doesn’t resolve by the above method, then we try the below methods also.

1. Service Accounts Verification

Sometimes the Mirroring 1418 error occurs due to the incorrect service account.

Firstly we Connect permissions to partner’s endpoints which are required by the SQL Server Service account.

We use domain accounts for all partners.

For the Local service, the certificate authentication is used and in the Network service, the computer account should be used.

2. Ports Verification

The database mirroring endpoint of each server instance is used only by the database mirroring process.

If any other processes are listening to the port assign to database mirroring endpoint then the other server instances cannot connect to the endpoint.

So we check that the Firewall is not blocking the port and allowing the traffic on both directions.

3. Verify Endpoints

We make sure that all endpoints are started.

All the endpoints must use the same encryption algorithm. We make sure that they are all using the same.

Also, we ensure that all the instances can access every other partner by Ping each partner from one another.

[Need assistance in SQL Server Mirroring Error 1418? – We can help you.]

Conclusion

In short, the Mirroring Error 1418 happens when the server network endpoints don’t respond. Also, we’ve discussed how our Support Engineers fix the error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

SQL Server comes with several inbuilt features for proper maintenance or availability of the database that supports failover clustering. One of them is SQL Server Database Mirroring, which is a disaster recovery and high availability technique that works only with databases using a full recovery model.

Though mirroring helps in increasing availability and data protection, the users encounter a common error while using the Mirroring feature, known as SQL Server Database Mirroring Error 1418. The blog discusses what is Microsoft SQL Server error 1418 Mirroring and how to resolve it.

SQL Server Database Mirroring Error 1418

The SQL Server Database Mirroring is a feature that involves two SQL Server instances where a copy of a single database is created on the same or different machines. One SQL Server instance that acts as a primary instance is called principal and other one is a mirrored instance called the mirror. One of the common Microsoft SQL Server error 1418 associated with mirroring that displays the following error message:

“The server network address cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.”

This error happens when the server network endpoint did not respond because the specified server network cannot be reached or it does not exist.

How to Troubleshoot Microsoft SQL Server Error 1418?

SQL Server Database Mirroring Error 1418 occurs due to connection problem between endpoints, the first thing we need to do is to check TCP/IP for the instance if it has been enabled or not.

Steps that need to be followed are:

Check if the system Firewall has not block SQL Server port.
Go to Computer Management → Service and Application → SQL Server 2005 Configuration → Network Configuration.
See if the TCP/IP protocol is enabled or not in Network Configuration and make sure that SQL SERVER port is by Default 1433.
Now check if TCP/IP protocol is enabled or not in Client Configuration Tab under Computer Management → Service and Application → SQL Server 2005 Configuration.

If the error cannot be resolved still, follow the below steps:

Manual Solutions Available for Users

Service Accounts Verification
- CONNECT permissions to partner’s endpoints is required by SQL Server Service account
- It is recommended that we use domain accounts for all partners.
- For Local service, certificate authentication should be used.
- For Network service, computer account should be used.
- See the SQL log for errors.
Ports Verification
While configuring network for a database mirroring session, database-mirroring endpoint of each server instance should be used only by database mirroring process. If another process is listening on the port assigned to database mirroring endpoint, the database mirroring processes of the other server instances cannot connect to the endpoint.
- Use the netstat command to display all the ports on which Windows-based server is listening.
- Use Telnet to test if the port is open and something is listening.
- Ensure no other SQL instances are connected on the server.
- Check if Firewall is allowing traffic both directions & not blocking the port
Ensure all instances can access every other partner
- Ping each partner from one another.
- To trace the path of the IP, use tracert to each partner
- Double check the ports if ping or tracert fails
Verify Endpoints
- Ensure that all endpoints are started.
- Make sure all endpoints are using same encryption algorithm.
- Ensure that OS supports chosen encryption algorithm on all partners.

Automated Solution if the Issue Arises Due to Database File Corruption

In case, the above-mentioned manual solutions do not work, then the problem is in the database. Evidently, the corruption in SQL databases is a well-known issue & this can also cause the mirroring SQL server error 1418. Simply download the repair & recovery tool that IT experts suggests to get rid of the corruption issues.

Download Now Purchase Now

After downloading the tool, follow these five simple steps to get a detailed solution.

Step-1. Launch the Tool and Navigate to the Open button to start fixing error 1418.

Step-2. Select Quick or Advance Scan to detect corruption.

Step-3. Preview the Database Objects and proceed further.

Step-4. Choose the Export settings accordingly as you prefer.

Step-5. Finally, Click on Export to fix the SQL error 1418.

Conclusion

The primary aim of this blog is to study the common error encountered by SQL database users while performing mirroring operations. Evidently, we hope now users can easily solve their Microsoft SQL Server Error 1418. This error restricts the server network endpoint from responding as the specified server network cannot be reached or it does not exist. It further suggests a solution for users to overcome SQL Server Database Mirroring Error 1418 using the steps defined in the blog.

Источник

Remove From My Forums

Question
Hi,

As I am going to configure Database Mirroring in sql server 2008 R2. It is giving error 1418.

The server network address «TCP://xyzserver.domain.con:5022» can not reached or does not exist. Check network address name and that the ports for the local and remote endpoints sre operational.(Microsoft SQL Server, Error: 1418)

I have tried the below steps:

1. ALTER ENDPOINT Mirroring FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED)

2. ALTER DATABASE PROD_DB SET PARTNER = ‘TCP://xyzserver.domain.Com:5022’

3. DROP ENDPOINT Mirroring

SELECT *

FROM sys.endpoints

4. CREATE ENDPOINT Mirroring

   STATE = STARTED

   AS TCP ( LISTENER_PORT = 60801 )

   FOR DATABASE_MIRRORING (

   AUTHENTICATION = WINDOWS KERBEROS,

   ENCRYPTION = SUPPORTED,

   ROLE=ALL);

SELECT state ,state_desc FROM sys.database_mirroring_endpoints

3. GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

4. GRANT CONNECT ON ENDPOINT::Mirroring TO SA

   WITH GRANT OPTION;

GO

I have tried all above the steps. But still I can not resolve the issue.

Can anyboby help me?

Sachin Jain

Answers

Please check below points

Make sure that the mirror database is ready for mirroring.

Make sure that the name and port of the mirror server instance are correct.

Make sure that the destination mirror server instance is not behind a firewall.

Make sure that the principal server instance is not behind a firewall.

Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.

To identify the listening ports and the processes that have those ports opened, follow these steps:

Obtain the process ID.

To learn the process ID of an instance of SQL Server, connect to that instance and use the following Transact-SQL statement:

SELECT SERVERPROPERTY(‘ProcessID’)

Match the process ID with the output of the following netstat command:

netstat -ano

also please check local host file are set-up correctly ipaddress and hostnames for (principal, mirror and witness)

http://uk.linkedin.com/in/ramjaddu
- Proposed as answer by
  
  Wednesday, July 27, 2011 6:40 AM
- Marked as answer by
  Peja Tao
  Monday, August 1, 2011 1:26 AM

Источник

We got error 1418 while configuring Database Mirroring on one of the SQL Server database instance. The error details are given below:

The server network address “TCP://ServerName.abc.local: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)

Reason behind Error 1418

Main reason behind database mirroring error 1418 is because the specified server network address cannot be reached or does not exist. The server network address cannot be reached because of multiple reasons. Some of them are given below.

Principal, Mirror and Witness servers are not talking to each other.
Wrong network address name, mirror server instance name or port number.
Mirror database is not ready for mirroring.
Principal or mirror server instance is behind a firewall.
You are using local system account to run SQL Server services and not using certificates for authentication.
SQL Server services are running with different domain accounts on Principal and Mirror server and service accounts don’t have enough permission on endpoints.
Different endpoint encryption value set on Principal, Mirror and Witness server.
Endpoints are not started on the partner servers.
Principal server instance is not listening on the port assigned to its database mirroring endpoint.

We need to verify each option mentioned above carefully to fix this error.

Solution

The solution of this issue may vary case to case depending on the nature of issue. To fix this issue we need to find out the main root cause behind error 1418. The root cause may be anything that is given in above section. Here I will describe each probable reason given above and its solution to fix this issue. Make sure to go through with each point given in this article.

Principal, Mirror and Witness Servers Should be Accessible

Make sure that SQL Server Instances installed on Principal, Mirror & Witness servers are accessible to each other. If remote login for any SQL Server instance is not enabled then you might get this issue. You can enable remote connectivity by enabling TCP/IP and Named Pipes protocols in SQL Server Configuration Manager. Each system must access the ports of the other SQL Server instances over TCP. You can try telnet the SQL Server ports of other partners.

Verify Correct System Details

Sometimes we entered wrong details that end up with throwing errors. Make sure to verify the network address name, mirror server instance name and its port numbers are correct. You can recheck all the details whether you have entered them correctly or not.

Mirror Database Preparation

Verify that the mirror database is ready for database mirroring. We should prepare mirror database before configuring or during configuring database mirroring. It is suggested to keep identical drive path for mirror database files. Below are the steps you should take care for preparing your mirroring database.

Run full backup and subsequent transactional log backup of the Principal database and copy it to the Mirror server.
Restore database with same name in norecovery state on Mirror server with the copied backup files from above step. You must ensure that restored database on mirror server should be in restoring mode before starting database mirroring configuration.

Firewall/Ports

One of the probable reason to get error 1418 is partner servers are behind firewall. Make sure that the principal server instance and destination mirror server instance is not behind a firewall and they should talk to each other. Principal server, Mirror server and witness server should be accessible to each other.

Verify that SQL Server port and endpoint ports are enabled and accessible via telnet. If not or you are not able to telnet any of the partner instance from any server participating in mirroring then you should ask your network admins to open these ports to establish the connectivity over given port.

Endpoints

Make sure that endpoint has been created on principal, mirror and witness servers. Verify that the endpoints are started on each partner server instances. You can check endpoint status by running below T-SQL query on each instance.

SELECT state_desc FROM sys.database_mirroring_endpoints

If either endpoint is not started, execute below ALTER ENDPOINT statement to start it.

#Change the name of your endpoints. Here endpoint name is Mirroring
ALTER ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = <port_number>)
FOR database_mirroring (ROLE = ALL);
GO

Recheck that the principal server instance is listening on the port assigned to its database mirroring endpoint and the mirror server instance is listening on its port. If a partner is not listening on its assigned port, modify the database mirroring endpoint to listen on a different port.

Sometimes this issue got fixed by dropping the existing endpoint and recreate it again on partner servers.

Service Accounts

Do not use local system account to run SQL Server services. If you don’t have any choice and want to use local system then ensure to use certificates for authentications.

Make sure that service accounts that you are using to run SQL Server services must have CONNECT permission to mirroring endpoints. Also, if you are configuring database mirroring between two domains then the login of one account must be created in master database on the other computer, and that login must be granted CONNECT permissions on the endpoint.

It’s always advisable to use same service accounts to run SQL Server services on all three Instances on Principal, Mirror and Witness server.

You can comment us your experience about fixing this issue. We will update this article with your solution that you have used to fix this issue.

I hope you like this article. Please follow us on our Facebook page and Twitter handle to get latest updates.

Read More:

Fix Error 15141: The server principal owns one or more endpoints and cannot be dropped
Fix AOAG Error 35250: Joining database on Secondary Replica resulted in an error
How to Upgrade or Patch Availability Group Instances?
SQL Server Always On Interview Questions and Answers

Author
Recent Posts

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.

Summary

Article Name

Fix Error 1418: The server network address cannot be reached or does not exist.

Description

We got error 1418 while configuring Database Mirroring on one of the SQL Server database instance. The error details are given below: The server network address “TCP://ServerName.abc.local: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)

Источник

What is SQL Server Mirroring 1418 Error?

How to fix SQL Server Mirroring Error 1418?

1. Service Accounts Verification

2. Ports Verification

3. Verify Endpoints

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

SQL Server Database Mirroring Error 1418

How to Troubleshoot Microsoft SQL Server Error 1418?

Manual Solutions Available for Users

Automated Solution if the Issue Arises Due to Database File Corruption

Conclusion

Question

Answers

Reason behind Error 1418

Solution

Principal, Mirror and Witness Servers Should be Accessible

Verify Correct System Details

Mirror Database Preparation

Firewall/Ports

Endpoints

Service Accounts