Re: SQL 2k5 SP2 Mirroring - SQL in Mixed mode.


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:

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:

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D28F94B-B5F6-4F85-A2E8-DCCA71EE2EAB@xxxxxxxxxxxxxxxx
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:

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B9A17207-B294-48CA-82FA-66F9E36EB94A@xxxxxxxxxxxxxxxx


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?







.

Relevant Pages

  • Re: SQL account rights
    ... Please advice what is the best, suitable rights rather than domain admin ... issues, such as a server that might have IIS running on the same machine, ... applicable to SQL 2000 environment, ... files, or backups, make sure that the service account has Full ...
    (microsoft.public.sqlserver.security)
  • RE: MP Install issue
    ... Where in the installation are you talking about specifying the account rather ... > MPDB ERROR - CONNECTION PARAMETERS ... > SQL Server Name: servername ... > with a trusted SQL Server connection. ...
    (microsoft.public.sms.setup)
  • Re: SQL Express Fails with Hardware Error
    ... The LocalSystem account is a built-in account, ... which the SQL Service runs. ... MCSE, CCEA, Microsoft MVP - Terminal Server ... Minimum Hardware Requirement (Warning) ...
    (microsoft.public.sqlserver.setup)
  • Re: SCCM with a remote SQL instance problems (IT IS NOT A WARNING)
    ... PreReq check is not a WARNING it is a FAILURE. ... account the run the SQL Server Service on the server, Domain Memberships, AD ...
    (microsoft.public.sms.installer)
  • Re: SQL 2k5 SP2 Mirroring - SQL in Mixed mode.
    ... How can I transfer the SQL logins to ... Here is SP_help_revlogin results from the Principal Server (NYSQL-3) ... -- Login: BUILTIN\Administrators ... it is the US\sqlservices (old SQL services account that is also a service ...
    (microsoft.public.sqlserver.clustering)