Sql-server – SQL Server Mirroring – endpoint permissions – why grant connect to SQL service account

sql server

SQL 2008 R2
Implementing database mirroring.
Mirroring endopoints created on both partner servers (principal and mirror) and on the witness.

Was planning to GRANT CONNECT permissions on the mirroring endpoints to the domain account under which the database servers (partners and witness) are running.

Question: Why do we need to create a database server login for this domain account on each database server? The domain account should already have full priviliges on the database servers.

Best Answer

The requirement to create a login for the peer and grant connect permission is required only if such a permission is necessary. If the peers (and witness) run under a service account that already has the required privileges, then the grant is not technically required.

However I would recommend that, whatever you do, do it consistently. If all your DBM deployments always create a login for the peer(s) and grant connect permission on the DBM endpoint, then do it everywhere, even if not required. If you decide that the explicit logic/connect permission is not necessary, be consistent and don't add this login anywhere.

Related Solutions

Sql-server – How to grant read-only access to the SQL Server 2008 database

I usually do something along these lines:

USE [test]
GO
CREATE USER [ReadOnlyUser] FOR LOGIN [LOCALNT\ReadOnlyNTUser]
GO
EXEC sp_addrolemember N'db_datareader', N'ReadOnlyUser'

Sql-server – Cannot add witness to the mirroring setup

Can you attach the SQL Profiler to all three instances involved and monitor for these events:

Then attempt again to establish the mirroring session. Make sure you select all columns when adding the events. Start from a blank template.

2011-12-09 20:04:07.983  Database Mirroring Connection    Connected   
2011-12-09 20:04:08.133  Audit Database Mirroring Login   Login Success
2011-12-09 20:04:27.980  Database Mirroring State Change DBM: 
  Synchronized Principal without Witness -> 
  DBM: Synchronizing Principal             
2011-12-09 20:04:28.237  Database Mirroring State Change DBM: 
  Synchronizing Principal -> 
  DBM: Synchronized Principal without Witness
2011-12-09 20:05:42.530  Database Mirroring Connection   An error occurred...

Here is the explanation for what happens:

at 20:04:07 the principal connect with the witness
at 20:04:08 the principal and witness finish a successful handshage (audit login succes)
at 20:04:27 DBM changes the state from synchronized w/o witness to synchronized
at 20:04:28 DBM changes the state back from syncronized to synchronized w/o witness
at 20:05:42 the connection with the witness times out due to inactivity and closes

This sequence of events indicates that the connectivity between principal and witness is functional. The request to add the witness fails, and there could be multiple reasons. Similar events must occur on the mirror and on the witness as well, is not clear why you say that you could only capture them on the principal.

Best Answer

Related Solutions

Sql-server – How to grant read-only access to the SQL Server 2008 database

Sql-server – Cannot add witness to the mirroring setup

Related Topic