SQL server SP2 Mirroring with auto failover. Everything looks fine except:

Login account, SQL service domain Account with local Admin rights.

on Windiows event viewer APP log om Principal server:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18452
Date: 8/4/2009
Time: 4:07:41 PM
User: N/A
Computer: NYSQL-4 (note: IP 10.105.3.78)
Description:
Login failed for user ''. The user is not associated with a trusted SQL
Server connection. [CLIENT: 10.105.3.78]

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 14 48 00 00 0e 00 00 00 .H......
0008: 09 00 00 00 4e 00 59 00 ....N.Y.
0010: 58 00 42 00 45 00 52 00 X.S.Q.L.
0018: 2d 00 34 00 00 00 07 00 -.4.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...


Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18452
Date: 8/4/2009
Time: 4:07:41 PM
User: N/A
Computer: NYSQL-4
Description:
Login failed for user ''. The user is not associated with a trusted SQL
Server connection. [CLIENT: 10.105.3.78]

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 14 48 00 00 0e 00 00 00 .H......
0008: 09 00 00 00 4e 00 59 00 ....N.Y.
0010: 58 00 42 00 45 00 52 00 X.S.Q.L.
0018: 2d 00 34 00 00 00 07 00 -.4.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...



On stand by server:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 8/4/2009
Time: 3:24:58 PM
User: US\sqlservices
Computer: NYXSQL-3
Description:
Login failed for user 'US\sqlservices'. [CLIENT: 10.105.3.78]

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 09 00 00 00 4e 00 59 00 ....N.Y.
0010: 58 00 42 00 45 00 52 00 X.S.Q.L.
0018: 2d 00 33 00 00 00 07 00 -.3.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...


anything wrong?

SQL 2005/2008 Service accounts should NOT be local admins.

Use the SQL Config manager to change the SQL Server to localsystem.
Remove the service account from the local admins group.
Use the SQL Config manager to change the service account back to the
original value.


UAC is blocking administrators membership but since SLQ cannot "run as
administrator" you won't ever get around that problem.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP



"EX Admin" <EXAdmin (AT) discussions (DOT) microsoft.com> wrote

Hi Geoff,

Thank you for your advise. I created new domain account and assign logon
locally and log on as service rights on local machine policy and use SQL
database config manager to change all SQL service to localsystem, then change
it back to the new sql service account. After I make the change, it still
happens when I failover the database on NYSQL-4. If the principal runs on
NYSQL-3, everything is OK. Any idea? thanks again.


"Geoff N. Hiten" wrote:

Do you have NYSQL-4 in mixed authentication mode? It should be. It looks
like a client app is connecting via SQL authentication and can't.

Also, did you transfer the SQL logins (with SIDs intact) to the secondary
server?

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP




"EX Admin" <EXAdmin (AT) discussions (DOT) microsoft.com> wrote

Yes, both servers are in mixed mode. All SQL logins are domain account or
built-in accounts. How can I transfer the SQL logins (with SIDs intact) to
the secondary
server?


Here is SP_help_revlogin results from the Principal Server (NYSQL-3)
-- Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE =
[master]

-- Login: NT AUTHORITY\SYSTEM
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE =
[master]

-- Login: NYSQL-3\SQLServer2005MSSQLUser$NYSQL-3$MSSQLSERVER
CREATE LOGIN [NYSQL-3\SQLServer2005MSSQLUser$NYSQL-3$MSSQLSERVER] FROM
WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: NYSQL-3\SQLServer2005SQLAgentUser$NYSQL-3$MSSQLSERVER
CREATE LOGIN [NYSQL-3\SQLServer2005SQLAgentUser$NYSQL-3$MSSQLSERVER] FROM
WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: NYSQL-3\SQLServer2005MSFTEUser$NYSQL-3$MSSQLSERVER
CREATE LOGIN [NYSQL-3\SQLServer2005MSFTEUser$NYSQL-3$MSSQLSERVER] FROM
WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: US\sqlservices
CREATE LOGIN [US\blackberry] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
REVOKE CONNECT SQL TO [US\sqlservices]

it is the US\sqlservices (old SQL services account that is also a service
account for another application needed to access the mirroing databases
exculsively) that caused the login failure issue

"Geoff N. Hiten" wrote:

it seems the account was revoked somehow? How can I grant it back? BTW,
US\sqlservices (old SQLservice account) still member of built-in local admin
group.

Login: US\sqlservices

CREATE LOGIN [US\sqlservices] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
REVOKE CONNECT SQL TO [US\sqlservices]

Thanks again.

"EX Admin" wrote:

Create a new login for US\sqlserviceson the secondary and add it to the
appropriate server roles.


CREATE LOGIN [US\sqlservices] FROM WINDOWS WITH DEFAULT_DATABASE = [master];


--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP



"EX Admin" <EXAdmin (AT) discussions (DOT) microsoft.com> wrote

I cannot delete the login:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Login 'US\sqlservices'. (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdN...n&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal owns an endpoint and cannot be dropped. (Microsoft SQL
Server, Error: 15141)

For help, click:
http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


"Geoff N. Hiten" wrote:

I tried to delete it from the principal server with the same error.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Deleting server logins does not delete the database users associated with
the logins. To complete the process, delete the users in each database. It
may be necessary to first transfer the ownership of schemas to new users.

------------------------------
BUTTONS:

OK
Cancel
------------------------------


"EX Admin" wrote:

try to

CREATE LOGIN [US\sqlservices] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
and got the following message.

Msg 15025, Level 16, State 2, Line 1
The server principal 'US\sqlservices' already exists.

tried to delete the account first:

I got :

TITLE: Microsoft SQL Server Management Studio
------------------------------
Deleting server logins does not delete the database users associated with
the logins. To complete the process, delete the users in each database. It
may be necessary to first transfer the ownership of schemas to new users.
------------------------------
BUTTONS:
OK
Cancel
------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Login 'US\blackberry'. (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdN...n&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------



The server principal owns an endpoint and cannot be dropped. (Microsoft SQL
Server, Error: 15141)

Any suggestion? thanks again.

"Geoff N. Hiten" wrote: