SQL Server Performance

Mirroing using Local System account

Discussion in 'SQL Server 2005 Database Mirroring' started by Akthar, Mar 4, 2008.

  1. Akthar New Member

    Has anybody configured DB Mirriroing with SQL Services starting using the Local System Account.
    Am trying that now, will keep you guys posted, if it is successfull
  2. satya Moderator

    <P mce_keep="true">Not possible until you use with a local account, FYI fromBOL;</P><P mce_keep="true">Authentication</P><DIV class=section id=sectionSection0><CONTENT xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">Authentication is the process of verifying that a user is who the user claims to be. Connections between database mirroring endpoints require authentication. Connection requests from a partner or witness, if any, must be authenticated.</P><P xmlns="">The type of authentication used by a server instance is a property of its database mirroring endpoint. Two types of transport security are available for database mirroring: Windows Authentication and certificate-based authentication.</P><P xmlns="">Windows Authentication supports two authentication protocols: NT LAN Manager (NTLM) and Kerberos. A database mirroring endpoint can be configured to use only one protocol or to negotiate between them. By default, negotiation is used. The default value, NEGOTIATE, causes the endpoint to use the Windows negotiation protocol to choose either NTLM or Kerberos. If a specific authorization method (NTLM or Kerberos) is specified on an endpoint, it can use only that method. If the opposite endpoint is configured to use only the other method, the endpoints cannot connect with each other. For more information about these methods, see <?XML:NAMESPACE PREFIX = MSHelp NS = "http://msdn.microsoft.com/mshelp" /><MSHelp:link tabIndex=0 keywords="eef476b4-6b4f-471c-b4e0-392a9a3b3dd7" filterString='("ProductVers"="kbsqlserv90")'>Endpoint Authentication Types</MSHelp:link>.</P><DIV class=alert xmlns=""><TABLE class="" cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH class="" align=left><IMG class=note src="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/local/note.gif">Note: </TH></TR><TR><TD class="">For information about this endpoint, see <MSHelp:link tabIndex=0 xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" keywords="39332dc5-678e-4650-9217-6aa3cdc41635" filterString='("ProductVers"="kbsqlserv90")'>Database Mirroring Endpoint</MSHelp:link>. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV><P xmlns="">A database mirroring connection uses either Windows Authentication (the Security Support Provider Interface (SSPI)) or certificate-based authentication. </P></CONTENT><SECTIONS xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><H3 class=subHeading xmlns="">Windows Authentication</H3><DIV class=subSection xmlns=""><CONTENT xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">Under Windows Authentication, each server instance logs in to the other side using the Windows credentials of the Windows user account under which the process is running. For this reason, Windows Authentication requires that SQL Server services must run as domain users in trusted domains or as network services.</P><P xmlns="">To authenticate both ends of a connection, Windows Authentication uses the credentials of the Windows user account on which the SQL Server instances are running. Therefore, the user account of each server instance must have the permissions needed to log in and send messages to each of the other server instances. </P><P xmlns="">For an example of setting up a database mirroring session using Windows Authentication, see <MSHelp:link tabIndex=0 keywords="35800769-aede-4aac-b077-0e0e487e302f" filterString='("ProductVers"="kbsqlserv90")'>Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL)</MSHelp:link>.</P></CONTENT></DIV><H3 class=subHeading xmlns="">Certificates</H3><DIV class=subSection xmlns=""><CONTENT xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">In some situations, such as when server instances are not in trusted domains or when SQL Server is running as a local service, Windows Authentication is unavailable. In such cases, instead of user credentials, certificates are required to authenticate connection requests. The mirroring endpoint of each server instance must be configured with its own locally created certificate. </P><P xmlns="">The encryption method is established when the certificate is created. For more information, see <MSHelp:link tabIndex=0 keywords="464c9096-10d6-4c5e-8bb1-19acba27ad9e" filterString='("ProductVers"="kbsqlserv90")'>How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL)</MSHelp:link>. Carefully manage the certificates that you use.</P><P xmlns="">A server instance uses the private key of its own certificate to establish its identity when setting up a connection. The server instance that receives the connection request uses the public key of the sender's certificate to authenticate the sender's identity. For example, consider two server instances, Server_A and Server_B. Server_A uses its private key to encrypt the connection header before sending a connection request to Server_B. Server_B uses the public key of Server_A's certificate to decrypt the connection header. If the decrypted header is correct, Server_B knows that the header was encrypted by Server_A, and the connection is authenticated. If the decrypted header is incorrect, Server_B knows that the connection request is inauthentic and refuses the connection.</P><DIV class=alert xmlns=""><TABLE class="" cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH class="" align=left><IMG class=note src="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/local/security.gif">Security Note: </TH></TR><TR><TD class="">Only install certificates from trusted sources. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV><P xmlns="">SQL Server 2005 does not provide any automated method for configuring database mirroring security using certificates. Using Transact-SQL is required. For an example of using certificate-based authentication for setting up a database mirroring session, see <MSHelp:link tabIndex=0 keywords="df489ecd-deee-465c-a26a-6d1bef6d7b66" filterString='("ProductVers"="kbsqlserv90")'>Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)</MSHelp:link>.</P></CONTENT></DIV></SECTIONS></DIV>
  3. Akthar New Member

    i have a problem starting my SQL Agent using Domain or other local accounts. as per my older posts.
    What option is left for me?
  4. satya Moderator

    May try with same account (local) between the servers.
  5. Akthar New Member

    i will try that tonight.
  6. Akthar New Member

    Guys ,
    i finally managed to setup DB Mirroring using a LOCAL ACCOUNT using the below Steps.
    Short history: i had an issue starting the SQL Server Agent Service that does not start if i start the SQL Server Service with a Domain Account or any Local Account.i even tried with SP2
    what i did:
    1.Start SQL SERVER AND AGENT SERVICES with a local account (ServerNameAccountName)
    2.Enable AgentXps using sp_configure
    3.Create the same account name with the same password on the Mirrored Server
    4.Setup Mirroring
    5.Re-Enable AgentXps (as Maintenence plan did not work)
    6.Tested Maintenence Plan - Success
  7. satya Moderator

    Great to hear that... why not blog that in this case?

Share This Page