Database Mirroring - 'NT AUTHORITY\ANONYMOUS LOGON.' failed

Hello,

I have set up 3 servers - Primary, Mirror and Witness. When I run the database mirroring wizard all my endpoints are configured, but when i start the mirroring service i get a 1418 error - in the logs in says -

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.20.1.5]

I have looked through the BOL and can't find anything helpful. - I'm pretty new to SQL server, so any help is very much appreciated!

Thanks

Kerpoise

This is not a SQL problem but an authentication issue. The right approach is to follow the guidance of the authentication troubleshooting whitepaper: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

HTH,
~ Remus

|||But why is my Primary server trying to logon as NT AUTHORITY/ANONYMOUS LOGON? When I have defined my logon through the Mirroring Wizard as a sysadmin account, with enabled Connect privileges on all servers.

I can't find a simple solution in the white paper. Isn't there a simple way to either - authenticate as a sysadmin account, or to allow NT AUTHORITY/ANONYMOUS to have CONNECT privileges to all servers?

It seems like this should be a very quick thing to do, but I really can't figure it out.

Please help!|||

Do not grant connect permission to ANONYMOUS LOGON.

When connecting the two instances what gets authenticated is the 'service account', the Windows account that runs the SQL Server instance. Depending on how you insnstalled the instances, the appropiate action is:
- if installed as 'LOCAL SERVICE': you must change the service account, it will never work. The SQL Management console in mmc has an option to change the service account, selecta different account.
- if installed as 'NETWORK SERVICE' or 'LocalSystem': you have to register the service SPN for Kerberos authentication to succeed. Use a tool like setspn.exe (available at microsoft download center) and register the SPN. Mirroring will use an SPN on the format 'MSSQLSvc/<partnername>:<partnerport>'. Also, make sure the AUTHENTICATION option on the mirroring endpoint is WINDOWS, WINDOWS NEGOTIATE or WINDOWS KERBEROS (in other words NTLM will not work). After registering the two SPNs (both for mirror and principal!), the authentication will resolve to the machine account ('DOMAIN\MachineName$'), you must create a login for this account and grant connect permission to it.
- if installed as a local account ('PrincipalMachine\LocalAccount' and/or 'MirrorMachine\LocalAccount'): it will not work, you must change the service account(s).
- if installed as domain account ('DOMAIN\UserName'), then it should work as long as both the mirror and the principal are in the same domain, or there is a trust relationship between their domains.

An alternative is to use certificate based authentication, then the whole domain/user/SPNs issue vanishes as the authentication will use a diferent protocol (SChannel's TLS).

HTH,
~ Remus

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2?
I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>sql

Database marked suspect

My primary database is currently marked suspect. I
believe it could be because the transaction log was
deleted. How can I restore the database to be
operational?
Any assistance is greatly appreciated!use this one sp_resetstatus [ @.DBName =3D ] 'database'
See BOL for more information om the same ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Michelle" <msarna@.starpower.net> wrote in message =news:042c01c3588e$c42cda60$a401280a@.phx.gbl...
> My primary database is currently marked suspect. I > believe it could be because the transaction log was > deleted. How can I restore the database to be > operational?
> > Any assistance is greatly appreciated!

Database marked as suspect please HELP!

Hi,

I restored my Windows XP system and discovered that I cannot access a
MSDE DB that was saved in D: drive (I restored my primary partition in
C). How can I restore it?
Thanks a lot.did you try reattaching it ?|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:

>did you try reattaching it ?

How can I do that? I have the MDF and LDF files but I've never done a
backup. I'm using the trial version of Teratrax Database Manager and
DbaMGR2K.
Thanks a lot for your help.|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:

>did you try reattaching it ?

I noticed the attach command in Teratrax but don't know how to use it.
It tells me

sp_attach_db 'database_name', 'file_name_and_path_relative_to_server'

Is the database_name the filename? What's the relative path? I've my
DB saved in D:\database while the server is in C:\InetPub I think.
Thanks.|||yea the relative path would be where you are storing the data...thus
being on your D drive.|||On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:

>yea the relative path would be where you are storing the data...thus
>being on your D drive.

Could you be more specific, what string should I enter? Thanks.|||kurotsuke (kurotsuke@.yahoo.it) writes:
> On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:
>>yea the relative path would be where you are storing the data...thus
>>being on your D drive.
> Could you be more specific, what string should I enter? Thanks.

The exact path to where the database file is located. (The Send To Cliboard
as Name from the Win95 Powertoys comes in extremely handy here.) For
instance D:\Program Files\Microsoft SQL Server\MSSQL\Data\yourdatabase.mdf.
Note that you have two files to enter: the .MDF and the .LDF.

However, since your database is marked as suspect, it still attached
to SQL Server in some sense. You may have to drop it first. Then again,
you could attach the database under a new name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp