0

I am trying to create an account which has permissions to start a mirroring session between two servers. Some Googling suggests that the permissions an account needs to start a mirroring session is ALTER ANY DATABASE and CONNECT on the database mirroring endpoint on both mirrors

MSDN post

I have granted these permissions and I get the error

Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

when running the SET PARTNER command on the secondary server as my mirroring account.

I have found that if the user is a sysadmin, the command works. I have also found that if I run the SET PARTNER command on the secondary as my account (which is a sysadmin) the SET PARTNER command on the primary works when run as my mirroring account

My MCVE is below:

set up the endpoint on the primary server

USE [master]
GO

IF EXISTS (SELECT 1 FROM sys.database_mirroring_endpoints)
BEGIN
    DECLARE @name SYSNAME = (SELECT name FROM sys.database_mirroring_endpoints)
    DECLARE @sql NVARCHAR(MAX) = N'DROP ENDPOINT [' + @name + ']';
    EXEC sys.sp_executesql @command = @sql
END

CREATE ENDPOINT [Mirroring_Endpoint]
       STATE=STARTED
       AS TCP (LISTENER_PORT = 7022, LISTENER_IP = ALL)
       FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'Mirroring')
BEGIN
    ALTER DATABASE Mirroring SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE Mirroring
END

create my test database plus the user I want to be able to start the mirroring session and give it the permissions it needs

CREATE DATABASE [Mirroring]
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FailoverUser') DROP LOGIN FailoverUser
CREATE LOGIN FailoverUser WITH PASSWORD = 'My570ngP4$$'

USE Mirroring
CREATE USER FailoverUser FOR LOGIN FailoverUser

USE [master]
GRANT ALTER ANY DATABASE TO FailoverUser
GRANT CONNECT ON ENDPOINT:: [Mirroring_Endpoint]TO FailoverUser

create the endpoint on the secondary server (same code barring the port)

USE [master]
GO

IF EXISTS (SELECT 1 FROM sys.database_mirroring_endpoints)
BEGIN
    DECLARE @name SYSNAME = (SELECT name FROM sys.database_mirroring_endpoints)
    DECLARE @sql NVARCHAR(MAX) = N'DROP ENDPOINT [' + @name + ']';
    EXEC sys.sp_executesql @command = @sql
END

CREATE ENDPOINT [Mirroring_Endpoint] 
       STATE=STARTED
       AS TCP (LISTENER_PORT = 7023, LISTENER_IP = ALL)
       FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'Mirroring')
BEGIN
    ALTER DATABASE Mirroring SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE Mirroring
END

create my test database plus the user I want to be able to start the mirroring session and give it the permissions it needs

CREATE DATABASE [Mirroring]
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FailoverUser') DROP LOGIN FailoverUser
CREATE LOGIN FailoverUser WITH PASSWORD = 'My570ngP4$$'

USE Mirroring
CREATE USER FailoverUser FOR LOGIN FailoverUser

USE [master]
GRANT ALTER ANY DATABASE TO FailoverUser
GRANT CONNECT ON ENDPOINT:: Mirroring_Endpoint TO FailoverUser

backup the database on the primary that I want to mirror:

BACKUP DATABASE Mirroring TO DISK = 'C:\Test\MirrorBackup.bak' WITH STATS = 1, FORMAT

restore the database to the secondary

RESTORE DATABASE Mirroring FROM  DISK = N'C:\Test\MirrorBackup.bak' 
WITH
    NORECOVERY,
    STATS = 5,
    REPLACE,
    MOVE N'Mirroring' TO N'C:\Test\SQL2016MIRROR\Data\Mirroring.mdf', 
    MOVE N'Mirroring_log' TO N'C:\Test\SQL2016MIRROR\Log\Mirroring_log.ldf'

at this point, the Mirroring database is ready to be mirrored, using the SET PARTNER commands. If I run the following on the secondary as the user I set up:

EXECUTE AS LOGIN = 'FailoverUser'

ALTER DATABASE Mirroring SET PARTNER = 'TCP://MYPC:7022'

I then get the error:

Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0,Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

I can try running again using the same sysadmin account I ran all the above commands as:

ALTER DATABASE Mirroring SET PARTNER = 'TCP://MYPC:7022'

and I get:

Commands completed successfully.

I can then run the corresponding command on the primary as the FailoverUser and it works:

EXECUTE AS LOGIN = 'FailoverUser'

ALTER DATABASE Mirroring SET PARTNER = 'TCP://MYPC:7023'

the database is then mirrored.

I have tried the above example on multiple PCs, and multiple versions of SQL Server (2016 and 2019) with the same result.

It appears that this is permissions related as if I run the first SET PARTNER command as a sysadmin account, it will work (It works if I add FailoverUser to the sysadmin server role)

What permission am I missing (or am I missing something else somewhere?) Why does the SET PARTNER command work on the primary?

Other permissions I have tried:

  • Adding FailoverUser as a user in master and in my Mirroring database
  • Making FailoverUser a db_owner in the Mirroring database on both servers
  • running the SQL Server service account as a local windows account, a domain account and a local service account on both servers
  • Adding FailoverUser to all server roles barring sysadmin on both servers

but none of these have worked either.

What am I missing?

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.