Mirroring error 1418 despite all possible solutions

Question

Hello,

I have SQL Server 2008 installed on two servers, the principal server is running Windows Server 2008 R2 Enterprise and the mirror server is running Windows Server 2003 R2 Enterprise. I have tried everything to mirror the database but everytime I get stuck with 1418 error that TCP://PORTAL:5022 the address cannot be reached or does not exist. (Where PORTAL is the mirror server name and CPS is the principal server name). Here are the solutions that I have tried.

1. Both databases (Principal and Mirror) are in full recovery mode.

2. I restored mirror database with no recovery option and it is showing (Recovering...) with its name.

3. Telnet both servers with 5022 port and it worked fine.

4. Disabled firewall on both servers.

5. SQL Services on both servers start with NT Auth\Local Servcice Account.

6. I connect to mirror database with SA account having SQL Authentication because if I connect with windows authentication mode I get "login comes from untrusted" domain error.

7. I deleted end points on both machines and recreated them.

8. I checked the status of both end points and they both are "STARTED".

Both machines are running on same work group, they are not on domain. What should I do to resolve this ? Please advice.

Answer 1

Hi,

What are the permissions for connect on the endpoints? Are these two SQL servers in different domains. You can solve most mirroring issues by running the SQL service using the same domain/serviceaccount.

Can you run this and post the results?

SELECT perm.class_desc, perm.permission_name, endpoint_name = e.name, perm.state_desc,
 grantor = prin1.name, grantee = prin2.name
FROM master.sys.server_permissions perm
 INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id
 INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id
 LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id
WHERE  perm.class_desc = 'ENDPOINT'

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Thank you for replying Sean,

I don't know whether both servers come is same domain or not, we have two offices both located in same building but different floors and are connected locally using work group. If I try to connect to mirror server (via SQL Management Studio) remotely using windows authentication mode I get an error message that login comes from untrusted domain but if I use SA account with SQL authentication I get connected to mirror server and rest of mirror configuration runs fine. But once I start mirroring I get this message.

Sorry I don't have much knowledge of networking like domains etc... I am basically an ASP.net Web Developer and has been recently assigned with this mirroring problem. I'll run your provided query on both servers and post the result.

Answer 3

If they are different domain then use certificate here is more  http://msdn.microsoft.com/en-us/library/ms186384.aspx

---

http://uk.linkedin.com/in/ramjaddu

Answer 4

Thank you RamJaddu, I'll saw this on google but didn't gave much attention to it.... I'll check it out and let you know.

@Sean

This is the result of your query sean

ENDPOINT    CONNECT    TSQL Local Machine    GRANT    sa    public
ENDPOINT    CONNECT    TSQL Named Pipes    GRANT    sa    public
ENDPOINT    CONNECT    TSQL Default TCP    GRANT    sa    public
ENDPOINT    CONNECT    TSQL Default VIA    GRANT    sa    public

let me know if this is ok.

Thanks

Answer 5

Those are the 4 default endpoints that are used internally by SQL. Usually when you create the mirroring endpoint you need to specify the name and then grant connect permissions for the user that you are using for mirroring. Looks like you need to create the endpoints and grant the permissions.

If they are not in a domain I believe certificates are the only real option for authentication. Local user accounts across machines may not work (never tried it).

http://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/

Edit: Using SQL Authentication with the users and passwords configured may work but again I used domain accounts.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Thank you for quick reply Sean,

When I configure mirroring wizard it ask for two endpoints and then successfully make those endpoints right now in Server Objects -> Endpoints -> Database Mirroring I see an endpoint by the name of "Mirroring" which I created during mirror wizard, a similar endpoint in present on mirror server as well created by the wizard I used on principal server. What is the purpose of those endpoints if your query is not mentioning them ?

Thanks.