December 3, 2011 at 5:44 am
Hi,
I'm trying to set up mirroring with a witness using my 3 dedicated servers running SQL Server 2008 R2. I've set up everything as described at this blog post[/url]
Mirroring seems to work fine between the principal and secondary server. But when I run the very last command to add the witness
alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';
I get the following error after a few seconds:
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.
Also, the event log on the principal shows the following error:
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.
I've already shut down the firewall on the witness, and I've successfully telnetted from the principal into the mirroring endpoint of the witness server to make sure there is no connectivity problem. Yet, I still get this error.
My setup is as follows:
Principal: SQL Server 2008 R2 Standard 10.50.1617
Mirror: SQL Server 2008 R2 Standard 10.50.1617
Witness: SQL Server 2008 R2 Express 10.50.1600
None of the servers is in a domain.
Please help me SQL gurus, you're my only hope!
December 5, 2011 at 9:00 am
I think the two likeliest causes are the witness server can't communicate back to the principle server or the authentication isn't working. If you haven't already, check out this link [/url]for help on these issues and some others to look at. But briefly, have you looked in the event log of the witness server?
Steve
SQL Managed
December 6, 2011 at 1:45 am
adrian-844207 (12/3/2011)
alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';
Where are you executing this command from?
Have you double checked the steps to create the certificate and the user?
What value are you supplying for witness_address (IP, name, etc)?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2011 at 7:46 am
Steve Turner - SQL Managed (12/5/2011)
I think the two likeliest causes are the witness server can't communicate back to the principle server or the authentication isn't working. If you haven't already, check out this link [/url]for help on these issues and some others to look at. But briefly, have you looked in the event log of the witness server?
Thanks for the list, I went through every single of these points and double checked it, but none of this seems to be the cause of my problem.
Also, there is nothing in the event logs on principal, mirror or witness, apart from a message on the principal and mirror that mirroring has been stopped and started again when I try to add the witness and, on the principal, the following error message:
Database mirroring connection error 4 'An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.' for 'TCP://witness.MYDOMAIN.com:5022'.
December 6, 2011 at 7:50 am
Perry Whittle (12/6/2011)
adrian-844207 (12/3/2011)
alter database citec_vc_prod set witness = 'TCP://witness_address:witness_port';
Where are you executing this command from?
Have you double checked the steps to create the certificate and the user?
What value are you supplying for witness_address (IP, name, etc)?
1. I am executing this command from within SSMS from my workstation, but on the principal.
2. I have quatruple-checked every single step. I also started from scratch multiple times to make sure I did not miss something. I'll post a full log of the t-sql commands I used for setting up mirroring in the next answer.
3. I am using DNS for name resolution. I checked that all servers can resolve the other 2 server's IP addresses properly.
December 6, 2011 at 7:53 am
Here's a complete log of the t-sql commands I used for setting up mirroring:
-- PRINCIPAL
create master key encryption by password = 'mypassword';
GO
create certificate "server1.ourdomain.com_cert" with subject = 'server1.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate "server1.ourdomain.com_cert", encryption = disabled, role = all);
GO
Backup certificate "server1.ourdomain.com_cert" to file = 'f:\cert\server1.ourdomain.com_cert.cer';
GO
-- MIRROR
create master key encryption by password = 'mypassword';
GO
create certificate "server2.ourdomain.com_cert" with subject = 'server2.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate "server2.ourdomain.com_cert", encryption = disabled, role = all);
GO
Backup certificate "server2.ourdomain.com_cert" to file = 'f:\cert\server2.ourdomain.com_cert.cer';
GO
-- WITNESS
create master key encryption by password = 'mypassword';
GO
create certificate "witness.ourdomain.com_cert" with subject = 'witness.ourdomain.com certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate "witness.ourdomain.com_cert", encryption = disabled,role = witness);
GO
Backup certificate "witness.ourdomain.com_cert" to file = 'd:\cert\witness.ourdomain.com_cert.cer';
GO
-- PRINCIPAL again
create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login";
GO
select * from sys.certificates
Create certificate "server2.ourdomain.com_cert"
Authorization "server2.ourdomain.com_user"
From file = 'f:\cert\server2.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login];
GO
------
create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login";
GO
Create certificate "witness.ourdomain.com_cert"
Authorization "witness.ourdomain.com_user"
From file = 'f:\cert\witness.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [witness.ourdomain.com_login];
GO
-- MIRROR again
create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login";
GO
Create certificate "server1.ourdomain.com_cert"
Authorization "server1.ourdomain.com_user"
From file = 'f:\cert\server1.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login];
GO
-------
create login "witness.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "witness.ourdomain.com_user" from login "witness.ourdomain.com_login";
GO
Create certificate "witness.ourdomain.com_cert"
Authorization "witness.ourdomain.com_user"
From file = 'f:\cert\witness.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [witness.ourdomain.com_login];
GO
-- WITNESS again
create login "server1.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server1.ourdomain.com_user" from login "server1.ourdomain.com_login";
GO
Create certificate "server1.ourdomain.com_cert"
Authorization "server1.ourdomain.com_user"
From file = 'd:\cert\server1.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [server1.ourdomain.com_login];
GO
-------
create login "server2.ourdomain.com_login" with PASSWORD = 'mypassword';
GO
create user "server2.ourdomain.com_user" from login "server2.ourdomain.com_login";
GO
Create certificate "server2.ourdomain.com_cert"
Authorization "server2.ourdomain.com_user"
From file = 'd:\cert\server2.ourdomain.com_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [server2.ourdomain.com_login];
GO
-- MIRROR again
alter database MyDBName set partner OFF
alter database MyDBName set partner = 'TCP://server1.ourdomain.com:5022';
GO
-- PRINCIPAL again
alter database MyDBName set partner OFF
alter database MyDBName set partner = 'TCP://server2.ourdomain.com:5022';
GO
alter database MyDBName set witness OFF
alter database MyDBName set witness = 'TCP://witness.ourdomain.com:5022';
GO
December 6, 2011 at 1:53 pm
adrian-844207 (12/3/2011)
None of the servers is in a domain.
so how come your script above references server1.ourdomain.com and server2.ourdomain.com
If setting up mirrroring in a workgroup please see the following article
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2011 at 4:39 pm
Perry Whittle (12/6/2011)
adrian-844207 (12/3/2011)
None of the servers is in a domain.so how come your script above references server1.ourdomain.com and server2.ourdomain.com
If setting up mirrroring in a workgroup please see the following article
Sorry about the misunderstanding. What I mean is that my servers are in separate workgroups. I am not using windows domains, but yes, I am using top-level domains. In other words, I am using goold old DNS to resolve the server's ip addresses. I hope this clarifies what I mean.
Thanks for the tutorial, but I see no difference between the scripts shown there and the one I listed, apart from mine using not encryption on any andpoint. I'll give it a shot nevertheless tomorrow because I'm really desperate, but it doesn't look as if it will make a difference. I'll keep this thread updated either way.
December 6, 2011 at 8:21 pm
Sorry if this seems obvious, but is your witness server configured for "SQL Server and Windows authentication mode" or just "Windows authentication mode"?
And do you have login auditing turned on for both failed and successful attempts so you can see if the principal server you're executing the "alter database" command from is able to authenticate to your witness server or not?
SQL Managed
December 7, 2011 at 3:49 am
Steve Turner - SQL Managed (12/6/2011)
Sorry if this seems obvious, but is your witness server configured for "SQL Server and Windows authentication mode" or just "Windows authentication mode"?And do you have login auditing turned on for both failed and successful attempts so you can see if the principal server you're executing the "alter database" command from is able to authenticate to your witness server or not?
Yes, SQL Server authentication is switched on for all three servers. I am administrating the servers via SSMS from my desktop via SQL Server authentication.
I just switched on login auditing for both successful and failed login attempts, but I am not sure how to monitor the logged data.
December 7, 2011 at 7:35 pm
Successful and Failure logins are written to the Event Viewer, application log. I think you may have to restart the service or server to pick up any changes to the settings. But after thinking about it, it's probably easier for you to just run profiler to check for login events. There is an Audit Database Mirroring Login Event Class that you can trace using Profiler.
SQL Managed
December 8, 2011 at 2:09 am
Thanks for the hint. I tried this, but for some reason I can't seem to see any mirroring-related login / logout events in my profiler. I can see other login / logout events.
The long version:
I just used SSMS to set "Server Preferences" > "Security" > "Login Auditing" to "Both failed and successful logins", then attached a profiler to the witness db to monitor login and logout audit events. Then I tried adding the witness to my mirroring setup as before. There was no login event. I tried logging into the witness with SSMS just to test the profiler, and that showed up in my profiler as expected.
Then I did the same on the principal and restarted mirroring. Mirroring was started successfully (as usual), but there was no login / logout event related to mirroring monitored.
---
On an unrelated note: I did another test to see to ascertain that really is connectivity between principal and witness: If I switch on the windows firewall for the witness and block port 5022, I get the following error in the principal event log:
Database mirroring connection error 2 'Connection attempt failed with error: '10060(failed to retrieve text for this error. Reason: 15105)'.' for 'TCP://wtn.logmytime.de:5022'.
If I switch the windows firewall off for the witness, I get the following error in the principal event log:
Database mirroring connection error 4 'An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.' for 'TCP://wtn.logmytime.de:5022'.
So the firewall does not seem to be the problem here since it was switched off during my "real" attempts to make this work.
December 8, 2011 at 7:56 am
Too bad it's not showing up but I'm not sure why. I have a mirror running in a domain and it isn't certificate based so there are some differences but with my mirror the SQL services are all running with an AD account and that account has a SQL login in all three servers and is granted the Connect permission to the mirror endpoints. I've never setup mirroring with certificates so I'm not sure how that changes things. You should probably double check all the settings again using these catalog views: sys.database_mirroring, sys.database_mirroring_endpoints, and sys.database_mirroring_witnesses.
This link has scripts for querying those views and also has the below statement which makes me think the service account of your database instances must have connect permission to the other endpoints with or without a domain.
"The user account under which the SQL Server instance is running has the necessary CONNECT permissions. If SQL Server is running under the same domain user on all the servers involved in the mirroring session (principal, mirror, witness), then there is no need to grant permissions. But if SQL Server runs under a different user on one or more of these servers, then you need to grant CONNECT permission on the endpoint of a server to the login account of the other servers."
Good luck.
SQL Managed
December 8, 2011 at 8:12 am
One other thing, I think I mentioned this before but don't remember if you verified... Can you connect to the principle and mirror from the Witness server? Have you opened the port on the principle and mirror server to the witness server?
I apologize if we already tried this.
SQL Managed
December 8, 2011 at 8:53 am
OK, I just read up quickly on certificate based authentication for mirroring so it shouldn't care about the service account. I'd still check on the other stuff, though.
SQL Managed
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply