Sql-server – MS SQL Start Mirroring Error

database-mirroringsql server

Helo,

I'm trying to mirror a db using a Principal, Mirror, and a Witness server.
Each computer that has it's own local ip address (v4) and I made them static so they would not change.

Then I did some reading and followed the steps on how to backup and restore a test database and then using the Mirroring Congure Security Wizard I'm able to connect to each of the Servers using the SQL Server Authentication not Windows Authentication and then I get the Success message then I click on Close and Start Mirroing but I then get an Error# 1418 Network address: "TCP://BAKSRV:5022" cannot be reached or does not exist, I then changed the network addresses to see if it will connect using IPv4 and entered the below info. into the Server network addresses:

Principal: TCP://10.1.10.1:5022
Mirror: TCP://10.1.10.2:5022
Witness: TCP://10.1.10.3:5022

I also tried using their computer names but it still gives me the same error

Error# 1418 Network address: "TCP://10.1.10.2:5022" cannot be reached or does not exist

on the Mirror Comp. I went into cmd and did netstat -a and I see this a number of times:

TCP ---- 10.1.10.2:5022 ------- Main-PC:diffrent port numbers ---- TIME_WAIT

I also disabled the firewall on the mirror comp. to rule it out but I still cannot Start Mirroring.

All Comps are runnning MS SQL 2012 and I'm using a TestDB that has like two fileds just to get it tested/working.

When I try this: SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints

I get:

name        role_desc   state_desc
Mirroring   PARTNER     STARTED

and when I try this: SELECT name, port FROM sys.tcp_endpoints

I get: 

name                            port
Dedicated Admin Connection      0
TSQL Default TCP                0
Mirroring                       5022

Additional Info.

when I go into SQL Server Configuration Manager and Select SQL Server under Log On: This Account is check, and there is a Account Name: .\BKAccount and the password is also filled out. all 3 servers have the same settings but all of them have a different username/password. I don't know if this helps any but when I'm in SQL Management Studio I can only connect to the Mirror server and Witness server by selecting Authentication: SQL Server Authentication, and not by selecting windows authentication.

Can someone please help me what am I doing wrong and how can I get this to work….
Please HELP!!!

Thanks again…

Best Answer

It sounds like you've probably got a problem with your endpoints.

If you haven't already you need to make sure that you've got the endpoints configured correctly and started on both servers. You can confirm they're there by running: 

SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints

If you've already done this I would recommend dropping and recreating them both and then trying to connect again. The steps for doing this are in books online: http://technet.microsoft.com/en-us/library/ms190456.aspx

UPDATE:

Based on your comments above you're using SQL Authentication to connect to the instance. And the server instances are running under local (non-domain) accounts. In order for this to work you will have to use certificates for endpoint authentication. And to use certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

Unfortunately there isn't an automatic way to configure the database mirroring security using certificates. So once you've got the certificates configured you'll have to manually create the endpoints with CREATE ENDPOINT. see: Determining the Authentication Type for a Database Mirroring Endpoint

Since you've already indicated that you're having a hard time it might be easier for you to use Windows Authentication. Here's the basic outline of what would be involved:

  1. Make sure that the SQL instances are configured for mixed mode authentication

  2. You'll need to create the appropriate domain logins that you'll want to use. Per: Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server)

    Under Windows Authentication, each server instance logs in to the other side using the Windows credentials of the Windows user account under which the process is running. Windows Authentication might require some manual configuration of login accounts, as follows:

    • If the instances of SQL Server run as services under the same domain account, no extra configuration is required.
    • If the instances of SQL Server run as services under different domain accounts (in the same or trusted domains), the login of each account must be created in master on each of the other server instances, and that login must be granted CONNECT permissions on the endpoint.
    • If the instances of SQL Server run as the Network Service account, the login of the each host computer account (DomainName\ComputerName$) must be created in master on each of the other servers, and that login must be granted CONNECT permissions on the endpoint. This is because a server instance running under the Network Service account authenticates using the domain account of the host computer.

  3. Depending on how you have the SQL Server services configured above you may need to setup the login accounts

I know this is a lot of info; once you've got the authentication sorted out you should be able to get this working with the GUI. Books online also has a really helpful example that includes all of the TSQL that you'd need to do this. Once you have the authentication setup you can pretty much use the example codes to get it setup. Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL)

Related Topic