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.
- Edited by Tahir Ahmed Saturday, January 28, 2012 10:09 AM
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'
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.
- Edited by Tahir Ahmed Saturday, January 28, 2012 12:43 PM
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.
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.
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).
Edit: Using SQL Authentication with the users and passwords configured may work but again I used domain accounts.
- Edited by Sean Massey Monday, January 30, 2012 11:53 AM
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 ?