sql error 15138: logs

Showing posts with label logs. Show all posts

Thursday, March 29, 2012

Error 18456 Severity 14 State 16

I am getting the following error in my Sql logs and in my Application Event
viewer:
03/16/2007 07:56:00,Logon,Unknown,Login failed for user
'CTPC\Administrator'. [CLIENT: <local machine>]
03/16/2007 07:56:00,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 1
6.
0000: 18 48 00 00 0e 00 00 00 .H.....
0008: 09 00 00 00 49 00 4e 00 ...I.N.
0010: 54 00 52 00 41 00 4e 00 T.R.A.N.
0018: 45 00 54 00 00 00 07 00 E.T....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...
I have tried figure out what process is trying to hit this database but to
no avail, how should I go about troubleshooting this? It logs this every on
e
minute and it is on our Portal server.
Thanks,
RobYou can run a trace or Profiler when a login fails and see
what shows up for the application name when auditing the
Login Failures.
It's on the same box as SQL Server so you could also check
what services may be running under the local administrator
account.The security logs may have more information as well
if you track the logins and details for the logins.
-Sue
On Fri, 16 Mar 2007 06:02:16 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:

>I am getting the following error in my Sql logs and in my Application Event
>viewer:
>03/16/2007 07:56:00,Logon,Unknown,Login failed for user
>'CTPC\Administrator'. [CLIENT: <local machine>]
>03/16/2007 07:56:00,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State:
16.
>0000: 18 48 00 00 0e 00 00 00 .H.....
>0008: 09 00 00 00 49 00 4e 00 ...I.N.
>0010: 54 00 52 00 41 00 4e 00 T.R.A.N.
>0018: 45 00 54 00 00 00 07 00 E.T....
>0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
>0028: 74 00 65 00 72 00 00 00 t.e.r...
>I have tried figure out what process is trying to hit this database but to
>no avail, how should I go about troubleshooting this? It logs this every o
ne
>minute and it is on our Portal server.
>Thanks,
>Rob

Error 18456 Login failed for user domain\user

We have an application that logs into our SQL Server 2000 using ODBC. All
service packs and critical updates are current on the server and clients.
SQL Server uses mixed authentification and runs under a domain account, the
users login with their domain\username, or at least they used to. Nobody
seems to know exactly when this stopped working, and of course, nobody said
anything until this week. Attempting to login now fails with error 18456
login failed for user domain\username. I've foolwed all the debugging info I
can find on Microsofts site. Running osql, I can log in using windows
authentification and SQL Server accounts with no problem. Trying to use
domain\username fails. I've been through everything from DNS to SQL Server
settings to client settings with no resolution. Anybody have an idea where
else to look?
Any hints would be greatly appreciated.
BruceHi Bruce,
Thank you for use the newsgroup and it is my pleasure to help you with you
issue.
From you information, you application, which used to work fine now got
error message
Could you check if the ODBC connection is made through the standard
security of SQL Server of trusted connection security? Is the SQL Server
authentication mode match the security option of ODBC connection? Could you
using the Query Analyzer to connect the SQL Server? That is, if you use the
standard security, such as use the account 'sa', when using 'sa' in Query
Analyzer, could it connect the SQL Server. When it is a Windows
authentication, when connect the SQL Server by Query Analyzer, you should
choose the Windows Authenticatin in the Query Analyzer.
Looking forward to your response. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong:
Using Windows Authentification or Sql Server Authentification both work
fine.
If I'm logged into the domain I can run
osql -S SERVER -d Test -E
or
osql -S SERVER -d Test -U SA -P password
with no problem I can connect through Query analyzer either way.
if I try
osql -S SERVER -d Test -U domain\username -P password
it fails with the message Error 18456 Login failed for user domain\username.
The application we are using was working with the domain\username login, I
can't use Windows Integrated security with it, and I really do not want to
setup and manage 50+ SQL Server accounts. I am assuming that something has
changed in the authentification / delegation process and am working through
that now. As far as I can tell nothing has changed, but obviously something
has.
Any ideas would be appreciated.
Bruce
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:EKzqmHs7DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Bruce,
> Thank you for use the newsgroup and it is my pleasure to help you with you
> issue.
> From you information, you application, which used to work fine now got
> error message
> Could you check if the ODBC connection is made through the standard
> security of SQL Server of trusted connection security? Is the SQL Server
> authentication mode match the security option of ODBC connection? Could
you
> using the Query Analyzer to connect the SQL Server? That is, if you use
the
> standard security, such as use the account 'sa', when using 'sa' in Query
> Analyzer, could it connect the SQL Server. When it is a Windows
> authentication, when connect the SQL Server by Query Analyzer, you should
> choose the Windows Authenticatin in the Query Analyzer.
> Looking forward to your response. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Bruce,
Thank you for your update.
For the 'osql -S SERVER -d Test -U domain\username -P password' which
failed with the error message, could you check if it is a valid SQL Server
login? You could check it by unfolder the database, unfoulder the
'Security', then in the logins, is the above 'domain\username' in the
logins?
Looking forward to your response. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Yes, it is a valid login and the password is correct. The account also has
privledges (dbowner + our SQL_Admin role) to access the test database. It
works fine with Windows Integrated Security, so I don't think it is related
to SQL Server privledges, my guess is that something has changed in the
authentification authorization or delegation between the domain and the
server or the client.
The client computers are running MDAC 2.8 or 2.7RTM, and component checker
says they are current and all is well. Servers and clients are current on
patches, SQL Server is current. I've been throgh every article I can find on
MSDN that remotely deals with this error, and everything seems to check out.
I'm sure there is some little thing I'm overlooking, but I'm totally stumped
at this point.
Bruce
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:ZZofg867DHA.2508@.cpmsftngxa07.phx.gbl...
> Hi Bruce,
> Thank you for your update.
> For the 'osql -S SERVER -d Test -U domain\username -P password' which
> failed with the error message, could you check if it is a valid SQL Server
> login? You could check it by unfolder the database, unfoulder the
> 'Security', then in the logins, is the above 'domain\username' in the
> logins?
> Looking forward to your response. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Bruce,
The exact same thing happen here - ODBC connections that had worked
stopped working. We THINK it was related to installing patches but not
the full SP3a running on SQL 2000. We know installing SP3a fixed it.
An easy test to see if it's the same problem - in the ODBC config, set
the server name servername.subdomain.domain.?, check with the network
people for the full name if you need to.
If it works then maybe install/reinstall SP3a on the server will help.
SQL DBA in Richmond, VA
PS - I know 4 locales that suddenly experienced this same problem.
Maybe triggered by Windows critical update?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Tuesday, March 27, 2012

Error 17883

<shandain@.gmail.com> wrote in message
news:1125535239.274678.73180@.o13g2000cwo.googlegro ups.com...
> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>
|||I met this error on SP3a after moving to SAN. Now we are trying to install
SP4 to fix this issue. You can find my post on Sept 1. Are you running on
SAN?
Bill
"shandain@.gmail.com" wrote:

> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>
|||How do i remove MS05-027 as it is not showing up in the add remove programs.
(I'm not sure if i have removed it but i am still getting the 17883 errors
when trying to backup databases)
"ME" wrote:

> I had this error after MS05-027 applied. Removing MS05-027 fixed the
> problem. It happened on 2 DL380 G2 Compaq servers with exac same h/w. All
> drivers were up-to-date. Engineers from MS admitted it was a rare bug
> reported only by 2 customers. Since it affected only 2 customers MS didn't
> want to think of a fix and asked us to seal the port 454.

>
> <shandain@.gmail.com> wrote in message
> news:1125535239.274678.73180@.o13g2000cwo.googlegro ups.com...
>
>

Monday, March 26, 2012

Error 17883

<shandain@.gmail.com> wrote in message
news:1125535239.274678.73180@.o13g2000cwo.googlegroups.com...
> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>|||I met this error on SP3a after moving to SAN. Now we are trying to install
SP4 to fix this issue. You can find my post on Sept 1. Are you running on
SAN?
Bill
"shandain@.gmail.com" wrote:

> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>|||How do i remove MS05-027 as it is not showing up in the add remove programs.
(I'm not sure if i have removed it but i am still getting the 17883 errors
when trying to backup databases)
"ME" wrote:

> I had this error after MS05-027 applied. Removing MS05-027 fixed the
> problem. It happened on 2 DL380 G2 Compaq servers with exac same h/w. Al
l
> drivers were up-to-date. Engineers from MS admitted it was a rare bug
> reported only by 2 customers. Since it affected only 2 customers MS didn'
t
> want to think of a fix and asked us to seal the port 454.

>
> <shandain@.gmail.com> wrote in message
> news:1125535239.274678.73180@.o13g2000cwo.googlegroups.com...
>
>

Error 17883

Hi ~
I realize that this has been posted on, but I am confused. We are
getting an error in our event logs at odd times (not at backup times or
anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
Scheduler 3, or a variant there of.. and so far, the only solution
that I can see for this problem on a Win2K server running SQL 2000 SP4
is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
option though, as this would open the server to known security flaws,
etc... correct? Are there any other options?Hi
17883 errors have been around since SP3 (the error was introduced to show
issues that were there, but were not detectable by users).
More and more have been fixed in the various hotfixes since then. It is best
for you to open a case with Microsoft Support so that they can sort the
problem out for you.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"shandain@.gmail.com" wrote:
> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>|||I had this error after MS05-027 applied. Removing MS05-027 fixed the
problem. It happened on 2 DL380 G2 Compaq servers with exac same h/w. All
drivers were up-to-date. Engineers from MS admitted it was a rare bug
reported only by 2 customers. Since it affected only 2 customers MS didn't
want to think of a fix and asked us to seal the port 454. :)
<shandain@.gmail.com> wrote in message
news:1125535239.274678.73180@.o13g2000cwo.googlegroups.com...
> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>|||I met this error on SP3a after moving to SAN. Now we are trying to install
SP4 to fix this issue. You can find my post on Sept 1. Are you running on
SAN?
Bill
"shandain@.gmail.com" wrote:
> Hi ~
> I realize that this has been posted on, but I am confused. We are
> getting an error in our event logs at odd times (not at backup times or
> anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> Scheduler 3, or a variant there of.. and so far, the only solution
> that I can see for this problem on a Win2K server running SQL 2000 SP4
> is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> option though, as this would open the server to known security flaws,
> etc... correct? Are there any other options?
>|||How do i remove MS05-027 as it is not showing up in the add remove programs.
(I'm not sure if i have removed it but i am still getting the 17883 errors
when trying to backup databases)
"ME" wrote:
> I had this error after MS05-027 applied. Removing MS05-027 fixed the
> problem. It happened on 2 DL380 G2 Compaq servers with exac same h/w. All
> drivers were up-to-date. Engineers from MS admitted it was a rare bug
> reported only by 2 customers. Since it affected only 2 customers MS didn't
> want to think of a fix and asked us to seal the port 454. :)
>
> <shandain@.gmail.com> wrote in message
> news:1125535239.274678.73180@.o13g2000cwo.googlegroups.com...
> > Hi ~
> >
> > I realize that this has been posted on, but I am confused. We are
> > getting an error in our event logs at odd times (not at backup times or
> > anything) with the infamous Error: 17883, Severity: 1, State: 0 Process
> > 0:0 (e54) UMS Context 0x121CA9A0 appears to be non-yielding on
> > Scheduler 3, or a variant there of.. and so far, the only solution
> > that I can see for this problem on a Win2K server running SQL 2000 SP4
> > is to downgrade to SQL 2000 sp3a... I can't really see this as a valid
> > option though, as this would open the server to known security flaws,
> > etc... correct? Are there any other options?
> >
>
>

Thursday, March 22, 2012

Error 15457 in sql logs

Seeing an error 15457 in the error log. Anyone know what it means? Thanks
It generally appears when you use EM to look at a servers properties and you
are a sysadmin. It's just the output of making sure "show advanced options"
is on, nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:3CC4F5CE-81FF-44AD-804C-03BD84985423@.microsoft.com...
> Seeing an error 15457 in the error log. Anyone know what it means?
> Thanks

Error 15457 in sql logs

Seeing an error 15457 in the error log. Anyone know what it means? ThanksIt generally appears when you use EM to look at a servers properties and you
are a sysadmin. It's just the output of making sure "show advanced options"
is on, nothing to worry about.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:3CC4F5CE-81FF-44AD-804C-03BD84985423@.microsoft.com...
> Seeing an error 15457 in the error log. Anyone know what it means?
> Thanks

Error 15457 in sql logs

Seeing an error 15457 in the error log. Anyone know what it means? ThanksIt generally appears when you use EM to look at a servers properties and you
are a sysadmin. It's just the output of making sure "show advanced options"
is on, nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:3CC4F5CE-81FF-44AD-804C-03BD84985423@.microsoft.com...
> Seeing an error 15457 in the error log. Anyone know what it means?
> Thankssql

Wednesday, March 21, 2012

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

What is the account that SQL server is running as?|||The account that SQL runs as is the one that it tries to login onto the other server as.|||Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

Message
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.16.6.14]

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend. Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.|||i too would like to create certificates i have the same problem|||

I met the same problem.

This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

IMPORTANT! To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)

FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN) ensure your 3 mssqlservices are running under NETWORK SERVICE account

|||Hi all,

I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating

Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.

This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command

master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]

I had to do this even though all SQL Services were running as a network service.

Cheers,
Damian
sql

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian
sql

Error 1418, fails to connect mirror server

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

a) On primary server:

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://mymirror.mydomain:5022'

b) On mirror server:

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

Hope someone can help me resolve this issue... Thank you very much..

Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||

In BOL there is a section that deals explicitly with setup errors for database mirroring.

Search for "troubleshooting database mirroring."

Thanks,

Mark

|||

I have exactly the same problem :

Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)

Source Logon

That's from the error log of the mirror.

Both the principal and the mirror have sql running as the local service account for each machine respectively.

Thanks!

Mark

|||

Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.

Once i did that it works just fine.

Hopefully, in future releases you'll make it easier to do that.

Thanks!

Mark

|||

Do you normally use Local System to run your SQL Server process?

Thanks,

Mark

|||Hello Mark,

I met the same problem.

And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

if someone got other answers,please write here.

|||

Hello everybody.

After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR

1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3) MRRUSR is the MSSQLSERVER service account in both machines

4) Machine A is gonna be the PRINCIPAL, B the MIRROR.

5) Log on in my machine as MRRUSR

6) Open Sql server Studio. Connect to both machines using windows authenticacion.

7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8) Backup the new DB (Full backup) with .bak extension

9) Backup the new DB (Transaction log) with .trn extension

10) Copy the both files to a location in B machine.

11) Restore .bak into a new DB using NORECOVERY option

12) Restore .trn into the previous DB using NORECOVERY option

13) Go to Machine A, open a new query

14) Take a look to the endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])

16) Create a new endopoint

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17) Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18) Do steps 13 to 17 in machine B.

19) Return to machine A. Query.

20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'

21) Repeat step 20 from MACHINE B pointing to MACHINE A

22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23) Lets test them: Add some new data in Machine A database.

24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25) Refresh the 2 databases in the UI you should see the new status and roles changed.

26) Open Machine B databse, the new data should be there.

Cheers!

Kelo

|||

Another one that has shorter steps (same idea)

http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

|||

|||Hi all,

Cheers,
Damian

Thursday, March 29, 2012

Error 18456 Severity 14 State 16

Error 18456 Login failed for user domain\user

Tuesday, March 27, 2012

Error 17883

Monday, March 26, 2012

Error 17883

Error 17883

Thursday, March 22, 2012

Error 15457 in sql logs

Error 15457 in sql logs

Error 15457 in sql logs

Wednesday, March 21, 2012

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

Error 1418, fails to connect mirror server

sql error 15138

Blog Archive

About Me