Sql Server Error 1474 Severity 16 State 1
I've got newly installed Win 2008 R2 machines, in a workgroup, in the same subnet, and of course SQL Server 2012.
I allowed login with SQL Server authentication, thinking it may help with each workstation having a different user account database. I created identical logins for each of three machines. Two have the full SQL Server software and one has SQL Server Express. Then I tried to mirror two different databases, producing the same result.
- Error: 28054, Severity: 11, State: 1. Service Broker needs to access the master key in the database 'XXXX'.
- Net DMA State. I am getting following message in my sql log and error 1418 on starting the Mirror and I am not able to telnet and My o/s on both server.
SysTools Inc. Box 36, Springville, Utah - 84663, USA +1 888 900 4529.
The first, I just right clicked on database in the SQL Server Management Studio, made a new database, then confirmed the recovery model was set to full. Backed it up and the transaction log. Restored the two items to my other server. (To start I'm not using the Witness yet in hopes to keep it as simple as possible) When restoring the data and log, for both I am restoring it without recovery. Then when I try to mirror, I get one warning about Fully qualified domain names, click through it because my servers are not in a domain, and then when it tries to mirror and comes back with:
An error occurred while starting mirroring.
Additional Information:
Alter failed for database 'test'. (Microsoft.SqlServer.Smo)
An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address 'TCP://(SERVERNAME):5022' can not 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, ErrorL 1418)
I have tried it with the AdventureWorks database too with the same result.
In Event Viewer I am seeing 'Database Mirroring connection error 4. 'An error occurred while receiving data.' '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.
In the SQL Server log I am seeing
Error 1443. Severity 16. State 2
Database mirroring has been terminated for database 'test'. This is informational only. No user action is required.
and
Error 1474. Severity 16. State 1.
Database Mirroring connection error 4. 'An error occurred while receiving data.' '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://(Mirrored SERVERNAME):5022'.
I have been doing searches on google trying to get my head around this, but I think because I am new to all this, I am having a hard time understanding where to go from here.
Colin 't Hart2 Answers
Sorry for the previous useless response - skipped over the fact that you are setting up a mirror without a domain. You will need to use certificates for this.
I found an article that might help you with that part. Check it out: http://mssqlnet.wordpress.com/2012/06/20/microsoft-sql-server-database-mirroring-in-a-workgroup-without-domain-with-certificate/
Sql Server Error 1474 Severity 16 State 1 Line 0
I tried to follow the way mentioned in the link above. I am using the certificate concept.
I have 3 instances on the same machine.Default one is for witness and other 2 instances are principal and mirror servers.
(1)Inside witness when I am running below query:
(2)Under instances Default,principal and mirror servers I have created the endpoint (similar to one mentioned in the link) which listen to 5024,5022 and 5023.
On the principal I ran below where database1 is the DB to be mirrored
One thing to mention that I am doing exactly what is mentioned in the link. So I am trying to setup DB mirroring without using domain certificates.
Max Vernon